AI how-to notes · 2026-09-28 · docs reviewed 2026-09-28 · 한국어 원문

Excel CSV leading zeros disappearing? Import product codes and long IDs as text

Keep the identifier intact

You open an order export and a product code such as 000042 becomes 42. A long order ID appears with E+ in the middle. Before saving anything, keep an untouched copy of the CSV. Then import the identifier columns as Text and compare them with that copy.

This guide covers Excel for Windows desktop with Power Query's From Text/CSV import. It is based on Microsoft documentation reviewed September 28, 2026. We did not run these steps in Excel; the sample and acceptance criteria below are a proposed reader check, not measured results.

Check whether the original still has the digits

Open the untouched CSV in a plain text editor. Find one affected record and compare it with Excel. This tells you where to continue:

For this workflow, treat SKUs and order IDs as labels. Quantities and prices can remain numeric. Decide by the column's purpose rather than by whether its characters happen to be digits.

Excel worksheet numbers have a maximum precision of 15 significant digits. A long ID converted to a number can therefore lose digits; changing its cell format to Text afterward does not bring them back. See keeping leading zeros and large numbers.

An E+ display is a separate issue: scientific notation can shorten the display without changing the stored value. Check the value in the formula bar against the untouched CSV. Changing the display format cannot repair digits already lost during conversion. Microsoft's scientific-notation guide distinguishes number formatting from the actual cell value.

Import through Power Query

1. In Excel, choose Data > From Text/CSV and select the untouched file.
2. Check the delimiter in the preview, then choose Transform Data to open Power Query before loading the sheet.
3. If Applied Steps contains an automatic Changed Type step, select it. Select each identifier column and change its data type to Text. If offered Replace Current, use it to replace that type conversion rather than append another one.
4. Compare the complete identifiers with the original. If they already differ in the preview, inspect the steps as described below before loading.
5. Once the preview matches, select Home > Close & Load.

Microsoft's CSV import instructions explain the preview and Transform Data route. Its leading-zero guide documents Text, Replace Current, and Close & Load.

Inspect an automatic Changed Type step

Power Query can infer column types from CSV contents and add a Changed Type step. The Power Query data-type documentation explains this automatic detection. In Applied Steps, select the step immediately before Changed Type, then select Changed Type, and compare the identifier in both previews. Selecting a step shows its output, as documented in Using the Applied Steps list.

If the characters are intact before the numeric conversion, replace that conversion with Text. For a fresh import with no later transformations, you can instead delete the automatic Changed Type step and assign the column types again: identifiers as Text, quantities as numbers. This re-evaluates the query from the intact source. Adding a Text step after a conversion that has already removed zeros, lost precision, or produced an error cannot reconstruct the original identifier. If the value is already damaged before that step, return to the untouched CSV.

Keep other transformations unchanged while diagnosing the identifier. If you also rename columns, remove duplicates, and change dates, comparing the first import with the original becomes harder.

Try a small fictional order file

Copy this original sample into a plain text file named sample-orders.csv. Import it using the steps above, with sku and order_id as Text and quantity as a number.

sku,order_id,quantity
000042,01234567890123456789,2
008510,98765432109876543210,1

Check the loaded cells against these acceptance criteria:

• Both product codes contain six characters, including the initial zeros.
• Both order IDs contain all 20 characters in their original order.
• The first order ID starts with zero and ends with 6789.
• Each quantity stays attached to the correct product and order.

These criteria specify what to inspect. They are not recorded results. After the sample, repeat the comparison with your own affected records. Include a code from the bottom of the export, not just the first few rows.

Optional automatic-conversion settings

Microsoft lists these controls for Excel for Microsoft 365 and Excel 2024, including their Mac editions. On Windows, look under File > Options > Data > Automatic Data Conversion. Before opening a CSV normally, you can disable conversion of leading-zero text and long numeric text to numbers. Microsoft lists the controls in data import and analysis options.

These settings cover actions such as directly opening CSV/text files, typing, and pasting from external sources. They do not directly affect Power Query imports: the Text types and Changed Type step above still need attention. They also do not recover characters already lost. See Microsoft's automatic-conversion scope.

For a shared import procedure, record the chosen column types alongside the file source so a colleague can repeat the process. If you export another CSV afterward, inspect that output in a text editor too. Reopening it normally in Excel introduces another interpretation step.

If the digits were already lost in the source you received, ask the sender for a fresh export. Guessing zeros or the ending of an order ID can create a different identifier.

Related notes

If the imported IDs still fail to match, continue with VLOOKUP errors and mismatched values. For a different task, see combining Excel sheets and files.