I have the same problem with the new numbers. In the old version it was possible to drag and drop a csv file in a cell of a table… this way the data were adapted on the following cells starting from the one you've dropped the csv file:
This was a very useful function to me... I can't believe it has been removed in the new Numbers!
If I try to open a csv file by dropping it onto the number icon on the dock, it will be opened with all the data in the same cell:
There is any other way to open properly a csv file in the new Numbers?
Number '09 let you drop a CSV file onto a cell and it would expand into columns and rows. It was an easy way to import new data into a file. Numbers 3.0 does not have the capability. But Numbers 3.0 will open a CSV file and it will expand into columns and rows if the file has a CSV extension. If the file has a TXT extension, it will open in one column instead.
The problem I have is that 3.0 and '09 do not treat the data the same way. As an example, in '09 the characters "7.10" it will be imported and formatted as a number with two decimal places. It will still look like 7.10. In 3.0 it will be formatted as a number and the trailing zero will be lost. It becomes 7.1. Kind of a problem for me because in my data these are task numbers as in Task 7, subtask 10.
I don't think it has to do with the extension. I have a comma-separated file with .csv extension that *used* to open nicely in Numbers '09, splitting data into columns. But in Numbers 3.0, all data is imported into the first column as in Cuky's example, whether I drag the CSV into a cell or choose File->Open in Numbers.
I don't know why, but if I try to open a csv file (with a .csv extension) Numbers 3.0 will not open it correctly, as I've reported on the screenshot above.
If I try to open a .csv document with both numbers (2.3 or 3.0) using the "open with" function, all the data will be in a single column. Only if I drag&drop a file directly on a cell on Numbers 2.3 it will be expanded in different columns
Yes, this makes .csv files readable with Numbers!
The only problem is that sometimes I have contents that use commas as part of the data (for example a date "Jun 21, 2013" or some text fields). With the find&change function all commas are turned into semicolon.
This requires a second check, but it's ok.
CSV is a terrible format. Tab separated would be better.
If you need the csv file to use semicolons as the separator (not comma), your localization must be using commas as the decimal separator. In those localizations, "CSV" files must use semicolons. In localizations with a point decimal separator, a semicolon separated file will not import correctly. It will import into a single column.
Of course, this was not the original topic of this thread. The original topic was the inability to drag & drop a CSV file onto a Numbers 3 cell like you could in Numbers 09.
Hello fellow sufferers of CSV headaches from commas within fields and ; instead of , etc.. They've been around as long as I can remember.
If you don't want to use Excel (which is good at this sort of thing), this script should help. (Dropbox download, AppleScript file). The effect is similar to Excel's Text to Columns but the usage is slightly different.
If you end up with text all in one column as Cuky describes, you simply select the body cells in that column containing the text, run the script, click on a cell (the first cell in the body of the next column to the right often works best) and paste.
The results here look like this:
I deliberately made the unexpanded column untidy and threw some difficult things at it, and it did a pretty good job in testing here. Return or shift-return within a field, even if quoted, will cause Numbers to move to a new line, but that's a Numbers thing that can't be helped. Otherwise, it handles the usual csv conventions better than expected.
If your separator is ; instead of , there is a place you can change that it the script.
If you frequently import data, this is easily placed in the menu, either as an Automator Service or under Scripts.
And it could be modified to read a csv file rather than grabbing values already in a Numbers column, making it almost as convenient as the old drag and drop. It could ask for the csv file and put the results on the clipboard for pasting into a table.
If there is interest in that let me know. And suggestions/feedback on this one welcome.
Here is another script, CSV File Paste, which prompts to open a file containing csv data then places the file's contents on the clipboard in a tab-separated form that can be pasted into a Numbers table. Run the script, navigate to the file, open. Then click once on a cell in an existing Numbers table and command-v to paste.
That's it. Almost as convenient as dragging and dropping, and in many cases it can save a trip to Excel (or wherever).
An extra trick: If the tabbed data ends up pasted into one cell instead of filling out a Numbers table, then that's probably because of a double-click in the cell before pasting rather than a single click. If that happens, instead of starting all over again, you can command-a to select all the text in the cell, command-c to copy all the text to the system clipboard, click another cell once, and paste. (Or click back on the original cell and paste.)
Or if you have trouble with that you can click the original cell (the supercell with all that data) once, run the Text to Columns script from the previous post, and command-v to paste.
All of this takes advantage of Numbers 3's ability to parse tab-separated data pasted from the clipboard onto a single-clicked cell.
The CSV File Paste script solves the problem I was having with index "numbers" (such as 1.10) losing their trailing zeros (i.e, importing as 1.1) in Numbers 3. To get around this problem I have been importing into Numbers '09 then copy/pasting to Numbers 3. I can stop doing that now.
I used Automator to turn the script into a service that accepts a file from Finder. This way I can right-click on a CSV file and run it from the contextual menu.