Reports - Viewing csv reports

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 excel tries to set an appropriate data type, eg fields with only digits may be set to an number.  This can cause issues where you have a code, eg a product code or barcode which is purely numerical 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 or copyright signs display strangely.  Websites use UTF-8 encoding but that is not always the default encoding in excel. 

To resolve these issues you can

• 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
• Import the .csv file into excel - this will allow 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

To import a .csv file into excel

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.