Dropping csv file into a cell?

I can drop a csv file onto the Numbers Icon (in the dock) and Number will open the file in a new window. I do not think I was ever able to drop a csv file onto a cell.

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?

Yep this is a big step back in Numbers :-(

You could try CSVKit instead, but it requires some handiness with Terminal and it's still pretty bleeding edge...

Thanks rjgijsbertsen!

I'll try this solution, but I still hope that Apple will re introduce the original feature on Numbers 🙂

This is the only reason I still have the old Numbers 2.3 on my Mac.

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 😟

We discovered, that Numbers 3 are using semicolon instead of comma. So the solution is: Open CSV file in Textedit, find&change commas to semicolon. Open directly with Numbers, no need of droping on the table etc. Hope it help you guys 🙂

Hi pavel186,

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.

Thanks 🙂

Hi Cuky,

well nothing is perfect :-)

I'm glad it helped a little bit 😉

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.

SG

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.

SG

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.

https://dl.dropboxusercontent.com/u/54961502/For%20Apple%20Forums/CSV%20to%20Tab s%20on%20Clipboard.workflow.zip

Thanks for that, Badunit. Interesting what Automator can do to make life more convenient. I suppose one could do something that would allow dropping a CSV file onto an app as well (droplet?) but that would only make sense for processing of multiple files at once. Your right-click and contextual menu pick is more convenient.

Since you are not (yet) an "identified developer" I was blocked from opening the workflow until I went to System Preferences > Security & Privacy and clicked 'Open Anyway'.

I see some of the key modifications to make it a service include choosing receives 'files or folders' in 'Finder.app' and wrapping the script an on run() handler with the file parameter. Nice.

Would love it to be able to recognize the separator (, ; | etc) automatically but that's beyond my abilities. It's working well here as is.

SG