Take the 2-minute tour ×
Ask Different is a question and answer site for power users of Apple hardware and software. It's 100% free, no registration required.

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

share|improve this question
    
What do you mean "the data was already messed with"? Do you mean converting to text after the file is open? –  Daniel Lawson 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

2 Answers 2

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

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

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.

share|improve this answer
    
Converting the cells to this format won't help since the data was already messed with... –  henryaaron Mar 21 '12 at 3:32
    
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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.