I have data in DiscoverText that I am trying to export to an Excel .csv file. I have noticed that the data structure of the exported CSV file is inconsistent. There are some records with all the fields in one line (i.e., the first cell when opening them in Excel), while other records have parts of data scattered across several cells. Those cases are then missing the “,,,,,,,” comma delimiters for empty variables.
That leads to imports into another application (e.g., SPSS) being difficult or close to impossible because there is no way to tell any other program to sometimes sort the data by cells and sometimes by delimiter.
It might be that the default delimiter for parsing .csv files on your computer is the semicolon character (;) instead of the comma character (,). Follow the instructions below.
- Open Excel (just by running it; not by double-clicking on a csv file).
- Open a blank workbook.
- Go to data --> From Text.
- Browse and open the .csv file.
- On the first page: Import it as delimited, and click "my data has headers". Also, to be certain, set the "File origin" to “65001 (Unicode UTF-8)”.
- On the next page: Ensure the delimiter is set only to Comma, and the text qualifier as the double quote character.
- Click on "Finish".
For general information about exporting data, please see the article How to Export a Dataset and the FAQ How can I download a CSV data file containing Sifter data?