Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

How do I import csv into numbers?

How do I import csv into numbers?


I'm trying to import a text file that is comma delimited into the Numbers application. I expect that each comma in the file will trigger a new column. However, the rows (CR delimited) are pasted into a single column. Any advice on how to make Numbers parse by commas?


I want it to look like this:


1 2 3 4 5

A B C D E


With each character existing in a different column.


Instead what I get it this:


1,2,3,4,5

A,B,C,D,E


All records for a row are all pasted in one column


Any advice?

MacBook Pro, Mac OS X (10.6.7), iWork Numbers Application

Posted on May 7, 2011 11:37 AM

Reply
Question marked as Best reply

Posted on Jun 29, 2012 8:51 AM

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

58 replies

May 7, 2011 12:24 PM in response to garryfromversailles

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

Jun 28, 2012 12:51 AM in response to garryfromversailles

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

Jun 28, 2012 9:57 AM in response to rene beekman

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

Jun 29, 2012 7:21 AM in response to 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.

Jun 29, 2012 9:00 AM in response to Jerrold Green1

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)

Jun 29, 2012 9:20 AM in response to rene beekman

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

Jun 29, 2012 11:46 AM in response to rene beekman

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.

User uploaded file

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

Jun 29, 2012 2:34 PM in response to Barry

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 😉

Dec 4, 2012 1:07 PM in response to Matthieu2012

If Numbers needs the "csv" to be spearated by semicolons, not commas, upon import then it will export a "csv" the same way. Yes, it is your regional settings that determines this. There is nothing you can do in Numbers to change it. Without changing your regional settings, I'm not sure how you fix the "csv" file.


From what I see in the "US" settings, a csv file only has quotes around text or numbers that have a comma in them. Maybe that's what you are seeing?


What regional setting (country) are you using? I'll try it out and see if I can recommend something.

How do I import csv into numbers?

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple ID.