Most reports downloaded from the website are in .csv format. These can be opened easily in excel or similar spreadsheet packages.
Issues opening reports 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.
To resolve this issue 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 - which avoids the issues mentioned above.
To import a .csv file into excel
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.
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). 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.