Opening a CSV file with semicolons and strange characters correctly

Exports from online shops, banks and membership administration systems are almost always CSV files. We hear two complaints again and again: everything ends up in one column, or strange characters appear, like ë instead of ë. You can fix both when you open the file.

Why does it go wrong?

• One column: Dutch programs usually separate fields with a semicolon, English ones with a comma. If the spreadsheet picks the wrong character, everything ends up in column A.
• Strange characters: the file is saved in a different encoding than the spreadsheet expects. Older Windows programs often use Windows-1252 (Western European), newer ones use UTF-8.

Note: clicking opens the text editor

If you click a CSV file in your WebOké Nextcloud, Nextcloud opens it as plain text in the text editor, not as a spreadsheet. To turn it into a proper table, import the CSV file into a spreadsheet.

How to import it correctly

1. Create a new, blank spreadsheet (or open the spreadsheet you want to add the data to).
2. Go to the Data tab, click Get Data and choose From Local TXT/CSV. Choose the CSV file on your computer. If the file is online, choose From TXT/CSV Web Address instead.
3. The Text Import window opens. Check the Preview to see whether the letters look right. If you see strange characters for é, ë and ô, choose a different encoding under Encoding. For exports from older Windows programs this is usually Western European (Windows); for newer ones it's Unicode (UTF-8).
4. Under Delimiter, choose Semicolon or Comma until the preview shows neat columns. Euro-Office often detects the delimiter automatically.
5. Under Choose where to put the data, select the top left cell, for example $A$1, and click OK.

Use Advanced to set the decimal separator and the thousands separator. For amounts such as 1,249.00, choose a period as the decimal separator and a comma for thousands. See also Importing and exporting CSV files.

Pitfalls

• After opening, you're working in a CSV file. A CSV doesn't store formatting, formulas or extra sheets. If you want to keep working with it, save a copy as xlsx via File, Save Copy As.
• Leading zeros (postcodes, phone numbers) and long numbers such as an IBAN can get lost when you open the file. See Keeping the leading zero in a phone number or postcode.
• Dates can come in as text. See Dates that come in as text or have day and month swapped.