Numbers: how to import csv data into specific sheets/ranges

A very pragmatic approach: just replace all comma's with tabs, e.g. using vi, save the result as a .txt file and drag it into Numbers. This will cause Numbers to correctly identify the columns.
Hope it helps anyone,

Robert.

rjgijsbertsen wrote:
A very pragmatic approach: just replace all comma's with tabs, e.g. using vi, save the result as a .txt file and drag it into Numbers. This will cause Numbers to correctly identify the columns.

Works only if the file uses the comma as separator. When the decimal delimiter is comma, the csv files are often using the semi-colon. In such case, it's the semi-colon which must be replaced.

Yvan KOENIG (VALLAURIS, France) jeudi 4 février 2010 12:20:39

Hi Yvan,

I'm having an awful lot of time importing a semicolon-delimited file into Numbers 09. I cannot find-replace the semicolon by a comma because there are a lot of numbers that have comma as thousands separators and dot as decimals separators.
As I now read your post: Where is "Import" in Numbers 09??

Thanks!
Julian

I use the word Import to make clear that the app is forced to translate the datas.
Just drag & drop the csv's icon onto Numbers 's one
or use the menu
File > Open

You may also select a xxx.csv's icon
press cmd + i and teach the beast that it must *_Open With_* Numbers.
Click the button named something like "apply to all" and bingo, double click a csv will open it in Numbers.

Yvan KOENIG (VALLAURIS, France) mardi 13 avril 2010 18:15:25

Thanks Yvan,

Maybe I understood you wrong..
But I quote you:

when you want to insert CSV datas in Numbers,
(1) if they use the comma on a system using the period as decimal separator, you may use import or drag & drop.
(2) if they use the comma on a system using the comma as decimal separator, use drag & drop but you will have to apply search/replace to replace the decimal period by the decimal comma.
(3) if they use the semi-colon on a system using the comma as decimal separator, use import.
(4) if they use the semi-colon on a system using the period as decimal separator, use drag & drop but you will have to apply search/replace to replace the decimal comma by the decimal period.

So I am in case 3. No matter the extension of the file, a semicolon-delimited file dropped onto the Numbers canvas or icon will import as a one column table (meaning the semicolons are not being recognized as column delimiters by Numbers) (Note: I have the english version of Numbers. I heard the Dutch version DOES recognize semicolons...)

Right now, the only workaround is:
1. From the source program: Export the columns that don't have numbers to one file (A) and the numbers to another (B)
2. Find-Replace the semicolons from A to commas and rename with extension .CSV
3. Rename B with extension .TXT
4. Import both separately and merge into one table

But as you can see, this is cumbersome....
Greetings.
Julian

hoolito wrote:
Thanks Yvan,

Maybe I understood you wrong..

Sure you did.

(Note: I have the english version of Numbers.

*There is no English version.* ! ! ! !

You are just using Numbers in English on a System running in English.

I heard the Dutch version DOES recognize semicolons...)

Same comment.
If you use Numbers in Dutch on a System running in Dutch, it will recognize semi-colons because the System will use the decimal comma.
If you use Numbers in Dutch on an System running in English, it will not recognize the semi-colons because the System will use the decimal period.

It seems that you didn't read carefully what I took time to write.

To be short :

If you receive CSV using semi-colons, set your System on a region using the decimal comma.
If you receive CSV using commas, set your System on a region using the decimal period.

*If you refuse to do that, you must use one of my scripts which replaces the embedded delimiters by TAB chars or use the awful process which you describe below.*

Right now, the only workaround is:
1. From the source program: Export the columns that don't have numbers to one file (A) and the numbers to another (B)
2. Find-Replace the semicolons from A to commas and rename with extension .CSV
3. Rename B with extension .TXT
4. Import both separately and merge into one table

You are wasting your time as well as mine.

The script able to do the trick is available since several days.
Do you know that I have other things to do than rephrasing the same responses because you didn't read carefully the first ones ?

Yvan KOENIG (VALLAURIS, France) jeudi 15 avril 2010 16:51:27