A Format Without Rules
CSV looks like the simplest file format there is: one row per line, values separated by commas. The trouble is that a CSV file carries no information about itself. It does not say which character encoding it uses, whether the separator is a comma or a semicolon, or whether 01/02/2026 means January or February. Every program that opens it has to guess, and Excel guesses according to the regional settings of your computer.
Almost every CSV problem comes from one of four wrong guesses. Each has a clear symptom and a clear fix.
1. Strange Characters Instead of Letters
Symptom: names such as Müller, Šabić or José appear as Müller, Å abić or José. Cause: the file is saved as UTF-8, the encoding used by practically all websites, databases and modern tools, but the program read it as an older Windows encoding. In UTF-8 a letter like ü takes two bytes; read the old way, those two bytes show up as two wrong characters. Fix: do not double-click the file. In Excel, open an empty workbook and choose Data → From Text/CSV, select the file, and set "File origin" to 65001: Unicode (UTF-8). The preview shows immediately whether the letters are right. LibreOffice Calc always asks for the character set when it opens a CSV — pick UTF-8 there.When we convert a spreadsheet to CSV on this site, the result is always UTF-8. We test that with a file containing š, đ, č, ć, ž and Cyrillic text, because those are exactly the characters that break first.
2. Everything Lands in One Column
Symptom: each row appears as one long cell:Name,City,Amount.
Cause: separators. In countries where the decimal sign is a comma — most of continental Europe — Excel expects CSV files to use a semicolon between values, because the comma is busy inside numbers such as 3,14. A comma-separated file from an English-language system is then not split at all. The reverse happens too: a semicolon file opened on a US or UK computer stays in one column.
Fix: again Data → From Text/CSV, and choose the delimiter the file really uses. If the data is already in one column, select it and use Data → Text to Columns. To avoid the question altogether, convert the file to a real spreadsheet with CSV to XLSX — our converter expects commas, so a semicolon file needs its separators replaced first in a text editor.
3. Leading Zeros Disappear
Symptom: postal code01234 becomes 1234; product code 000457 becomes 457; a long card or tracking number turns into 1.23457E+15.
Cause: the program sees only digits, decides it is a number, and numbers have no leading zeros. Numbers longer than 15 digits also lose precision, because that is all a spreadsheet number can hold. The CSV file itself is still intact — the damage happens on opening, and becomes permanent the moment you save.
Fix: in the import dialog (Data → From Text/CSV → Transform Data, or the column list in LibreOffice), mark those columns as Text before loading. Anything that is an identifier rather than a quantity — postal codes, phone numbers, account numbers, article numbers — should be text. This also applies when a CSV is converted to XLSX automatically: digits are read as numbers, so check identifier columns afterwards.
4. Dates and Numbers Change Meaning
Symptom:03/04/2026 becomes 4 March on one computer and 3 April on another. A value such as 1.250 turns into 1250, or MAR1 becomes the first of March.
Cause: the same guessing game, applied to dates and decimal signs. Day–month order and the decimal separator both depend on regional settings.
Fix: if you create the file, write dates as 2026-04-03 (year-month-day). That form is unambiguous everywhere. If you receive the file, import the column as text first, look at a few values where the day is above 12 to see which order is used, and convert it deliberately.
Going the Other Way: Spreadsheet to CSV
Saving a workbook as CSV loses more than most people expect, and by design:
- Only one sheet is saved. A CSV holds a single table. Other sheets are not in the file.
- Formulas become their results. The CSV contains
150, not=A1*B1. - All formatting is gone: colours, column widths, number formats, merged cells, charts.
- What you see is what is saved. A value shown rounded to two decimals is usually written rounded.
Keep the XLSX or ODS file as your working copy and treat the CSV as an export for another system. Our XLSX to CSV converter writes comma-separated UTF-8 with quotes around values that contain commas — the form most databases and import tools expect.
A Short Checklist
- Open CSV files through the import dialog, never by double-click, when the content matters.
- Wrong letters → choose UTF-8.
- One column → choose the right separator.
- Codes and long numbers → import as text.
- Dates → write them as year-month-day.
- Never save over the original CSV until you have checked the import.
More on spreadsheet formats is in How to convert spreadsheets.