Skip to content
FrAIFlow

Solutions

Keep leading zeros and long IDs in CSV imports

A product code such as 00123 can survive CSV export and still lose its zeros when a spreadsheet opens it. CSV stores text fields, not spreadsheet cell types. The important question is where the first unwanted numeric conversion happens.

R and pandas users have reported leading-zero values that looked correct before export but appeared shortened in Excel. A separate report traced lost long-number digits to saving through Excel. These are useful examples of a type boundary, not proof that every CSV exporter removes zeros.

A careful Excel import, from file to checked cells

Make a backup before trying to repair the workbook. Open the CSV in a plain-text editor first. If it still says 00123, there is something worth protecting. If the raw file already says 123, formatting Excel cells cannot tell you whether the original had one zero, two or none.

  1. In Excel, start with an empty workbook and choose Data → From Text/CSV. Select the original CSV rather than a copy that Excel has already resaved.
  2. Check the preview’s delimiter and encoding. The product-code header and sample rows should line up before you load anything.
  3. Choose Transform Data. In Power Query, inspect Applied Steps. If an automatic Changed Type step has converted the identifier to a number, remove or correct that conversion before continuing.
  4. Select the identifier column and choose Text as its data type. Repeat for postal codes, account codes and other fields that require exact characters. Keep actual quantities numeric.
  5. Use Close & Load, then compare complete values with the original file, including at least one leading-zero code and one long identifier.

Menu availability varies by Excel version. The linked Microsoft instructions cover the import approach and supported automatic-conversion settings. This is a documented workflow, not a claim that this article’s author ran your Excel installation.

A useful acceptance sample is 00123 alongside 12345678901234567. The first catches zero removal; the second catches precision loss. Looking only at a short ordinary number such as 123 would miss both problems.

If the workbook shows scientific notation, widen the column and inspect the full underlying value before deciding what happened. Scientific display alone is not proof of lost digits. Conversely, switching a rounded number to a non-scientific format does not recover its original digits. Compare against the source, not a more reassuring-looking cell.

Finally, test the handoff. If you export to CSV again, inspect that new file as text, then import it using the recipient’s intended process. The text type belongs to the workbook, not to the CSV format. Include import instructions, or use a workbook with text-typed codes when that better fits the recipient’s workflow.

Why quotes do not protect identifiers

Illustrative acceptance sample. Both imported identifiers must match the untouched CSV exactly.
Illustrative acceptance sample. Both imported identifiers must match the untouched CSV exactly. Open the image for a larger view.

A small synthetic example wrote and read 00123 and 12345678901234567 unchanged as text. Explicit JavaScript Number conversion removed zeros or rounded digits. The saved output is a JavaScript test, not an Excel simulation. Excel’s separately documented numeric precision is 15 significant digits.

Choose a reliable handoff

If recipients must double-click with no import instructions, consider an XLSX workbook with explicitly text-typed identifier cells. Quoting a field with ordinary CSV double quotes protects separators; it does not reliably tell a spreadsheet to store that field as text. Keep quantities that need arithmetic separate from codes that need exact identity.

Never guess lost digits. A padded display may look right without proving the stored code is correct. Padding is justified only by a trusted fixed-width schema and intact remaining digits; rounded long identifiers need recovery from the source. Avoid formula wrappers as a shortcut: they change the data’s meaning and can introduce formula-handling risks. Test the actual recipient’s import path before sending important identifiers.

Text IDs compared with JavaScript numbers: converting to numbers removes a leading zero and changes a long ID.
Supporting explanation, not an application screenshot.
Recorded JavaScript output shows 00123 becoming 123 and a long ID changing after numeric conversion. This is not an Excel test.
Saved local test output. Open the image to inspect the original evidence.
Sources, examples and further reading