Keep Leading Zeros and Long IDs in Excel CSV Imports
Product code 00072 turns into 72 when you open the CSV in Excel, and a long order ID quietly loses its last digit. How do you bring the file in so both come back exactly as sent?
If a CSV must return to an inventory system after editing, its identifiers need to survive the entire round trip. A product code such as 00072 must still contain five characters in the delivered file. Making a worksheet display five digits does not by itself establish what the receiving system will read.
The first useful check is outside Excel: open the original CSV in a plain-text editor. If the zero and all the long ID's digits are still present, import that original again with the identifier columns set to Text. If the original has already been overwritten with altered values, obtain a fresh export or an earlier intact copy.
Choose types by the job each column does
A quantity is a number you calculate with. An order ID is a sequence you use to find a record. Both may contain only digits, but they need different treatment. Microsoft's leading-zero guidance recommends text for codes that must retain their original characters.
| Column | Example | Import type |
|---|---|---|
| Product code | 00072 | Text |
| Order ID | 7316049281572469 | Text |
| Telephone | 02079460123 | Text |
| Quantity | 12 | Whole number |
Excel worksheet numbers have 15 significant digits of precision. A 16-digit identifier can therefore lose information when converted to a number. Widening a column to remove scientific notation changes the view, not the precision of a value already stored. Scientific notation alone does not prove corruption, either: compare the underlying value with the original identifier.
For two orders whose IDs differ only in the last digit, that final character is the entire distinction. Treating the column as Text preserves the sequence needed for matching. Converting it to a number to make the cells look consistent can erase the distinction you intended to preserve.
Import the original through Text/CSV
For recurring files, use an explicit import instead of relying on double-click behavior. The following route uses desktop Excel on Windows. Microsoft's CSV import instructions explain that opening a CSV uses current default format settings, while importing lets you transform data before loading it.
- Open a blank workbook and select Data → From Text/CSV.
- Select the untouched source file. In the preview, choose the correct delimiter and file encoding.
- If the preview offers Data Type Detection, select the option not to detect types, then choose Transform Data.
- Click the type icon beside each identifier column's heading and choose Text. Set quantity to a suitable numeric type through the same menu.
- Compare representative values with the original, then choose Close & Load.
Disabling inference makes columns start as Text in the Text/CSV connector. Available controls can vary by host and version. If that preview option is absent, enter the editor and inspect the type-conversion steps before loading.
In the example order file, the first three columns remain literal identifiers while quantity supports totals. This is more useful than making every column numeric, which damages codes, or leaving every column as text, which adds unnecessary work to calculations.
Encoding and delimiters solve different import problems. If names contain unreadable characters, match the source encoding. If the entire record appears in one column, choose the actual separator. Neither change restores a zero that has already been discarded by numeric conversion.
Remove the conversion that happened too early
Power Query may automatically create a Changed Type step. Its data-type documentation explains that unstructured sources such as CSV can have their types inferred from their values. That inference may classify your identifier as a number before you explicitly select Text.
Adding a new text conversion after the numeric step can simply turn the number 72 into the string 72. Instead, remove the initial unwanted conversion or edit it so that the identifier becomes Text at its first type-conversion step. If Excel asks whether to replace the existing conversion or add another step, replace the unwanted first conversion.
Then return to the preview and verify that 00072 is present. If the query has later calculations, review their inputs after removing a step: quantity may need its numeric type reapplied. The aim is to preserve identifiers while retaining the useful numeric columns.
Power Query's Whole Number type is a 64-bit integer that can hold up to 19 digits within its supported range. Excel worksheet numbers have 15 significant digits of precision. A long ID can therefore look intact in the query preview yet lose precision if loaded as a worksheet number. Set identifiers to Text in the query and retain that type through the final load.
Disable automatic conversions for opening and pasting
Excel for Microsoft 365 and Excel 2024 support automatic-conversion settings on Windows and Mac. On Windows, go to File → Options → Data. On Mac, go to Excel → Preferences → Edit. Find Automatic Data Conversion and disable the conversions relevant to the data you handle, following Microsoft's settings guide.
The leading-zero option and the long-number option are separate. Turning off the first preserves digit strings beginning with zero as text. Turning off the second preserves values with 16 or more digits as text. A file containing both telephone numbers and long order IDs needs both behaviors addressed.
These settings do not directly control Power Query imports. A saved query still needs the column types described above. They also do not repair existing cells: after changing the settings, start again with the intact source.
A discussion on r/excel starts with a user losing zeros while copying data from another platform, then struggling with lookups. For that workflow, preparing the destination identifier column as Text before pasting is useful too. Repairing only the visible appearance afterward can leave mismatched representations in the two lists.
Use the legacy import wizard when available
The Windows Text Import Wizard offers another explicit route. If it is hidden, enable From Text (Legacy) under File → Options → Data, then open it from Data → Get Data → Legacy Wizards. Select the source, choose a delimited file and the correct separator, and set each identifier column to Text in the final step.
Microsoft's wizard instructions distinguish the text qualifier from the column's data format. Quotes surrounding a CSV field keep a comma inside that field from splitting it into two columns. They are not a reliable declaration that the field must remain a text cell.
For example, quoting a product description containing a comma can keep the description together. You still need to specify Text for the adjacent product-code column. The separator tells Excel where a value ends; the column type tells it how to interpret that value.
What can be reconstructed after damage
A known fixed-width code can sometimes be rebuilt from its specification. If every code is exactly five characters, padding 72 to five places produces 00072. Without that specification, several different original strings could produce 72, so padding would be a guess.
Lost trailing digits in a long ID cannot be recovered by adding zeros or changing the display format. Obtain a new export from the system of record. If the damaged workbook contains other edits, retain it separately and reconcile those edits using another intact unique identifier. Without one, matching the rows automatically could assign an edit to the wrong record.
That recovery work is easier if the source CSV was preserved before editing. Keep the downloaded source unchanged and create a separate workbook for the transformations. The original then remains available when you need to distinguish an import mistake from an error already present upstream.
Verify the file you will actually send
Save the working document as XLSX to retain workbook features. When the receiving system requires CSV, select the intended worksheet and save a separate CSV export. Excel exports the current worksheet, as described in the export guide; a multi-sheet workbook does not become a multi-sheet CSV.
Inspect that exported file in a text editor. Check the leading-zero code, the entire long ID, the corresponding quantity, and the delimiter. Double-clicking it back into Excel may run automatic conversion again, obscuring whether the file itself was intact.
If the actual CSV contains the correct values but a colleague sees altered ones after opening it, provide the import types with the handoff. For this example, that means Text for Product code, Order ID, and Telephone, and Whole number for Quantity. If the colleague wants to edit in Excel rather than upload to another system, send the XLSX copy with the identifiers already stored as text.
For repeat exports, keep the column definitions beside the file specification. Test newly arriving long IDs and leading-zero codes against the source as well as familiar sample rows. The import steps can remain the same while the next file introduces values that an earlier sample never exercised.