The exported CSV file looks strange, why does this happen?

CSV settings vary from Excel version to another, which affects how the data appears in Excel. Learn how to sort the data!

Once you have exported a CSV file from Vainu, and opened it in Excel, you may come across data in the wrong format depending on your Excel settings. Here are two of the most common settings that can affect how CSV files are displayed and instructions for sorting the data: 

All the information is in one cell 

If all the information is in a single cell instead of split to correct columns, the problem has to do with the separator that breaks the data into columns. The file is in CSV format, and it varies from country to country, excel version to another, what the default separator is. 

  1. The easiest way to sort the data is by choosing "text to columns" function, which is typically under "Data". 

Screen Shot 2017-08-03 at 14.39.10



2. Choose "Delimited": 

Screen Shot 2017-08-03 at 15.08.25



3. Select delimiter: Semicolon and check that the data looks correct: 

 

Screen Shot 2017-08-03 at 15.14.45

And you are good to go! Please note that the settings might look slightly different depending on whether you are using Windows or Mac. If you are using Windows, you can find instructions, for instance, here


Special characters (ä, ö, å) are shown wrong 

If special characters are shown as question marks or something else, the problem has to do with opening the CSV file with a wrong type of character encoding. Learn how to fix it: 

For Windows users: 

1. Create a blank workbook in Excel. 
2. Select a column →  Go to Data tab and select From text: 

Screen Shot 2017-08-03 at 16.30.42


3. Choose the option for "Delimited" and file origin: UTF-16LE
4. Go to next page and choose semicolon as the delimiter
5. Go to next page and keep the column data format as "general".
6. Done! You can now continue working with the file in Excel and save it for instance as an xls. file if needed.

More specific instructions here

For Mac users: 

1. Create a blank workbook in Excel.
2. Select a column → Click on Data → Get external data → Import text file: 

Screen Shot 2017-08-03 at 15.21.32


3. Choose the correct CSV file from your computer.
4. Pick Delimited, and choose a file origin. Same file origin does not always fix the text, so try few options for example Unicode (UTF-16LE) and Windows (ANSI). You will see in preview if the text has been fixed.

Screen Shot 2017-08-03 at 15.24.35

5. Go to next page and choose semicolon as the delimiter. 

Screen Shot 2017-08-03 at 15.27.52


6. Click on next and choose the column data type (General works fine). 
7. Done! Continue working normally and save the file e.g. as xls. file if needed.

Happy to help you out in the chat if you have any questions!