Excel changed my CSV IDs. Catch the problem before import.

#data #tools

A CSV can open without an error and still lose the information you needed.

The awkward case is an identifier that looks like a number. An account ID of 00123 and a long ID of 9007199254740993 are strings of digits. If an importer turns them into numeric cells, their original spelling may not survive.

For identifiers, keep the text until you have a reason to convert it. Adding a numeric format later cannot recover digits that were already lost.

A small file that exposes the problem

Save this as import-check.csv, or use it as pasted text:

account_id,name,comment
00123,Alice,Ready
9007199254740993,Bob,=1+1

There are two records. Three values deserve review before a spreadsheet import:

00123 needs its leading zeros preserved.
9007199254740993 needs all of its digits preserved.
=1+1 may be interpreted as a spreadsheet formula rather than displayed as the original text.

The formula is deliberately harmless. These rows use synthetic data, so the example does not need real customer records.

Check the source before blaming the export

Python's standard CSV reader keeps these fields as text unless your own code converts them:

import csv
import io
import json

source = """account_id,name,comment
00123,Alice,Ready
9007199254740993,Bob,=1+1
"""

records = list(csv.DictReader(io.StringIO(source)))

assert records[0]["account_id"] == "00123"
assert records[1]["account_id"] == "9007199254740993"
assert records[1]["comment"] == "=1+1"

print(json.dumps(records, indent=2))

The result includes quoted JSON strings for both IDs:

[
  {
    "account_id": "00123",
    "name": "Alice",
    "comment": "Ready"
  },
  {
    "account_id": "9007199254740993",
    "name": "Bob",
    "comment": "=1+1"
  }
]

That gives you a reference to compare with the imported data. CSV quoting handles CSV structure; it is not a reliable instruction to a spreadsheet to use Text cells.

Excel and JavaScript have different numeric limits

Microsoft documents a 15-significant-digit limit for Excel numeric values. Long account numbers should be imported as text when every digit matters. Leading zeros can also disappear when a field is converted to a number.

JavaScript has a different boundary. Number.MAX_SAFE_INTEGER is 9007199254740991. Try this in a JavaScript console:

JSON.parse('{"account_id":9007199254740993}').account_id;
// 9007199254740992

JSON.parse('{"account_id":"9007199254740993"}').account_id;
// "9007199254740993"

Both JSON documents have valid syntax. Only the second represents this identifier as a string. Keeping an ID quoted avoids this particular Number conversion; calculations on genuinely numeric values need their own precision policy.

Choose the import type before loading the column

Use Excel's text/CSV import flow instead of relying on a double-click to choose types for you. In Power Query, set identifier columns to Text before numeric conversion loses information. Check any automatically added type-conversion step: changing a rounded value back to text does not restore the original ID. Menu labels vary between Excel versions.

After loading, compare the two IDs with the source. Check the actual cell value rather than judging only its display. If you export the workbook to CSV, close it and inspect the exported file too. A workflow that preserves an ID on the first open can still change it in a later step.

Review formula-like values separately

Preserving an ID is not the same task as preventing formula interpretation. The comment field demonstrates why: =1+1 can parse as a CSV string and later act as a formula in a spreadsheet.

OWASP's CSV injection guidance covers formula prefixes and explains why escaping must be tested against the destination. There is no universal rewrite that suits every spreadsheet and downstream consumer. A warning should show the original cell and let you choose a destination-specific treatment.

To try the example without uploading a file, open DataToolForge's Excel Import Preflight, paste the sample and run the check. The report is intended to surface import risks, not prove that a file satisfies every business rule. A broader CSV Import Preflight also checks file structure.

The useful habit is simple: compare the source text, the imported values and the next exported file. Check exact IDs and formula-like cells before using the data in another system.

Disclosure: DataToolForge is my project. AI assistance was used to draft and edit this article.

DEV Community