Upon downloading your participants data and opening it Microsoft Excel, various columns (or individual cells) show numbers in a scientific format. For example, 9.8263E+18. Other issues can include missing columns and/or generally bad formatted dates or consistent data formatting.
Common Excel data issues
The following points outline common issues when opening CSV files.
- Numbers (such as a Facebook IDs) that contain more than 15 digits will be truncated. When Excel encounters a 16th digit it will round the number down to 0. This behaviour is described here and is common to all versions of Excel. The number will also formatted in the scientific format.
- Excel may also incorrectly determine the column delimiter resulting in blocks of data with no separation.
- Dates can appear incorrect due to regional formatting on 'dd/mm/yyyy hh:mm:ss'.
What to do
These steps describe how to import the CSV data into Excel without being affected by the number precision limitation or possibly incorrectly applied data formatting.
- Open Excel and create a new Blank Workbook.
- From the Data menu, select From Text.
- Open the exported CSV file, the Text Import Wizard should open.
- Select Delimited type and click Next.
Note: Unicode UTF-8 is the format used in all CSV exports.
- Change the Delimiter to Coma and click Next.
- Under Data Preview, use the shift key to select all the columns and change the Column data format to Text (The column header will change to Text to signify that the data format has changed).
This is very important, if you try to change the formatting on a column once it has already been imported as scientific, you will need to start the import process again as you cannot revert the data back to original value.
- On the Import Data screen, select a New worksheet and click OK.
That for cells that have 15 or more digits you will not be able to apply any Functions.