How to Convert a CSV to Excel without Breaking Your Data

The usual way people convert a CSV is to double-click it and choose File › Save As › Excel Workbook. It takes five seconds, and it is why so many .xlsx files contain wrong data. Everything Excel guessed while reading the text — that 00742 is the number 742, that 1-2 is a date, that a 16-digit reference only needs 15 digits — is written permanently into the workbook.

Why Double-Click Is the Wrong Route

A CSV file is plain text. It carries no column types, no formatting, no encoding and no delimiter declaration, so when Excel opens one directly it makes all of those decisions for you, silently, cell by cell. Microsoft names four conversions it performs, and publishes them as the switches you can turn off:

Two of these are not reversible. Once a 16-digit number has been rounded to 15 significant digits, the missing digits are gone; setting the cell format to Text afterwards gives 4532118899001230, not the original. Formatting changes what a value looks like; it cannot recover one Excel already replaced. That mechanism is behind the two complaints we hear most, covered in stop Excel removing leading zeros and stop Excel turning long numbers into 4.5E+15.

Double-clicking fails a second way too: Excel splits the file on your Windows list separator, so a comma file on a German machine opens in one column. If your file has not split into columns yet, start with why your CSV opens in one column.

The Correct Route: Import, Do Not Open

Importing hands you the type decisions instead of making them for you. In Microsoft 365 on Windows:

1. Open a blank workbook — not the CSV — and go to Data › From Text/CSV, which Microsoft puts in the Get & Transform Data group. Choose the file.
2. In the preview, check the dropdowns. File Origin is the encoding — 65001: Unicode (UTF-8) suits most modern exports — and Delimiter must match what is in the file.
3. Use the third dropdown, Data Type Detection, to turn automatic detection off. Microsoft describes the effect plainly: every column then defaults to Text. Nothing is reinterpreted, and it is the fastest safe import there is. The dropdown’s other two settings base detection on the first 200 rows or on the whole file.
4. For real numbers and dates in most columns and text in a few, click Transform Data instead, select each identifier column, set Data Type to Text, and choose Replace current when asked.
5. Click Close & Load, then File › Save As and pick Excel Workbook (*.xlsx).

One thing to watch in Power Query: if it has already added an automatic Changed Type step, setting the type afterwards will not restore what that step removed. Delete Changed Type in the Applied Steps pane and set types yourself. If the preview reads 742 rather than 00742, you are too late.

On a Mac and in Older Versions

Excel for Mac has the same connector under a different name: Data › Get Data (Power Query) › Text/CSV, which Microsoft documents as generally available to Microsoft 365 subscribers on Excel for Mac version 16.69 (23010700) or later; the Power Query Editor opens from the same menu. Older builds have only Data › From Text (Legacy), whose last step still lets you mark columns as Text. Excel 2016, 2019 and 2021 keep the connector on the Data tab, and Microsoft notes that if there is no Get Data button you can reach it through New Query › From File.

Turn Off Automatic Data Conversion

Newer Excel can disable the conversions at the source, protecting you even when somebody double-clicks a file. On Windows go to File › Options › Data; on a Mac, Excel › Preferences › Edit. Microsoft documents four checkboxes:

• Remove leading zeros and convert to a number
• Keep first 15 digits of long numbers and display in scientific notation
• Convert digits surrounding the letter “E” into a number in scientific notation
• Convert continuous letters and numbers to a date

There is a fifth, separate one worth turning on whatever you do with the other four: “When loading a .csv file or similar file, notify me of any automatic data conversions.” It does not prevent anything; it tells you a conversion happened, which is the part you currently have to guess.

Microsoft lists this feature for Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel 2024 and Excel 2024 for Mac. If File › Options › Data has no Automatic Data Conversion section, your version does not offer it and importing is your only protection. Note also that the settings cover typing, pasting and find and replace as well as opening files, so they change how the whole application behaves — and a colleague opening the same file has not made the change.

When the File Is Too Big for a Worksheet

A worksheet holds 1,048,576 rows and 16,384 columns, and a CSV with more rows cannot become a normal workbook: Excel loads what fits, warns that the file was not loaded completely, and if you then save, the rest is gone. Either split the file into parts of up to a million rows with the CSV splitter, split out only the rows you need with split by column value, or load the whole thing into Excel’s Data Model for PivotTables rather than editable rows. Excel’s row limit explains the ceiling and opening a CSV that is too large for Excel compares the six routes.

Google Sheets as an Alternative

Sheets works if you have no Excel licence or want to share the result. Open a spreadsheet, choose File › Import, upload the file and check Separator character. Google documents four choices: Detect automatically, Tab, Comma and Custom — detection can recognise semicolons, and Custom is there if it guesses wrong. The dialog also offers an option to convert text to numbers, dates and formulas; untick it and identifier columns keep their leading zeros. Then download the result as .xlsx. Check the limit first: Google publishes 10 million cells or 18,278 columns per spreadsheet, so a 20-column file runs out at 500,000 rows. Google began doubling the cell limit to 20 million in September 2026, rolling out gradually.

LibreOffice Calc, Free and Precise

LibreOffice Calc is free, runs on Windows, macOS and Linux, and its Text Import dialog is more explicit than anything in Excel. Opening a CSV always shows it, with the Character set, the separator, the String delimiter, and a Column type you pick per column in the preview grid: Standard, Text, Date (DMY), Date (MDY), Date (YMD), US English or Hide. Two more options matter — Format quoted field as text, which imports a fully quoted value as text, and Detect special numbers, which you want off. Set identifier columns to Text, then save as an Excel workbook. Its row ceiling is the same 1,048,576.

Check the Result Before You Send It

1. Look at your identifier columns. Are the values left-aligned (text) or right-aligned (numbers)?
2. Click a long number and read the formula bar, not the cell: it shows what is stored.
3. Check the row count — Ctrl + End jumps to the last used cell. See how to count the rows in a CSV.

When You Should Not Convert at All

A lot of CSV damage happens because someone converted a file nobody wanted as a spreadsheet. Do not convert when:

• The file is going into another system. Import tools for CRMs, e-commerce platforms, payroll and accounting software want plain CSV. An .xlsx either fails or forces another export, and that round trip through Excel is where the leading zeros disappear.
• The job is mechanical. Splitting, merging, deduplicating or re-encoding do not need a spreadsheet. Our splitter, merge tool and duplicate remover copy every row as its original text.
• Only the delimiter or the encoding is wrong. Then you want a corrected CSV, not a workbook. The encoding and delimiter tool re-saves as UTF-8, optionally with the byte order mark Excel looks for, and switches between comma, semicolon, tab and pipe.
• You are archiving the data. CSV will still be readable in thirty years. A proprietary workbook format is a bet on a vendor.

To be plain about what we do: our tools read and write CSV text only. They do not produce .xlsx files, do not open Excel files, and do not edit cells, chart or sort. They prepare the CSV — right delimiter, right encoding, a size that fits — so the import you run in Excel, Sheets or LibreOffice goes cleanly the first time. Going the other way, CSV UTF-8 vs CSV explains which format to save a workbook back out in, and combining CSV files into one workbook covers putting several CSVs on separate sheets.