How do I keep Numbers from mangling numbers when importing a CSV?

Question

When I open a CSV file in Numbers, it "helpfully" converts fields it recognizes as numeric by stripping leading zeros, converting things it recognizes as dates, etc.

Take for example, you type a UPC code into a Numbers spreadsheet 005566778899, Numbers will automatically convert that to 5566778899. This is not what I would want...

However, the way I use Numbers, I open databases with thousands of UPC codes with preceding zeroes. Some of them also contain dates which Numbers will also reformat. Basically I do not want any of these features, I want my content to be left alone.

How can I get Numbers to leave my data intact when I open a CSV file?

Converting the fields to text after importing won't help since the data was already messed with...

Answer 1

If a field in a CSV file begins with an apostrophe ('), both Excel and Numbers will treat the field as text, and not render it with any numerical formatting.

Your CSV files appear to try to force text rendering through enclosing number fields in double quotes, but Numbers and Excel don't seem to take the hint that double quote numbers should be treated as strings. The trick seems to be to modify the CSV files so they use the "starts with a single apostrophe" trick rather than the "contained in double quotes" trick for numeric fields, but keep the double quotes for text fields involving punctuation (including commas, quotation marks, line breaks, etc).

To process your CSV files so they do this, you can create an Automator application.

In Automator, create a new Application.

It will have a single action: Run Shell Script (passing input as arguments). Here is the script:

for f in "$@"
do
    perl -pi -e "s/\"\"([0-9A-Za-z: \.\-+]+)\"/'\1/g" "$f"
done

Save the resulting application on your Desktop. Drop any CSV files you want to use in Numbers on the application icon, and they will be converted so Numbers should keep the numbers in the fields as literal numbers, and not format them or throw away information.

Back up your data before trying this; it is possible that a particularly oddly constructed string in a record field could throw off the results here.

Answer 2

Specifically, here's how to handle leading zeros. I think your question is more about cell formatting than autocorrect.

As was mentioned the autocorrect feature itself is a system item, and needs to be disabled from System Preferences.

Answer 3

Huzzah, thanks to this answer and others, there is now a method with the following benefits:

• Works in Excel as well as Numbers
• Can be automated.
• Is invisible in Numbers.
• Produces zero-impact CSV files from import to export.

The trick is to not prefix each field with an apostrophe nor wrap in double quotes, but to prefix with =" and suffix with ". On import Numbers treats the field contents as a string and on export it drops the prefix and suffix.

Here's a handy one liner that pre-processes a file called my.csv:

sed 's/^/="/;s/,/",="/g;s/$/"/' my.csv | sed 's/=""//g' > tmp.csv

The first sed puts =" at the start of each line, changes each comma to ",=", then ends each line with ". The second sed then strips out any empty fields, because Numbers chokes on them. Finally it writes a file called tmp.csv that can be doubled clicked or passed to open to import into Numbers.

In practice this will garble any fields that have commas in them. You might be better off only wrapping those fields that start with numbers, but then you need to be careful with the start and end of each line and your version of regular expressions. This will do on macOS:

sed -E 's/(^|,)([[:digit:]][^,]*)($|,)/\1="\2"\3/g'

That is, match the start of the line or a comma, a digit and then any number of characters that aren't commas, then the end of the line or another comma. Replace with the same beginning and end, but with the middle bit wrapped in =" and ".