How do I import csv into numbers?

Rene,

CSV is a lousy data format, across country boundaries especially. Rather than worry over what Apple has or hasn't done about that, you could just drop your data into a text editor and use Find and Replace to convert to Tab Separated Variables, a more universal and much better behaved standard.

Jerry

I had this same problem and was climbing the walls with Numbers being unable to correctly open a CVS, but it turns out there is a much better way to do this;

1. Open a new spreadsheet in Numbers

2. Drag & drop the CVS you want to import onto the spreadsheet

Numbers will now correctly import the CVS, generate additional columns, etc.

See also this help document http://docs.info.apple.com/article.html?path=Numbers/2.0/en/lso101b05d9.html

If the file has a csv extension, not txt, right click and open it with Numbers or drop it on the Numbers icon in the dock. If it has a txt extension, change it to csv.

This assumes your decimal is a period, not a comma. If not, csv files must use a semicolon instead of a comma for the separator.

Hi rene,

"Hope this answers your questions, Barry."

Well, it did, in that it sent me back to re-read the thread. I'd missed the detail—drop on the spreadsheet, vs drop on the Numbers icon. 😊 (That's a 'blush' smiley, btw.)

Checking out your suggestion, though, indicates that, like Badunit's, it works only with some regional settings.

Here are the results I got with a sample csv file in two versions.

Top: The csv file, with semi colon separators, was dropped on cell B2 of a table in an open Numbers document.

Middle: The csv file, with semi colon separators, was dropped on an unoccupied space on Sheet 1 of the same Numbers document.

Bottom: The csv file, with comma separators, was dropped on an unoccupied space on Sheet 1 of the same Numbers document.

Results from dropping the comma separated csv file on a table were essentially the same as the bottom example.

From these results it appears "2. Drag & drop the CVS you want to import onto the spreadsheet" is, like Badunit's suggestion, a "much better" method only under some regional settings.

Good to know it works in those settings, though.

Regards,

Barry

Hi Alain,

CSV import is already easily done with Numbers, using an AppleScript. See this thread , where you can download the script and/or Automator Service that incorporates the script so it shows up in your menu. If you need to use semcolon as a separator, just substitute {separator: ";"} for {} on the line indicated.

If you need OFX or QFX let me know and I'll post script.

SG

Jonathan,

If you're having trouble with the delimiter, then a good solution for you may be this CSV to Tabs on Clipboard Automator Service (Dropbox download).

To install just doubleclick the .workflow package. (You may need to click 'Download Anyway' in System Preferences > Privacy & Security).

To use, select the .csv file in Finder, right-click, and choose CSV to Tabs on Clipboard in the Services menu. Specify the delimiter when prompted. Then when you see the notification that the data is ready, click once in a Numbers cell and command-v to paste.

Thanks to Badunit for showing how to package this script as a Service, and to Hiroto for explanations about character-separated-values. If this one doesn't do a good job on your data, then try Hiroto's script here. It can even guess what your delimiter is.

SG

"Open the file and replace the existing delimiter with a tab, then select all, copy now paste into a table in numbers"

Not my solution, but found in Apple discussions: https://discussions.apple.com/thread/7487616?language=en

The release notes to Numbers 3.2 (just out yesterday) describe improvements to CSV import:

• Drag and drop a CSV file directly into a sheet
• Automatically update an existing table by dragging in a CSV file

Haven't had a chance to test the improvements. But between that (and, if necessary, easy-to-use AppleScripts) Numbers users need not fear they cannot easily get CSV data into their documents.

SG

Your avatar let me think that yo are in France.

So I will repeat one more time that Numbers read Comma Separated Values files if:

the file name ends with .csv and if the values are separated by :

commas if the decimal separator is the period

semi-colons if the decimal separator is the comma.

If your system setting is with decimal comma, the described behavior is normal.

Set temporarily your system to the region/country USA

open your file with Numbers and save it.

Reset your original system setting and open the newly created Numbers document.

It's really boring to see that some users are unable to apply the rules of these forums :

To avoid repeating questions that may have already been asked,

check to see if there are questions similar to yours.

Yvan KOENIG (VALLAURIS, France) 7 mai 2011 21:23:37

rene beekman wrote:

...there is a much better way to do this;

1. Open a new spreadsheet in Numbers

2. Drag & drop the CVS you want to import onto the spreadsheet

Numbers will now correctly import the CVS, generate additional columns, etc.

I'm puzzled, rene. How is this "a much better way" than the method described in Badunit's post?

Badunit wrote:

"If the file has a csv extension, not txt, right click and open it with Numbers or drop it on the Numbers icon in the dock. If it has a txt extension, change it to csv."

Regards,

Barry

Barry, in short: Badunits solution may work if your computer's region settings is north america and your csv uses commas to separate values and a period as decimal separators.

Unfortunately, that is only true for part of the world.

And, to make things more complicated, some of us have European region settings (i.e. comma's as decimal separators) but use US-generated csv (periods as decimal separators).

I have no idea what Apple was thinking (if anything at all) when they left out the import options interface for csv's, but the result is that if I follow Badunit's advise, every row in the csv ends up stuffed in a single cell, the entire file is stuffed in the first column. At least this is true for the csvs that I have tried it with, but they do not contain floatingpoint numbers.

Dropping the file onto the Numbers icon in the doc invokes the file open command (and thus results in the same problem).

Creating a new spreadsheet and dropping the file onto the spreadsheet, opens the file correctly.

As far as I'm concerned, given the above situation, the file open command in Numbers 2.x does not work for csvs.

Hope this answers your questions, Barry.

Jeremy,

I don't "worry" about Apply, I don't have the self-deception to believe that it would make any difference.

As for the text-edtor > search / replace option: too much work, too many cvs files a day and they're too freaking large. Why would I want to do that when all I have to do is drop them onto an already open spreadsheet to have them open properly???

(that's a rethorical question - just to ward off the flames)

rene,

Thanks for the feedback, I'll adjust the sensitivity of my worry detector. I got what I thought was a clear alarm, but it must have been a false indication - Type II error.

I think you may have missed Barry's point. He commented your your virtually verbatim quote of Badunit's advice as though you hadn't seen it. You still don't appear to have seen it.

Jerry

Barry, I'm glad you seem to do much better at reading than Jerrold is 😉

Yes, it does make a difference whether you drop a file on an application icon or on an open document window (roughly the equivalent between open and import).

It, however, also helps to read a thread in chronological order, as you'll see below.

About the "some regional settings" thing: I believe it roughly translaters to this:

Opening the file either through file open by right-clicking or by dropping it onto the application icon (or by simply using the file > open menu from within the application), probably works fine if:
1. the csv uses comma's to separate values, and points as decimal separators

2. your regional settings are the equivalent of US/North American (that is: use a point as a decimal separator)

However, if your regional settings are anything other than North American, or they do not comply with the US system of using points for decimal separators, and the csv you're opening does not comply with that format, you are probably better off using Yvan Koenig's or my solution.

Now, accuse me of whatever you want for thinking that a drag-and-drop soltution that merely changes the destination of the drop is "better" than a solution that involves fiddling with system settings. I'm fine with whatever side that coin drops (but this is where the chronological reading comes in).

I do, however, take offense with the "some regions" remark - from where I'm sitting, "some regions" would include the just about entire continent of Europe and then some.

And to be completely exhaustive: editing the csv itslef (as has been suggested multiple times here) is ok, as long as you only have the occassional file to open. For anything else, you might want to use a more serious work-around 😉

This is perfect to open the CSV file in Numbers.

However, if I export the nUmbers document in CSV, it is rubbish in text Edit and it exports ; instead of ,

And the strings are not between ““....

Do you have a solution to correct this?

PS : I am in Europe, so it is probably the regional settings...