Add an option to ignore numeric values

[qwesda]

An option to threat numeric values as just text would be helpful. It could be represented by offering a none option in the decimal section of the footer bar. I came across this issue when I had to update a few line in a huge tab-separated file. The workflow should have been:

• processed the few line in numbers
• export to csv
• open with TableTool
• convert with appropriate settings

Since I wanted to compare the original and the new file with a diff tool I had to go back and replace the decimal point in all numbers to an arbitrary character so that TableTool didn't interpret them as numbers. Then make the conversion and replace the placeholder character back to the decimal point. Otherwise TableTool was truncating the decimal values (removing trailing zeroes) and the diff would highlight the whole file.

[jakob]

I think Table Tool should generally only modify columns when converting from one format to another. It should just leave values the way they are.

The way it currently attempts to parse numbers just cause pain and suffering (see also eg #41)

But unfortunately we're way too busy with Postico at the moment to give Table Tool the attention it deserves...

[qwesda]

It's not urgent - just thought I mention it.

[bbugh]

We just got bit by this issue too. Every CSV editor we've found (including my favorite TableTool) is removing significant leading zeroes. 😢

[fulldecent]

Can everyone please provide test cases here for exactly which csv file you are using, what you are behavior you are seeing and what you would like to see?

[bbugh]

I moved on to Easy CSV Editor which handles and many other quirks correctly, but the example case is very simple:

col1,col2
"00500",002350

I expect that to result in "00500" and 002350, but it's "500" and "2350" in TableTool.

There's a lot of reasons why significant leading zeroes are important, like 1) product numbers, 2) matching existing data with newly imported data, etc. IMO, CSV editors shouldn't be opinionated about the content of the data, and just preserve it as-is.

[fulldecent]

@bbugh Thank you for sharing. At first glance the expected behavior would be 00500 (string) and 2350 (number) for me.

Would anybody like to research if Excel, LibreOffice, and pandas.read_csv() handles these cases?

CSV does not have a standard format. So to understand best practice we can always compare to existing implementations.

[RichardFoxworthy]

I've found that TableTool displays numeric values with leading zeros removed, but when I save and open the file in a text editor, the leading zero is retained. Ideal would be to have option to not trim numeric values whatsoever.

My own usecase is to retain correct text formatting for phone numbers, which in my location always start with a leading 0

[fulldecent]

Here is a test case to document the SQLite behavior in interpreting "00500",002350.

Test case

cat > test.csv <<EOF
col1,col2
"00500",002350
EOF
sqlite3
sqlite> .mode csv
sqlite> .import test.csv test
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE test(
  "col1" TEXT,
  "col2" TEXT
);
INSERT INTO test VALUES('00500','002350');
COMMIT;
SELECT * FROM test;
00500,002350
sqlite> .exit

Results

• Input values with leading zeros are preserved
• Output does not preserve unnecessary quotation marks

Discussion

SQLite is the most deployed software in the world. It is probably safe to use their behavior. Or more stringently we might just preserve every input as is until it is necessary to convert (as you recommend).

And, of course, it might be nice to implement SQLite as a storage backend for TableTool. But that is an entirely separate discussion.