CSV to Excel
Everything that goes wrong with a CSV goes wrong while it is being read, and none of it can be repaired afterwards. The file is plain text carrying no types at all, so letting Excel decide unaided turns each column into a guess: leading zeros drop off codes, anything past fifteen digits is rounded for good, SEP1 and 1-5 become dates, and a comma-separated export lands entirely in column A on a machine whose regional list separator is a semicolon. The import dialog exists to take those decisions back — delimiter, encoding and each column's type — before a single value is written. When something has already landed wrong, the fix is another import, never a reformat.
A worked example
members.csv holds 4,000 rows exported from a German system: member IDs like 000481 in column A, a sixteen-digit bank reference in column D, and semicolons between fields.
Excel > Data > From Text/CSV > members.csv > Delimiter: Semicolon > File Origin: 65001: Unicode (UTF-8) > Transform Data > set columns A and D to Text > Close & Load
IDs keep their leading zeros, the sixteen-digit reference stays exact, and the fields sit in their own columns with the umlauts intact. Double-clicking the same file gives 481 instead of 000481, a bank reference whose last digit has become a 0, and every row crammed into a single column.
Which one do I need?
| If you want to… | Use |
|---|---|
| Leading zeros vanished, or long IDs now end in zeros | Import again with Data > From Text/CSV and set those columns to Text in the preview — the loaded values cannot be repaired in place |
| Every row is sitting in column A | Wrong delimiter: choose Semicolon, Tab or Custom in the import dialog. Excel defaults to your Windows regional list separator, which is why a colleague's file behaves differently |
| Accented characters show as é or as question marks | Set File Origin to 65001: Unicode (UTF-8) in the same dialog — a CSV carries no marker saying how it was encoded |
| Text like SEP1 or 1-5 has become a date | The same fix: import that column as Text. There is no setting that undoes date autocorrect after the values have landed |
| You need to save a workbook back out as CSV | File > Save As > CSV UTF-8 — one sheet only, formulas flattened to their values, all formatting dropped |
| Dozens of CSVs share the same layout | Get Data > From Folder reads them all in one query instead of opening each in turn |
| The file is a .txt or tab-separated export | The same importer handles it — set Delimiter to Tab |
| The CSV is republished on a schedule | Load it through Power Query so a Refresh re-applies the delimiter, the encoding and every column type |
Frequently asked questions
Why does Excel delete the leading zeros in my CSV?
A CSV is plain text with no type information, so 000481 reads as a number and a number has no leading zeros. The only reliable prevention is telling Excel the column is Text during the import — Data > From Text/CSV > Transform Data > set the column type to Text. Reformatting the cells afterwards will not bring the digits back.
Why is my whole CSV in one column?
Excel split on the wrong character. It uses the Windows regional list separator by default, which is a comma in the UK and US and a semicolon across much of Europe, so files travelling between the two open wrongly. Data > From Text/CSV lets you pick the delimiter explicitly; for a file already open, Data > Text to Columns does the same job after the fact.
What does a CSV lose compared with an .xlsx file?
Everything except the values of one sheet. Formulas are written out as their results, formatting, colours, column widths, charts, comments and every sheet but the active one are discarded, and no warning appears on subsequent saves. If a workbook has to stay a workbook, keep an .xlsx alongside and treat the CSV as an export.
Why do my sixteen-digit numbers change their last digits?
Excel stores numbers to fifteen significant digits, so anything longer — card numbers, some IBANs, long barcodes — is rounded on the way in and the original digits are unrecoverable. Import that column as Text so the value is kept as characters, and remember that no arithmetic is possible on it afterwards, which for an identifier is exactly right.
New guides and tools, once a month
DE + EN · double opt-in · no spam