How do I import csv into numbers?

I'm trying to import a text file that is comma delimited into the Numbers application. I expect that each comma in the file will trigger a new column. However, the rows (CR delimited) are pasted into a single column. Any advice on how to make Numbers parse by commas?

I want it to look like this:

1 2 3 4 5

A B C D E

With each character existing in a different column.

Instead what I get it this:

1,2,3,4,5

A,B,C,D,E

All records for a row are all pasted in one column

Any advice?

MacBook Pro, Mac OS X (10.6.7), iWork Numbers Application

Posted on May 7, 2011 11:37 AM

Top-ranking reply

Jerrold Green1

Level 7

30,274 points

Posted on Jun 29, 2012 8:51 AM

Rene,

CSV is a lousy data format, across country boundaries especially. Rather than worry over what Apple has or hasn't done about that, you could just drop your data into a text editor and use Find and Replace to convert to Tab Separated Variables, a more universal and much better behaved standard.

Jerry

View in context

58 replies

May 21, 2014 7:54 AM in response to SGIII

When you say "none of the above worked" what were the results you got?

All the line pasted in the first cell.

It might br becaus I have a french canadian version of Numbers and keyboard. Who knows?

It is not a plug in.

The csv file si uploaded ont he site

SGIII

Level 8

37,562 points

May 21, 2014 8:39 AM in response to Daniel Derome

All the line pasted in the first cell.

Was there only one line? What did your csv data look like? I'm pretty sure there is an easy fix that doesn't involve using websites that may or may not be secure, and won't require going through an Excel file format.

It is not a plug in.

The site (quite properly) discloses that it uses third-party plug-ins that install "cookies" on your computer to track what you do and transmit data to Google/Twitter/Facebook servers. My understanding is that once these cookies are on your computer, some continue to track and report what you do even when you are on other sites.

May 21, 2014 1:37 PM in response to SGIII

«Was there only one line?»

Each line is paste in the first cell of the line.

«I'm pretty sure there is an easy fix»

It could be time consuming to find that easy fix.

I have tried 3 of the methods above, and none worked.

Thank you for the warning for the cookies.

I will delete them.

SGIII

Level 8

37,562 points

May 21, 2014 4:31 PM in response to Daniel Derome

It could be time consuming to find that easy fix.

I have tried 3 of the methods above, and none worked.

Sorry that's been your experience. If you wish to share the structure of your data my guess is there is indeed an easy fix that would not be that time-consuming. In any case, best of luck.

May 24, 2014 7:29 AM in response to SGIII

Well, I changed my mind and thought that I had deleted this post. But it was not deleted.

Though I deleted the dropbox files

Sorry

It is because there was some sensible data on those files that it would have been silly to publish.

Finaly, I manage to find a way to transform csv to Numbers.

I open the csv file with textEdit and replace all the comas with tabs,

copy all

and paste in Numbers.

And it works.

Sorry again and thank you for your help.

SGIII

Level 8

37,562 points

May 24, 2014 7:57 AM in response to Daniel Derome

Daniel,

Glad you have found something that works for you. Your approach, however, will not work if there are commas within fields. That is one of the reasons for the service (aside from the fact that clicking once to run a service is easier than messing around with TextEdit). The script will handle the commas-within-fields situation, and many others. But it won't work on phantom data.

SGIII

Level 8

37,562 points

Mar 29, 2015 7:13 AM in response to Fedux

Thanks for the positive feedback! Credit needs to go also to Nigel Garvey, who posted the crucial AppleScript handler on MacScripter (his is the best out there, followed closely by Hiroto's in this forum), and to Badunit, who demonstrated how to turn the script into an Automator Service.

Fedux

Level 1

5 points

Mar 29, 2015 7:56 AM in response to SGIII

I'm not sure how to find them... I try with "People" tab, on the top of the page, but I couldn't find them.

By the way, it's really amazing how well this forums are working after all these years. I've been a Mac user since 2007, so not so a long period, but here and in a lot of other forums I could help and be helped in a lot of circumstances. I believe internet will go back from social networks to forums again or, at least, the most polite part of it.

I hope Apple never close the "discussions".

Have a good day

NowWhat!

Level 1

11 points

May 24, 2015 9:15 PM in response to SGIII

Thank you sooo much. I've been reading this thread, doing settings, scripting and every other thing.....

Annnd all I had to do was re-type .csv to .tsv ...

and a batta-bing-batta-boom Numbers 2.3 opened up everything like little ducks in a row.

One star for helping doesn't do your suggestion right... should be..FIVE STARS \(*<>*)/

Thank you... m(_ _)m

SGIII

Level 8

37,562 points

Jun 19, 2017 8:16 AM in response to lmayala

Hi Imayala,

This is a really old thread. If you've got a question about how to do something with the current version of Numbers, could you consider starting a new thread? When doing that, it would be helpful to provide more details on what you are trying to do in Numbers.

Aug 7, 2017 10:43 AM in response to rene beekman

Thank you so much Rene! This was really helpful. It actually worked

I didn't even know that I could drop a file right onto a new open spreadsheet until I read your solution for getting a .csv file to open into multiple columns

May 22, 2014 7:51 AM in response to Daniel Derome

This is what I get:

Aug 16, 2012 9:59 AM in response to rene beekman

Works perfectly!

Oct 2, 2013 4:18 PM in response to Wayne Contello

Thank you, Thank you, Thank you!

Feb 21, 2014 8:47 AM in response to SGIII

Hi,

Thanks a lot for the hint. Indeed using AppleScript makes everything possible. Bought the books, but haven't started with it. Certainly a good opportunites.

Appreciated.

Alain

This thread has been closed by the system or the community team. You may vote for any posts you find helpful, or search the Community for additional answers.

How do I import csv into numbers?