7 Replies Latest reply: Apr 14, 2014 3:49 AM by xolytem
Rodrigofromsp Level 1 Level 1 (0 points)

Im open a file in .csv with "," parameter in numbers but it open in one column. If you make a sheet in numbers and export to .csv format and open in a text app, it use ";". How can i make Numbers open a .csv file with "," parameter in multiple columns?


Numbers, Mac OS X (10.7.4)
  • 1. Re: Import .CSV file in Numbers in multiple columns
    Barry Level 7 Level 7 (29,180 points)

    Hi Rodrigo,

     

    See Yvan Koenig's post in Getting CSV into Seperate columns.

     

    Regards,

    Barry

  • 2. Re: Import .CSV file in Numbers in multiple columns
    Barry Level 7 Level 7 (29,180 points)

    Rodrigo,

     

    Another, safer way to do it is to leave the CSV file as is, and switch the decimal separator in your System settings, then reset the decimal separator after the import:

     

    1. Quit Numbers
    2. Temporarily set the system decimal separator to period (Using System Prferences).
    3. Start Numbers
    4. Import (File > Open) your CSV file (without changing its use of the comma as values delimiter)
    5. Save the resulting Numbers document
    6. Quit Numbers
    7. Reset your default system decimal separator to comma.

     

    Regards,

    Barry

  • 3. Re: Import .CSV file in Numbers in multiple columns
    Ruud Weiss1 Level 1 Level 1 (5 points)

    Just a note to Barry that the solution through system settings works fine.

     

    THANKS

     

    Ruud

  • 4. Re: Import .CSV file in Numbers in multiple columns
    pplanch Level 1 Level 1 (20 points)

    Interesting discussion. I am having a related problem ... when exporting data from my bank's netbanking site to csv, the file results in semi-colon delimited data (probably because that is the most popular setting in MS Excel).

     

    I am using settings with decimal point as ".", so in order to be able to import the csv files I first open them in TextEditor and do a replace-all to convert the semi-colon's to comma's. However, I first need to make sure the numbers in the export (which contain comma's as decimal point due to the belgian national settings of the bank's web site) are converted to the correct (in my case) decimal notation.

     

    In Excel, there is a "preferences" setting that allows users to define what separator is preferred when importing csv's. Is there a way to tell Numbers (if necessary through Terminal) what a csv is expected to look like ?

  • 5. Re: Import .CSV file in Numbers in multiple columns
    Barry Level 7 Level 7 (29,180 points)

    "In Excel, there is a "preferences" setting that allows users to define what separator is preferred when importing csv's. Is there a way to tell Numbers (if necessary through Terminal) what a csv is expected to look like ?"

     

    Numbers uses the System Preferences setting to determine the expected csv item separator.

     

    Numbers 'expects' a csv file to follow the standards of the region. If your region is set (in System Preferences) to use the full stop (period) as the decimal separator, Numbers expects the comma as the item separator; if the region is set to use the comma as the decimal separator, Numbers expects the semi colon as the item separator.

     

    Regards,

    Barry

     

    PS: Requests for a preference setting similar to that offered in Excel (and the various 'open' Office versions) may be made through the Provide Numbers Feedback channel, found in the Application menu ("Numbers" menu for Numbers) of current Apple applications.

    B

  • 6. Re: Import .CSV file in Numbers in multiple columns
    FelipeGlauber Level 1 Level 1 (0 points)

    Wow, BARRY!

     

    Thank you so much, well done..

  • 7. Re: Import .CSV file in Numbers in multiple columns
    xolytem Level 1 Level 1 (0 points)

    Just modify numeric separator in osx regional preferences to something differrent than in your CSV file.

     

     

    For instance, if your CSV file uses "," as separator, make sure your decimal separator in osx regional preferences is set to "."

     

    If these 2 separators are equals, Numbers will not split into columns. You can just modify regional settings prior to import your csv file into Numbers, and then revert to original value after import.

     

    To import the csv file, just open it through the file menu, or drop it in Numbers app or dock icon as any other Numbers file.