Protect Leading-Zero Identifiers During CSV to JSON Conversion

Convert a synthetic roster to JSON while detecting the tool's automatic numeric inference on identifier-like fields.

By DevSphere Editorial · · 5 min read

Open CSV Tools

Spot the type conversion risk

A CSV cell has no built-in type declaration, so a converter has to choose whether a value is text, a number, a boolean, or something else. CSV Tools attempts to convert numeric-looking cells to numbers in its JSON tab. That can be useful for quantities but dangerous for codes with leading zeros. This tutorial uses a fictional equipment roster with identifiers such as 0042, a numeric count, and a boolean availability marker. The intended rule is that assetCode remains a string even when it contains only digits. We will inspect the generated JSON and decide whether the inferred type is acceptable. Keep examples invented and do not paste actual staff rosters or serial numbers. For production exports, use a schema-aware conversion step that preserves declared column types.

assetCode,count,available
0042,5,true
A-017,2,false

Parse the rows and check settings

Paste the sample into CSV Input. Select comma as Delimiter, double quote as Quote, and keep First row is header checked. Check the summary shows two rows and three columns, then switch to the JSON tab. The converter builds one object per row using the header labels. Exact true and false strings become booleans, and nonempty numeric-looking strings are passed to parseFloat. As a result, assetCode 0042 may become number 42, losing its leading zero. The second ID remains a string because it contains a letter and hyphen. This is a meaningful data change even though the JSON is syntactically valid. Do not assume the converter knows which columns are identifiers.

Choose a type-preserving workflow

If identifiers must preserve leading zeros, do not accept the inferred JSON blindly. Keep CSV as the source, or use a workflow that lets you declare assetCode as a string and count as a number. The tool has no per-column type controls. You could alter a disposable sample to include a nonnumeric prefix to illustrate the issue, but changing actual identifiers just to trick inference is not a valid data migration. If you have a required downstream object shape, map columns explicitly in code and test representative values such as 0042, 0000, empty, and a normal count. For the existing tool, the JSON output is a quick inspection view. Copy or Download is available in the JSON panel, but exporting a lossy representation does not restore lost digits.

[
  {"assetCode": 42, "count": 5, "available": true},
  {"assetCode": "A-017", "count": 2, "available": false}
]

Review SQL separately

The SQL tab provides a different mapping that also attempts to recognize simple numbers and true/false values. It inserts the selected Table Name and headers into SQL text and quotes ordinary text values, escaping single quotes in values. The generated SQL is not a substitute for prepared statements or a database schema. Table and column identifiers are inserted as provided, so use only trusted, valid names and never use untrusted headers as executable SQL identifiers. The interface does not connect to a database or validate column types. A CSV field that must remain a zero-padded code needs an explicit database character type and a compatible insert workflow. Check each sample row in SQL output and compare with the database's declared schema before considering any generated statement.

Troubleshoot and verify type fidelity

If JSON output is unexpectedly empty, confirm the source is parsed into data rows and that the first-row header option reflects the actual file. A missing header makes synthetic Column names and can shift expected keys. Verify the critical case by checking whether the JSON contains assetCode as "0042" or as 42; the latter signals lost formatting. Also confirm count is numeric five and available is boolean true. CSV Tools' parser is lightweight: it handles configured delimiter and quotes but does not offer a robust schema or detailed error report for every malformed CSV. Validation must occur in the destination pipeline. If type fidelity matters, use an explicit importer and tests rather than editing output by hand. Clear the editor between files and retain the original CSV for recovery.

The same issue can affect dates, postal codes, version strings, and measurements that happen to contain only digits. A CSV reader cannot know whether 0007 is a quantity or an identifier without a contract. Decide column types before conversion and include boundary fixtures such as all-zero codes, leading zeros, blank cells, and a value with a decimal point. Then assert not only the JSON value but also its type and exact spelling. Avoid trying to preserve a code by changing its real content; use an explicit mapping or a parser that supports column schemas. If a spreadsheet is involved, verify its import settings as well, because the spreadsheet may independently strip zeros even when the JSON conversion is correct. Keep raw source and output paired for audit.

The header option is also part of type interpretation. If the first row is mistakenly treated as data, each object receives a generated Column name and the literal headings become values, which can make a preview appear plausible while the object shape is wrong. Check that First row is header matches the source before opening JSON. Then inspect one output object from each distinct category and confirm that identifier spelling, quantity type, and boolean type match the intended model. For fields that must remain strings, use an importer with per-column declarations rather than trusting automatic inference. A conversion test should include both ordinary IDs and zero-padded IDs so a regression cannot pass merely because a typical alphanumeric code survived.

Browse all tutorials