Viewing csv reports – AB Commerce Online Help

Most reports downloaded from the website are in .csv format. These can be opened easily in excel or similar spreadsheet packages.

Issues opening csv files in excel

When excel opens a .csv file

Depending on how your Excel is configured, Excel can try to set an appropriate data type, eg fields/columns with only digits may be set to an "number" data type . This can cause issues where you have a code, eg
- a product code or barcode which is purely numerical ( eg 0000003890370) as excel can remove zeros at the start of the code. This is also an issue for phone numbers which start with zero.
- Also Excel can display long numerical fields as scientific numbers eg 5.05E+12.
Sometimes text can display text with unusual characters, for example trademark sign, copyright sign and euro symbol display strangely. Websites use UTF-8 encoding but that is not always the encoding used by in Excel.

How to resolve these issues

Updating the data conversion options in Excel

In some versions of excel you can configure how excel handles different types of data eg leading zeros in codes (eg 0000003890370) and using scientific notation like 5.05E+12

In FILE - OPTIONS Choose DATA on the left

Untick the following

Remove leading zeros and convert to a number
Keep first 15 digits of long numbers and display in scientific notation
Convert digits surrounding the letter "E" to a number in scientific notation

Click Ok to save the changes.

You can also tick the "When loading a .csv file or similar file, notify me of any automatic data conversion. If ticked you will get an option to CONVERT or DON'T CONVERT any data. Choose DON'T CONVERT to stop the excel from changing the data

Opening CSV Files as UTF-8 under DATA to display special characters correctly

To ensure that your Excel displays special characters such as the trademark sign, copyright sign and euro symbol, make sure that Excel uses UTF-8 encoding.

Instead of double-clicking the CSV:

Open Excel first
Go to Data
Click From Text/CSV
Select the file
In the preview window:
- Set File Origin to 65001: Unicode (UTF-8)
Load

This guarantees UTF-8 is used.

Alternative Options to resolve issues

Import a .csv file into excel

If the options above (Updating data conversion options and opening CSV Files as UTF-8 under Data) are not available to you you can try to import the file

Allows you to

Set the type of data in each column eg you can set ProductCode or Barcode to a 'Text' field so that excel doesn't treat it as a number
Set the encoding to UTF-8 which will avoid issues with funny characters

STEP 1

In your file explorer right-click on the file and rename. Change the .csv to .txt. Click ok on the "If you change a file name extension, the file might become unusable" message.

If you don't see the file extension (eg .csv, .txt) in your filename you can change the settings on the file explorer to show the file name extension by clicking on the view table and checking the File name extension checkbox as in the image below.

STEP 2

Open excel and choose Open from the file menu. Browser to the folder containing your .TXT file. Change the dropdown to show all files(as highlighted in yellow below). Select your file and click OPEN.

In step 1 of the Text import wizard choose the file type as Delimited, check the 'my data has headers' and change the file origin to unicode (UTF-8). "unicode (UTF-8)" is about 9 from the bottom of the long list in the dropdown, . Click Next

In step 2 of the text import wizard, check the Comma delimiter (this should be the only delimiter chosen). Click NEXT.

In step 3 of the text import wizard, click on each column you wish set the data type for and chose the data type. In the example below I clicked on the Code column and set it to text, then i clicked on the the BarCode column and set it to text. Click finish.

Save your file as .xlsx - the excel file type. This ensures the changes you have made are kept when you close the file.

Using a csv viewer instead of excel

View the report in a csv viewer instead of excel. These csv viewers allow you to view information but have limited functionality eg for searching eg see www.tadviewer.com