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.

39 Responses to “iWork Numbers: How to Open Tab Delimited CSV Files”

  1. January 25, 2009 at 7:28 am, zach said:

    nice!

    Reply

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

    Reply

  3. February 26, 2009 at 5:53 pm, unlucky said:

    I have no luck…

    Reply

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

    Reply

  5. August 30, 2009 at 10:23 am, Anonymous said:

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

    Reply

  6. 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???

    Reply

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

    Reply

  8. April 23, 2010 at 9:45 am, Todsaporn Wattanasupinyo said:

    Thank, this post help me.

    Reply

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

    Reply

    • July 13, 2012 at 7:45 pm, Amanda said:

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

      Reply

  10. 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!

    Reply

    • 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

      Reply

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

    Reply

  12. June 25, 2010 at 5:42 pm, kbm said:

    thank you!

    Reply

  13. August 08, 2010 at 9:39 am, Zaheadblader said:

    Doesnt work for me. OSX 10.6.4

    Reply

  14. 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)

    Reply

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

    Reply

  16. June 06, 2011 at 8:17 am, Vincenzo Pignatelli said:

    thank you, geat!

    Reply

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

    Reply

    • July 01, 2011 at 1:33 pm, qo said:

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

      Reply

    • July 01, 2011 at 1:33 pm, qo said:

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

      Reply

    • 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]$

      Reply

      • July 01, 2011 at 5:44 pm, qoqo said:

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

        Reply

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

      Reply

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

      Reply

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

    Reply

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

    Reply

  20. October 24, 2011 at 11:40 am, Bedrijf op Facebook said:

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

    Reply

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

    Reply

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

    Reply

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

    Reply

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

    Reply

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

    Reply

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

    Reply

  27. April 25, 2012 at 3:53 am, vib said:

    thank you :p

    Reply

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

    Reply

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

    Reply

  30. December 04, 2013 at 11:21 am, Mizner Country Club said:

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

    Reply

  31. March 06, 2014 at 6:03 am, Aurélien Debord said:

    Thanks for this very helpful tip :)

    Reply

Leave a Reply