Practical spreadsheets and data

Import CSV into Excel without losing leading zeros or ID digits

Import identifiers as text before Excel converts them. Then compare the loaded values with the original CSV, including the last digit of long IDs.

By coru · Published 2026-10-21 · Updated 2026-10-21

Start with an untouched source file

A product code such as 00123 is an identifier: its characters matter even if they are all digits. A quantity such as 2 is a number you may want to add. Tell the importer which is which before loading the file.

These steps target desktop Excel for Microsoft 365 and Excel 2024 on Windows, using Microsoft’s documented Text/CSV import. Labels can vary by build and display language. The Mac and web interfaces are outside this walkthrough. We checked the documentation on 21 October 2026.

Keep the downloaded CSV unchanged and work from a copy. If you have already opened it and the values look wrong, close it without saving over the source. Open the original in a plain-text editor and inspect a known ID. If 00123 is still there, you can import it again. If the only remaining copy already contains 123 or altered final digits, changing the cell format cannot establish what the original value was. Obtain a fresh export or another intact copy.

1. Make a small check file

Use this fictional inventory before trying important data. In a plain-text editor, save the following three lines as coru-id-check.csv with UTF-8 encoding. Copy each listed item as one complete line, without the list marker or extra spaces. The comma inside the quoted note belongs to that field.

sku,long_id,exponent_code,date_code,name,note,quantity
00123,1234567890123456,1E10,03-04,Ёлка,"green, small",2
00456,1234567890123457,2E03,04-05,Берёза,plain,10

2. Choose encoding, separator and text before loading

Open a blank workbook. Choose Data → From Text/CSV, or Data → Get Data → From File → From Text/CSV, and select your copy. In the preview, set File Origin to UTF-8 (65001) for this sample and Delimiter to Comma. Confirm that the headings separate into seven columns and Ёлка is readable.

Under Data Type Detection, choose the option that does not detect data types. Microsoft’s Text/CSV connector documents that this leaves columns as Text. Select Transform Data to inspect the result before loading. If the first row is still treated as data rather than headings, use Use First Row as Headers.

In the editor, confirm Text for sku, long_id, exponent_code and date_code, using the type selector beside each column name. Keep name and note as text too. Change only quantity to Whole Number if you need arithmetic. Then choose Close & Load.

If the preview has already detected types, do not assume that adding Text at the end repairs earlier conversions. In Applied Steps, inspect any Changed Type step. For this fresh sample query, remove that automatic step, check that the original characters reappear, and then assign the types above. Microsoft also documents replacing the current type conversion when its dialog offers Replace Current. The goal is to read the source characters as text before a numeric or date conversion can change them.

3. Compare values, not just how the cells look

Check the loaded table against the plain-text source. Click a cell and inspect its full value in the formula bar when the column is too narrow. For this sample, every check below must pass:

Structure: seven columns and two data rows, with the header excluded from the row count.
Leading zeros: sku contains exactly 00123 and 00456; each has five characters.
Long IDs: long_id contains exactly 1234567890123456 and 1234567890123457. They are different 16-character strings; check both final digits.
Code spelling: exponent_code remains 1E10 and 2E03; date_code remains 03-04 and 04-05. Here these are codes, so no date interpretation is wanted.
Text: name contains Ёлка and Берёза. The first note is green, small in one cell, without its enclosing CSV quotes.
Quantity: 2 and 10 are in the quantity column; if you deliberately converted that column to Whole Number, their sum is 12.

Why quotes and number formats are insufficient

CSV quotes mark field boundaries. For example, "green, small" keeps an internal comma within one field. They do not declare an Excel cell type. Wrapping "00123" in quotes is therefore not a reliable substitute for selecting Text during import.

Excel’s numeric precision is limited to 15 significant digits. An ID with 16 digits must remain text if every digit matters. Scientific notation is a display style, so its appearance alone does not tell you whether data was lost; compare the full value with the original.

A custom number format such as 00000 can display a number using five positions. That is a display rule, not evidence that an unknown source code had five digits. It cannot recover discarded digits from a long ID. Likewise, applying Text after conversion cannot reconstruct lost characters. Treat identifiers as text at the point of import.

If your preview differs

Everything is in one column: check the file’s actual separator and choose it in the import preview. Some exports use semicolons or tabs. Do not replace every comma in the file; commas may belong inside quoted fields.
Letters are garbled: choose the encoding used by the exporter. UTF-8 is correct for our sample, not a universal answer for every file. Microsoft documents normal opening of UTF-8 CSV with a BOM; an explicit import also lets you choose the encoding. A BOM does not specify identifier column types.
Your interface lacks the needed import controls: Microsoft documents a legacy Text Import Wizard. Enable From Text (Legacy) under File → Options → Data, then open it under Data → Get Data → Legacy Wizards. Select Delimited and the correct file origin, select the separator and double-quote text qualifier, then select each identifier column and choose Text in step 3 before finishing.

Save a workbook; verify CSV again when exchanging it

Save the working result as an .xlsx workbook. CSV stores field text rather than workbook cell-type settings. Exporting another CSV and double-clicking it starts another interpretation of the text; it does not prove that the exported bytes have changed.

If a recipient needs CSV, export to a new filename and inspect that file in a plain-text editor. Check the exact IDs again, then reimport it with explicit text types. Tell the recipient the encoding, separator and identifier columns. Keep the workbook and untouched source until the exchange has been checked.

Excel for Microsoft 365 and Excel 2024 also provide Automatic Data Conversion settings under File → Options → Data. These can change some conversions during ordinary opening or entry, but Microsoft explicitly says they do not directly control Power Query imports. They do not replace this import check.

What our example verifies

Our local Python CSV checks preserve all seven fields, both distinct long IDs, leading zeros, Cyrillic text and the quoted comma. UTF-8 samples with and without a BOM produce the same values when decoded appropriately. These checks verify the sample and its expected answers. They do not certify any Excel build or reproduce its user interface.

For your own import, record the source filename, Excel version, encoding, delimiter, identifier columns and a few exact before/after values. Include the last row as well as the first: a correct preview does not establish that every record in a larger file was imported correctly.