How to Open a CSV in Excel Without Changing Your IDs
The warehouse export says 000731. Excel shows 731. You can still see the quantity beside it, so the file looks fine until the stock lookup starts missing items. The same import can change a long tracking ID, and the last few digits may already be gone by the time you see it.
The fix: Import the CSV through Data → From Text/CSV and set identifier columns to Text before any step converts them to numbers. Keep quantities and prices in their usual numeric types.
1. Check the CSV before changing the workbook
Open a copy of the original CSV in a plain-text editor. If it contains 000731 and Excel shows 731, the change happened during import. If the CSV itself says 731, go back to the system that produced it and check how it exports IDs. Wrapping the ID in CSV quotes is no guarantee Excel will keep it as text.
Try this with a small file named stock-export.csv. Save these lines in a plain-text editor. The third row is there on purpose: 000731 and 731 represent different items in this example.
ItemID,TrackingID,Quantity
000731,123456789012345678,24
A-204,123456789012345679,8
731,123456789012345680,6
2. Import the ID columns as text
Open a blank Excel workbook. Select Data → From Text/CSV (under Get Data in some versions), choose the file, then select Transform Data. If the preview offers a data type detection setting, choose Do not detect data types.
In Power Query, check Applied Steps. If an automatic Changed Type step already converted the IDs, remove that step before any later transformations. Select ItemID and TrackingID and set their data type to Text. Set Quantity to Whole Number, then select Close & Load.
Check the loaded values against the raw file. You should see 000731 and 731 as separate IDs, all 18 digits of the tracking IDs, and three quantities totaling 38. That total would still be 38 if the IDs changed, so check the keys themselves.
3. Recover a workbook you already opened
If Excel converted the ID to a stored number, switching the cell format to Text won’t recover the original characters. A custom number format can display extra zeros, but the underlying value stays numeric. And if Excel converted a long identifier to a number, it can retain only 15 significant digits. Displaying the full number may reveal zeros where later digits used to be.
Reimport from the unchanged CSV or export a fresh copy. If you only have the converted workbook, check the upstream records. Add zeros back only when the source system confirms a fixed width for that specific ID field. In the sample above, padding every item ID to six digits would turn two different records into 000731.
Check for altered IDs before the next import
If you have an original export and a processed copy, compare them using a field that stayed stable, such as a transaction line number. If the two files lack a separate stable key, list missing IDs and possible matches for review. Similar-looking IDs can belong to different records.
Compare the ID columns in these two files. Show which values lost leading zeros, which long IDs changed, and where two different source IDs ended up with the same imported value. Cite the source cells and flag uncertain matches.
Microsoft reference on keeping leading zeros and large numbers in Excel.
The stock export uses synthetic IDs. Verify the identifier rules in your source system before repairing an export.
Dates need their own import rule. If 07/04 appears in July instead of April, follow the three-receipt CSV date check. It preserves the raw text and verifies both the $2,600 overall amount and its monthly split.
Try the check at Jolene.pro and follow Jolene.pro on LinkedIn for more spreadsheet guides.
1 comment