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.


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.

  • zach

    nice!

  • Anonymous

    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

    I have no luck…

  • Luke

    I’ve got semi colon delimited data and this work around doesn’t work. Not happy with Apple about this one.

  • Anonymous

    Thanks so much for this information it’s been really helpful and is much appreciated.

  • Anonymous

    ” 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

    the txt. tip does not work here…

    I`m on snow Leopard and iWork 09

    Do you have an idea? It is still”messy”

  • http://www.facebook.com/Todsaporn Todsaporn Wattanasupinyo

    Thank, this post help me.

  • denis

    for ; separeted CSV’s open CSV with textEdit, and replace all ; with , then just import it in numbers.

    • Amanda

      > Thank you so much! This is the only tip I found that worked.

  • Richard

    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!

    • Peggy Li

      > Is there any way to import a plain table which was made with any text editor in Numbers spreadsheet without messing it up

  • Vjayant

    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

    thank you!

  • Zaheadblader

    Doesnt work for me. OSX 10.6.4

  • http://findablemarketing.com Jules

    change the extension to .csv it opens in numbers ok then on snow leopard (iwork 09)

  • qo

    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.

  • http://www.facebook.com/people/Vincenzo-Pignatelli/756411841 Vincenzo Pignatelli

    thank you, geat!

  • Ch0c0

    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

      Sure, take 5 minutes to write a perl script to perform the search/replace.

    • qo

      Sure, take 5 minutes to write a perl script to perform the search/replace.

    • qoqo

      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

        Opps, sorry for cat’ing the script twice :-(

    • qo

      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.:

    • Ramon

      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.

  • qo

    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.

  • Ian

    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 :-)

  • http://www.bedrijfinsocialmedia.nl/ Bedrijf op Facebook

    Wow! Worked perfectly. It’s important that you change it to TXT –>before

  • Tom Pollard

    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

    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.

  • http://philsolano.com Phil

    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

    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

    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.

  • Louis

    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

  • vib

    thank you :p

  • Rafael

    A simpler way: Open a window and drag your Numbers. CSV file from the Finder. Done.

    Sorry my English 100% Google Translator.

    regards

  • Someone

    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

  • Mizner Country Club

    Thank you so much! So simple and effective – I was almost ready to install office on my iMac.

  • Aurélien Debord

    Thanks for this very helpful tip :)