Skip navigation

How do I import csv into numbers?

39187 Views 33 Replies Latest reply: Apr 14, 2014 1:24 PM by Jonathan Pool RSS
1 2 3 Previous Next
garryfromversailles Calculating status...
Currently Being Moderated
May 7, 2011 11:40 AM

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
  • Badunit Level 6 Level 6 (10,760 points)
    Currently Being Moderated
    May 7, 2011 12:22 PM (in response to garryfromversailles)

    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.

  • Level 8 Level 8 (41,760 points)
    Currently Being Moderated
    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

  • rene beekman Level 2 Level 2 (280 points)
    Currently Being Moderated
    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

  • Barry Level 7 Level 7 (29,095 points)
    Currently Being Moderated
    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

  • rene beekman Level 2 Level 2 (280 points)
    Currently Being Moderated
    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.

  • Jerrold Green1 Level 7 Level 7 (28,135 points)
    Currently Being Moderated
    Jun 29, 2012 8:51 AM (in response to rene beekman)

    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

  • rene beekman Level 2 Level 2 (280 points)
    Currently Being Moderated
    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)

  • Jerrold Green1 Level 7 Level 7 (28,135 points)
    Currently Being Moderated
    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

  • Barry Level 7 Level 7 (29,095 points)
    Currently Being Moderated
    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.

    Picture 1.png

    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

  • rene beekman Level 2 Level 2 (280 points)
    Currently Being Moderated
    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

  • ZGoncalves Calculating status...
    Currently Being Moderated
    Aug 16, 2012 9:59 AM (in response to rene beekman)

    Works perfectly!

  • Matthieu2012 Calculating status...
    Currently Being Moderated
    Dec 4, 2012 3:15 AM (in response to rene beekman)

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

  • Badunit Level 6 Level 6 (10,760 points)
    Currently Being Moderated
    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.

  • Nik DK Calculating status...
    Currently Being Moderated
    Sep 27, 2013 1:49 AM (in response to Badunit)

    These posts are almost a year old, but I still feel like adding a few notes.

     

    I'm in Denmark, so a standard CSV-file uses semicolon as field seperator. This is basically because our floating point seperator is a comma, not a dot (we write 123,45 - not 123.45).

     

    If I drag a comma-CSV file onto the Numbers icon - or just open it from the application - it fails. Everything is in the first column. However - if I drag the file onto an open spreadsheet, it works.

     

    Editing a CSV-file in a text editor is a bad idea however. Doing a search and replace might break the file - or the content. You need some regular expression to only match the field seperators, not the same characters which might exists inside quotes (the string delimiter). And string delimiters are only needed if the string contains special characters (line break, field or string delimiter)

     

    Anyway - try dragging the file onto a new, open spreadsheet. Did the trick for me without heavy search/replace operations.

1 2 3 Previous Next

Actions

More Like This

  • Retrieving data ...

Bookmarked By (0)

Legend

  • This solved my question - 10 points
  • This helped me - 5 points
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the Apple Support Communities Terms of Use.