feature: auto-quote zero padded numeric values in CSV

[kulnor]

I regularly run into CSV files containing zero-padded numbers that are not properly quoted. These can be identifiers, multi-digit codes, a US zip code, etc. For example:

person_id, us_zip,  education_level
0001,00123,03
0002,01234,08
0003,12345,99

When read by software, these are interpreted as a numeric data type (rightfully so), which then drops the leading zeros, causing various issues (invalid values, data loss, mismatched codes, incorrect data type, etc.).

The proper behaviour is to surround these with quotes, like:

person_id, us_zip,  education_level
"0001","00123","03"
"0002","01234","08"
"0003","12345","99"

So... could such a quality-of-life utility be added to our favorite QSV toolkit? It would essentially parse a CSV file and either update it or create a new properly encoded version.

I unfortunately do not think there is a fix for this in `\tab or other delimited files (of fixed ascii), the option then would be to convert to CSV (which such a utility may also properly take care of?).

Beyond this, parsers and other inference routines could be instructed to take this into account (which typically requires a double pass or top rows scan).

Thoughts?

[kulnor]

Interestingly, and to my surprise, this does not seem to be in the CSV specification (RFC 4180). The purpose of double quotes in CSV is strictly structural, not for data typing (e.g., allow commas in CSV).

As a result, Polars, Pandas, DuckDB, Spark, and others would still treat these values as numeric (they strip the quotes...)

So the only way to properly handle this is to inform the parse tha these are of string data type.

I actually added a --leading-zeros flag to a parse I was working on to handle this. But I was hoping that the quoting would render this extra processing unnecessary. Looks like I was wrong...

So my request can be ignored and closed. Keeping it here for someone else to read about and for archival purposes...

[jqnatividad]

Hi @kulnor , thanks for sharing your discovery.

The simplicity of RFC 4180 is its greatest strength and the same time, its greatest weakness :)

That's why you may want to check out https://github.com/jqnatividad/csv-nose, the paper it was based on, and its accuracy compared to other CSV sniffers.

The different CSV test suites show how "flexible" the specification is and how CSVs are implemented IRL.

As for the postal code processing, I do have special handling in stats for valid integers with leading zeroes - recognizing them as String, instead of Integers.

[kulnor]

Thanks for chiming in. Definitely need to get my nose in nose...

Great to hear that stats takes the leading zeroes into account. I should have known

Is the same true for qsv shema? I was going to request this as a new feature (infer integers with leading zeroes as a string).
In my Python UNF packages, I added an option to trigger this.

[kulnor]

Never mind. Just tried, and qsv schema does indeed pick up the leading 0 and types the variable as a string. This is great.