iWork Numbers: How to Open Tab Delimited CSV Files
Posted by tipmonkies in 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.
The Conversation
Follow the reactions below and share your own thoughts.
January 25, 2009 at 7:28 am, zach said:
nice!
February 10, 2009 at 9:36 am, Anonymous said:
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.
February 26, 2009 at 5:53 pm, unlucky said:
I have no luck…
June 28, 2009 at 10:02 am, Luke said:
I’ve got semi colon delimited data and this work around doesn’t work. Not happy with Apple about this one.
August 30, 2009 at 10:23 am, Anonymous said:
Thanks so much for this information it’s been really helpful and is much appreciated.
December 15, 2009 at 3:10 pm, Anonymous said:
” 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???
December 15, 2009 at 3:12 pm, Anonymous said:
the txt. tip does not work here…
I`m on snow Leopard and iWork 09
Do you have an idea? It is still”messy”
April 23, 2010 at 9:45 am, Todsaporn Wattanasupinyo said:
Thank, this post help me.
May 18, 2010 at 8:55 am, denis said:
for ; separeted CSV’s open CSV with textEdit, and replace all ; with , then just import it in numbers.
July 13, 2012 at 7:45 pm, Amanda said:
> Thank you so much! This is the only tip I found that worked.
June 07, 2010 at 12:28 pm, Richard said:
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!
May 19, 2012 at 12:55 pm, Peggy Li said:
> Is there any way to import a plain table which was made with any text editor in Numbers spreadsheet without messing it up
June 11, 2010 at 8:08 am, Vjayant said:
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.
June 25, 2010 at 5:42 pm, kbm said:
thank you!
August 08, 2010 at 9:39 am, Zaheadblader said:
Doesnt work for me. OSX 10.6.4
February 15, 2011 at 9:54 pm, Jules said:
change the extension to .csv it opens in numbers ok then on snow leopard (iwork 09)
April 05, 2011 at 8:02 pm, qo said:
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.
June 06, 2011 at 8:17 am, Vincenzo Pignatelli said:
thank you, geat!
July 01, 2011 at 6:26 am, Ch0c0 said:
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.
July 01, 2011 at 1:33 pm, qo said:
Sure, take 5 minutes to write a perl script to perform the search/replace.
July 01, 2011 at 1:33 pm, qo said:
Sure, take 5 minutes to write a perl script to perform the search/replace.
July 01, 2011 at 5:43 pm, qoqo said:
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]$
July 01, 2011 at 5:44 pm, qoqo said:
Opps, sorry for cat’ing the script twice
July 07, 2011 at 6:01 pm, qo said:
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.:
December 06, 2012 at 9:30 pm, Ramon said:
So you can import perfectly into Numbers as you say. But exporting is a chore. I guess I’ll have to stick with Excel for that since it does export tab delimited text. I cannot believe Apple is still horsing around with compatibility issues after 40 years.
July 01, 2011 at 5:55 pm, qo said:
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.
October 19, 2011 at 4:44 pm, Ian said:
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
October 24, 2011 at 11:40 am, Bedrijf op Facebook said:
Wow! Worked perfectly. It’s important that you change it to TXT –>before
November 27, 2011 at 11:35 am, Tom Pollard said:
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.
December 06, 2011 at 10:04 am, Sean said:
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.
December 29, 2011 at 5:20 am, Phil said:
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.
January 06, 2012 at 7:01 am, RCBoyd said:
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.
January 29, 2012 at 2:25 pm, MS said:
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.
March 10, 2012 at 11:16 am, Louis said:
wow, incredible how complicated this is, i just have a simple CSV file (with commas) and it can’t open it?? its pretty rare i have to do this but rebooting in windows in 3, 2, 1
April 25, 2012 at 3:53 am, vib said:
thank you :p
May 06, 2012 at 10:21 pm, Rafael said:
A simpler way: Open a window and drag your Numbers. CSV file from the Finder. Done.
Sorry my English 100% Google Translator.
regards
April 07, 2013 at 3:21 pm, Someone said:
The .txt suffix rename does work. I wouldn’t say that “The problem is honestly not iWork’s Numbers program. The problem is that CSV files are not supposed to be tab delimited” considering this works natively in all other major spreadsheet applications