iWork Numbers: How to Open Tab Delimited CSV Files

Contributed by tipmonkies

Tagged: Apple Mac

Google adsense and many other programs export data into tab delimited CSV files. Numbers will not open these files with the correct formatting. However, with some minor preparation, Numbers will open the files perfectly.

The problem is honestly not iWork’s Numbers program. The problem is that CSV files are not supposed to be tab delimited. As their name suggests, CSV files should contain Comma Seperated Values.

Although Excel does not care and has a wizard to walk the user through the import process, Numbers is a little more strict.

This is what happens if you try to load a tab delimited CSV file in Numbers:

Messy, huh?

Luckily, the fix is very easy. Tab delimited files should be in txt format. In a Finder window, click on your .csv file and rename it to a .txt file.

Now when you open the file in Numbers, you formatting will be correct.

30 Comments -

zach said on January 25, 2009

nice!
Anonymous said on February 10, 2009

Numbers doesn’t strictly adhere to the comma itself though. The Dutch version uses semicolons instead of commas as the comma is the decimal point in the Dutch languange.
unlucky said on February 26, 2009

I have no luck…
Luke said on June 28, 2009

I’ve got semi colon delimited data and this work around doesn’t work. Not happy with Apple about this one.
Anonymous said on August 30, 2009

Thanks so much for this information it’s been really helpful and is much appreciated.
Anonymous said on December 15, 2009

” In a Finder window, click on your .csv file and rename it to a .txt file.”

Sounds simple – but it does not work for me here.
I`m using mac snow Leopard, the newest iWorks…

Do have an idea why???
Anonymous said on December 15, 2009

the txt. tip does not work here…

I`m on snow Leopard and iWork 09

Do you have an idea? It is still”messy”
Todsaporn Wattanasupinyo said on April 23, 2010

Thank, this post help me.
denis said on May 18, 2010

for ; separeted CSV’s open CSV with textEdit, and replace all ; with , then just import it in numbers.
Richard said on June 7, 2010

Thank you so much for this information. I could have screamed because I couldn’t get the csv files to look correctly. What a nice and simple solution!
Vjayant said on June 11, 2010

csv stands for “character seperated values”. Strictly speaking one should be able to specify and use any character as a delimiter, it should not have to be commas.
kbm said on June 25, 2010

thank you!
Zaheadblader said on August 8, 2010

Doesnt work for me. OSX 10.6.4
Jules said on February 15, 2011

change the extension to .csv it opens in numbers ok then on snow leopard (iwork 09)
qo said on April 5, 2011

Yep, Jules is right. Files with a format:

item1,item2,item3

can be imported if their extension is .csv

If you’re trying to convert CSV which has a format of:

“item1″,”item2″,”item3″…

1. Make sure no commas are present in the actual data*
2. Search/replace (“) with () i.e. delete all double-quotes.
3. Make sure the following is checked (or you may be saving as file.csv.txt without knowing it):

Finder->File->Preferences->Advanced->Show all filename extensions

4. Save as file.csv
5. In Numbers File->Open, browse to the file and open it.

*If the format IS “item1″,”item2″,”item3″ and there ARE commas in the data, you can remove them with multiple passes of search/replace, e.g.:

1. Search/replace (“,”) with (:::)
2. Search/replace (,) with ()
3. Search/replace (:::) with (,)
4. Search/replace (“) with () for any leading/trailing double-quotes.
Vincenzo Pignatelli said on June 6, 2011

thank you, geat!
Ch0c0 said on July 1, 2011

Well, this “simple” search-and-replace solutions is not at all that simple in the “real” working world environment – I have to export a whole bunch of data a good portion of which is free-form text entered by the user into a web form so it’s full of all kinds of punctuation. This exported file get’s downloaded by a client. Do you really think I can tell them to search-and-replace the data file in order to get their data out of it?

Would really appreciate if someone knows and is willing/able to share a more business-feasible solution to this.

Thanks.
qo said on July 1, 2011

Sure, take 5 minutes to write a perl script to perform the search/replace.
qo said on July 1, 2011

Sure, take 5 minutes to write a perl script to perform the search/replace.
qoqo said on July 1, 2011

I’m sure someone could write this more succinctly, but it gets the job done. And, if you’re in a “real working” world environment, you’ll most likely have a linux box (or Mac, which comes with perl). Or you could (ugh) download perl and install it if you’re forced to use that other operating system…

[qo@qo-linux tests]$ cat csv.pl
#!/usr/bin/perl

if(@ARGV != 2) {
   usage();
}
($inFile,$outFile) = ($ARGV[0],$ARGV[1]);
open(IN,”;
close(IN);
foreach (@csv) {
   chomp;
   s/”,”/:::/g;
   s/,//g;
   s/:::/,/g;
   s/”//g;
}
open(OUT,”>$outFile”) || die “Cannot open outFile ($outFile)n”;
foreach $line (@csv) {
   print OUT “$linen”;
}
close(OUT);

sub usage {
   print “Usage:n”;
   print “./csv.pl inFile.csv outFile.csvn”;
}
[qo@qo-linux tests]$
[qo@qo-linux tests]$
[qo@qo-linux tests]$ cat csv.pl
#!/usr/bin/perl

if(@ARGV != 2) {
   usage();
}
($inFile,$outFile) = ($ARGV[0],$ARGV[1]);
open(IN,”;
close(IN);
foreach (@csv) {
   chomp;
   s/”,”/:::/g;
   s/,//g;
   s/:::/,/g;
   s/”//g;
}
open(OUT,”>$outFile”) || die “Cannot open outFile ($outFile)n”;
foreach $line (@csv) {
   print OUT “$linen”;
}
close(OUT);

sub usage {
   print “Usage:n”;
   print “./csv.pl inFile.csv outFile.csvn”;
}
[qo@qo-linux tests]$ ./csv.pl in.csv out.csv
[qo@qo-linux tests]$ cat in.csv
“1111″,”2222″,”3333″
“4444″,”5555″,”6666″
[qo@qo-linux tests]$ cat out.csv
1111,2222,3333
4444,5555,6666
[qo@qo-linux tests]$
qoqo said on July 1, 2011

Opps, sorry for cat’ing the script twice :-(
qo said on July 1, 2011

Or, even better, write an Automator workflow for it (Automator comes with the Mac). If you buy BBEdit, it comes with many Automator plugins, including a Search and Replace plugin. Then it’s just a matter of telling Automator to open the file in BBEdit, do the several search/replace operations, and save the file.
qo said on July 7, 2011

The original message was received at 2011-07-07 10:58:14 -0700
from postoffice.cisco.com [10.0.0.1]

—– The following addresses had permanent fatal errors —–

—–Transcript of session follows —–
… while talking to postoffice.cisco.com.:
Ian said on October 19, 2011

Brilliant advice – been trying for over two hours to resolve bring a CSV file in to Numbers from Google Keyword Tool, this works a treat! Nice one :-)
Bedrijf op Facebook said on October 24, 2011

Wow! Worked perfectly. It’s important that you change it to TXT –>before
Tom Pollard said on November 27, 2011

Thanks! The .txt tip worked perfectly for me. It allowed me to import tab-delimited .csv files (exported from PHPlist) into Numbers ‘09 on my MacOSX 10.6.8 Macbook.
Sean said on December 6, 2011

Awesome tip! I thought I was going to have to dig out a copy of Excel but this little bit of info saved me easily an hour, maybe more.
Phil said on December 29, 2011

Thanks very much for this useful tip. I’m just getting into using Numbers instead of Excel as overall I prefer it. I’m using Snow Leopard with iWorks 9 and had no problem with it. Maybe this is something that could be added to the preferences section of Numbers – The option of Commas, Tabs or Semi-Colons separation.
RCBoyd said on January 6, 2012

It is not the most convenient but it works. I am using if for bank info so luckily I do not have to do it so often. Thanks for the information. Much appreciated.
MS said on January 29, 2012

The csv-to-txt trick didn’t work for me.

Then I just dragged and dropped the original csv file from my desktop into a table in Numbers …. it’s magic.

RSS feed for comments on this post. TrackBack URL

iWork Numbers: How to Open Tab Delimited CSV Files

Google adsense and many other programs export data into tab delimited CSV files. Numbers will not open these files with the correct formatting. However, with some minor preparation, Numbers will open the files perfectly.

You may also like -

30 Comments -

Leave a comment -

RECENT POPULAR RECIPES

TECH-RECIPES BLOGS