CSV to Excel: Open a CSV Without Losing Leading Zeros, Long Numbers or Accents (Excel 365, Tested)

The test CSV

Three made-up orders. The columns are order number (00123), name (Korean and accented Latin), postcode (04524), mobile number (01012345678), a 16-digit card number, product code (1E5, 2E10, AB-7), option (3-1, MAR1, 1/2) and amount ("1,250,000", quoted because it contains commas). Everything except the amount should stay exactly as written; the amount should be a number you can add up.

주문번호,이름,우편번호,휴대폰,카드번호,상품코드,옵션,금액
00123,김민준,04524,01012345678,1234567812345678,1E5,3-1,"1,250,000"
00124,José Núñez,06236,01098765432,9876543298765432,2E10,MAR1,"89,000"
00125,Zoë Müller,13529,01055556666,4000123412341234,AB-7,1/2,"15,900"

I saved it as UTF-8 without a BOM, UTF-8 with a BOM, and ANSI (this PC's system code page is Korean CP949, which has no accented letters, so those two names were written without accents). Test machine: Windows 11 and Excel for Microsoft 365 (version 16.0, build 20527) with default options. This PC runs Excel in Korean; menu names below are Microsoft's English names for the same commands.

Way 1: double-click the CSV — what changes

Microsoft's support article Import or export text (.txt or .csv) files says that when you open a CSV directly, Excel uses its current default data format settings to interpret each column. Here is what that did to the test file.

Microsoft's Keeping leading zeros and large numbers article explains that Excel removes leading zeros and keeps only 15 digits of precision, so a 16th digit becomes 0. Changing the cell format to Text afterwards does not bring the original value back. When I used Save As > Excel Workbook (.xlsx) at this point, the file stored 123, 4524, 1234567812345670, 100000 and date values — so a CSV to Excel conversion has to start with the right way of opening.

Garbled text only with UTF-8 without a BOM

Of the three files, only UTF-8 without a BOM broke the names when double-clicked: José Núñez became Jos챕 N첬챰ez on this Korean-locale PC and the Korean name turned into unrelated characters. The UTF-8 file with a BOM and the ANSI file showed the names correctly; the numbers and dates changed the same way in all three. Exports from web apps are often UTF-8 without a BOM, so broken accents right after opening a downloaded CSV usually mean this case.

Way 2: Data > From Text/CSV — import the values as they are

The same Microsoft article describes the other route: import the file instead of opening it. In Excel 365 that is done by Power Query. Start from a blank workbook.

1. 1On the Data tab choose From Text/CSV and pick the CSV file.
2. 2The preview window shows File Origin, Delimiter and Data Type Detection at the top. For the UTF-8 file without a BOM, File Origin was set to 65001: Unicode (UTF-8) automatically and every name looked right. If text looks broken here, change File Origin.
3. 3Change Data Type Detection from Based on first 200 rows to Do not detect data types. The preview should now show 00123, 04524, 01012345678, the full 16-digit number, 1E5 and 3-1 unchanged.
4. 4The header row is now a data row under Column1–Column8, so choose Transform Data and, in the Power Query Editor, Use First Row as Headers.
5. 5If a new Changed Type step appeared under Applied Steps on the right, delete it with the X next to it (see the trap below).
6. 6Change only the columns you need to calculate: click the type icon (ABC) on the amount column header and pick Whole Number. Then Close & Load.

If you simply load with the default detection, File Origin is right but the values still change: order number 123, postcode 4524, mobile 1012345678, card number 1.23457E+15 and the options as dates such as 2026-03-01. The only difference from double-clicking was that 1E5 stayed text. In other words the default import fixes the encoding and nothing else.

Trap: Use First Row as Headers converts the columns again

With detection off and every column as text, clicking Use First Row as Headers in the editor added a Changed Type step right after Promoted Headers without asking. The formula bar showed Int64 for order number, postcode, mobile, card number and amount, and date for the option column, and the preview was back to 123, 4524 and 1.23457E+15. Turning detection off in the first window is not enough on its own.

Deleting that Changed Type step kept the promoted headers and brought the values back to 00123, 04524, 01012345678, 1234567812345678, 1E5 and 3-1. Setting only the amount column to Whole Number then turned 1250000, 89000 and 15900 into numbers, and after Close & Load the amounts added up to 1,354,900 while the other columns stayed text.

Excel 365's Automatic Data Conversion options

Microsoft's Set automatic data conversions article lists options in Excel for Microsoft 365 and Excel 2024 that stop these conversions when you open a .csv or .txt file. They are under File > Options > Data > Automatic Data Conversion:

• Remove leading zeros and convert to number — when off, 00123 stays text
• Keep the first 15 digits of long numbers and display in scientific notation if required — when off, 16 or more digits stay text (the article's example is a credit card number)
• Convert digits surrounding the letter "E" into a number in scientific notation — when off, 123E5 stays text
• Convert "date-like" combinations of continuous letters and numbers into a date — when off, JAN1 stays text, though the article notes JAN 1 or JAN-1 may still become dates

The article also says these options don't directly affect data imported by using Power Query, so the detection setting in Way 2 still matters. And because values with hyphens or slashes such as 3-1 may still be read as dates, Way 2 is the safer choice for date-like codes. All results here were measured with the default options; I did not change them on this PC.

Comparison

When the result looks wrong, check in this order

1. 1Names look like Jos챕 or random symbols — UTF-8 without a BOM was double-clicked. Import with From Text/CSV and check File Origin is 65001.
2. 200123 shows as 123, long IDs as 1.23E+15 — automatic type detection. Choose Do not detect data types in the preview.
3. 3It changed again after you turned detection off — Use First Row as Headers added a Changed Type step. Delete it in Applied Steps.
4. 4Amount totals are 0 or missing — every column is text. Set just the columns you calculate to Whole Number or Decimal Number.

If you need to send the cleaned table back out as CSV, the JSON to CSV guide on this site measured that CSV UTF-8 (Comma delimited) writes a BOM, which keeps accents safe in other programs. Double-clicking that CSV in Excel again drops the zeros again, though, so keep the .xlsx for viewing in Excel.

FAQ