Import .CSV file in Numbers in multiple columns

Hi Rodrigo,

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

Regards,

Barry

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

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

THANKS

Ruud

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 ?

"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

Wow, BARRY!

Thank you so much, well done..

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.

Thanks Barry. I wrote my localization feedback to apple in hopes they'll change it in Yosemite or a Numbers update.

Is there a list of regions with their preference settings or do I have to check until I find one?

Q 2. See the PNG. I tried Libya but they have the same "." as US. My CSV [exported fr Constant Contact] when opened with Numbers appear to have separation by space or tab. Numbers separator shown for the US or Lybia is a ""."",

I tried this solution, now for the weird thing:

When opening the file with numbers, in the preview I see the columns separated.

As soon as I open the file though, the content is all back in one column again.

Wow...what a pain in the ***. First, the "solution" does not work for me. My geo-settings are already set to the period and comma. But, when I open a cvs file, it does not separate the data into columns. Excel works perfectly. Just confirms once again that, despite my desire to move away from Office, Numbers is not ready for real work.

InternetDR wrote:

But, when I open a cvs file, it does not separate the data into columns. Excel works perfectly.

Excel is a champ at dealing with csv data. But don't give up on Numbers before trying this Text to Columns Automator Service (Dropbox download).

To install (one-time) simply double-click the download .workflow package and, if necessary, click 'Open Anyway' in System Preferences > Security & Privacy.

To use:

1. Select the cells in the column containing the data that you want to separate into columns.
2. Choose 'Text to Columns' from the Numbers > Services menu.
3. Answer the prompts about separators.
4. Click once in a destination cell (I usually pick a cell in the adjacent column)
5. Command-v to paste.

This provides similar functionality to the Text to Columns wizard in Excel.

SG

Thanks for the response, SGIII. It just seems like every time I want to convert from Excel to Numbers, I run int fundamental problems. Another example revolves around printing. First, you cannot select the print area. At least I have not been able to figure out how to do it. For many of the spreadsheets I use, I keep a running tally of data. Example, every week, I enter sales by client into a spreadsheet and want to print just one weeks worth of data. In Excel, I highlight the cells I want to print, the header and left columns that contain the static data are already frozen and I am able to select to print the selection only. I have not found a way to do this in Numbers. Last, when I copy a block of cells from Excel into Mail, the numbers align properly on the right side. In Numbers, while the cell formatting is generally maintained better than Excel (borders, text formatting, etc), the numbers are left justified. WTH!! Makes no sense to me.

Also, Excel has been widely condemned as being blotted. But, when I look at Activity monitor, Numbers and Pages take up more memory than Excel and Word. So, fewer features and more memory.

Maybe someday Numbers will be the champ. But, not at this moment in time for me. All of these small, nagging issues cause me to pack up my bags with Numbers and move back in with Excel. I don't want to...I have to :-)

I you are looking for a specific solution to the specific problem posed in this thread, then you might at least try what's suggested.

If you start new topics on the other (unrelated) problems you mention, then you may find that there are easy workarounds for many.

SG