Excel opens a CSV wrong: what to do
A CSV is a plain text table, but when you double-click it Excel often spoils it: it merges columns, breaks letters and “fixes” numbers. Here is each problem and how to see the data undistorted.
Everything in one column
Values in a CSV are separated by commas, semicolons or tabs. On a double-click Excel takes the separator from your Windows regional settings: a comma in English settings, a semicolon in much of Europe. When the file uses another one, each whole row lands in the first column.
Gibberish instead of letters
Exports from websites and services are usually UTF-8, while Excel on a double-click often reads the file in an old Windows encoding — and “Müller” becomes “Müller”. More in the guide about gibberish text.
Zeros vanished, numbers became 1.23E+15
Excel guesses the type of every cell right away and changes the data:
- a product code
00123becomes123— leading zeros are lost; - long numbers — cards, accounts, barcodes — turn into
1.23E+15, and every digit after the fifteenth becomes zero; - values like
1-2or3/10become dates.
If you save the file after that, the damage stays in it for good.
Over a million rows
An Excel sheet holds 1,048,576 rows. When a CSV has more, Excel says the file was not loaded completely and shows only the beginning.
See the CSV as it is in OsonKit
Drop the file on OsonKit. The separator — comma, semicolon, tab or pipe — and the encoding are detected for you. Values are shown exactly as written in the file: zeros, long numbers and date-like strings stay untouched. The file is read in your browser and sent nowhere.
- Files up to 20 MB and up to 1,000,000 rows; longer files show their first rows, and the status bar says so.
- Large tables scroll smoothly, and search highlights every matching cell.
- It is a viewer: you can read and search the table, not edit it.
How to open a CSV in Excel properly
Instead of double-clicking, import the file: Data → From Text/CSV. In the preview pick the encoding (UTF-8) and the delimiter, and in Transform Data set columns with codes and account numbers to Text. With more than a million rows, choose Load To… → Only Create Connection and tick Add this data to the Data Model: you can then analyse it with a PivotTable.