9

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...

  • What do you mean "the data was already messed with"? Do you mean converting to text after the file is open? – Daniel Mar 21 '12 at 12:10
  • Upon opening the file, both Excel and Numbers will change around all your data. So whatever it is I have to do, I have to do it before I open the file. – henryaaron Mar 21 '12 at 12:13
  • 7 years later this still seems to be a gaping hole. It's problematic that a round trip from csv -> Numbers -> csv modifies cells. The leading zeros is a good example. Another one is percentages (eg. 20% becomes 0.2). Another one is scientific notation (eg. 1E-2 becomes 0.01). Wrapping in double quotes doesn't help, and prefixing with an apostrophe is interpreted literally. I think the best option might be to prefix everything with an apostrophe prior to import and strip all initial apostrophes out after export... – Heath Raftery Mar 10 at 11:59
7

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.

enter image description here

  • Making this work looks like it needs some interaction. This discussion would better take place in chat – Daniel Mar 21 '12 at 22:26
0

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.

  • Converting the cells to this format won't help since the data was already messed with... – henryaaron Mar 21 '12 at 3:32
  • 1
    I would have expected the zeros to come back if you format the cells - if Numbers does destructively remove those zeros on import that would truly suck. Maybe you're going to have to format cells before import. – Adam Eberbach Mar 21 '12 at 3:34
  • It most definitely does. – henryaaron Mar 21 '12 at 3:44
0

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 ".

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .