Mac – Exporting CSV with Numbers with different languages

Numbers ’09 supports Excel native format (.xls) but it can’t convert from an Excel XML Spreadsheet. In fact, if you rename it as “xls” and try to open it (trying to fool Numbers), you’ll probably see all the XML but not the actual spreadsheet. Which is more scary than Psychosis II back in the 80s.

Your only bet is some CVS or TEXT format, which excel can even export with Macintosh format for line endings and carrier return (if I’m not mistaken).

The big drawback is that all format and binary stuff is automagically lost in translation (certain pun intended). ;)

You’ll get the data, but not the format.

Another alternative is trying to automate the Excel XML to Excel Native format, even if your webapp outputs a XML, you could try to “transform them” before anything else.

For that, I haven’t investigated but you can start with a google query like this. There seem to be some Windows solutions, maybe something you can use/try.

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.