How to convert CSV to Excel without breaking your data
Converting a CSV to Excel is two clicks. The problem is what those two clicks do to your data on the way in, silently, with no warning and no undo.
The fast way, and why it is risky
Double-click the .csv, then File, Save As, Excel Workbook (.xlsx). Done.
What also happened, without asking:
| Your data | What Excel stored |
|---|---|
00123 |
123, leading zeros gone |
1-2 |
2 January, a date |
SEPT1 |
1 September |
8432596718423 |
8.43E+12 |
+44 20 7946 |
a formula error |
1,234.00 |
text, in some locales a different number |
Excel guesses the type of every column on import. Account numbers, product codes, postcodes, phone numbers and gene names are the usual casualties. Nothing on screen tells you, and once saved as XLSX the original value is gone.
The safe way: Power Query
Do not open the CSV. Open a blank workbook, then:
- Data, Get Data, From Text/CSV
- Pick the file
- Transform Data, not Load
- Select any column that is an identifier, then Data Type, Text
- Close and Load
Setting the type to Text before loading is the whole point. Excel never guesses, so nothing gets converted. This is the only method that reliably preserves leading zeros and long numeric IDs.
On a Mac
Power Query is in Excel for Mac from 2019 onward, but the editor is more limited. The reliable alternative is the legacy import: Data, Get External Data, From Text, then in the wizard set each problem column to Text before finishing.
Convert without Excel
LibreOffice gives you a type-per-column dialog on every CSV open, no add-ins, free, and honours “Format quoted field as text”.
Python, if a script suits you better:
import pandas as pd
pd.read_csv("in.csv", dtype=str).to_excel("out.xlsx", index=False)dtype=str is doing the work. Without it pandas guesses too.
DuckDB, for large files:
duckdb -c "COPY (SELECT * FROM 'in.csv') TO 'out.xlsx' WITH (FORMAT gsheet)"When the file will not convert
Above about 1,048,576 rows, conversion is not possible into a single sheet. That is the Excel row limit, and it applies to the destination regardless of how you get there.
Your options are to split the data across sheets, aggregate before exporting, or keep it as CSV and use something that can open it.
Below the row limit but still large, the failure is memory rather than rows. A 500 MB CSV commonly needs several gigabytes of RAM to become a workbook, because every cell gains formatting and formula metadata on the way in.
Which format should you keep?
| CSV | XLSX | |
|---|---|---|
| Row limit | none | 1,048,576 |
| Keeps types | no, everything is text | yes |
| Formulas, formatting, multiple sheets | no | yes |
| Readable by anything | yes | needs a library |
| Size on disk | smaller | smaller when compressed, slower to parse |
The practical answer: keep the CSV as the source of truth and treat the XLSX as a report you regenerate. CSV is the format that will still open in twenty years.
What we built
BigXL exports XLSX directly from a CSV of any size, and splits automatically at the 1,048,576 row limit with the header repeated on every sheet. Column types are taken from the data rather than guessed, so identifiers stay identifiers.
It runs entirely on your own machine, with no account and no network calls, so a file with customer or payroll data never goes to a conversion website.
Opening, sorting, filtering, charting and CSV export are free at any file size.