9 Replies Latest reply: Jan 23, 2015 6:35 PM by Badunit
peterfrommaastricht Level 1 Level 1 (0 points)

I need to import semicolon seperated CSV files on a regular basis in a spreadsheet. I used to do this in Excel, but I would like to switch to numbers and do it from there. I was unable to do it thusfar, any suggestions anyone?


iMac, OS X Mavericks (10.9.1)
  • Yellowbox Level 5 Level 5 (6,170 points)

    Hi Peter,

     

    Right click on the CSV and Open With > TextEdit.

     

    Edit > Find > Find and Replace ; with ,

     

    Screen Shot 2014-01-30 at 10.02.24 pm.png

     

    Replace All and Save. Open in Numbers:

     

    Screen Shot 2014-01-30 at 10.03.50 pm.png

     

    This will work if there are no semicolons within text strings.

     

    Regards,

    Ian.

     

    Edit, Column A does contain the full dates. The column was too narrow

     

    Message was edited by: Yellowbox

  • Yellowbox Level 5 Level 5 (6,170 points)

    Hi Peter,

     

    Another thought: Does your region use a comma as the decimal separator?

     

    So 2.35 in my region is 2,35 in your region?

     

    If so, we need to think again!

     

    Regards,

    Ian.

  • Wayne Contello Level 6 Level 6 (14,885 points)

    The latest update for Numbers (version 3.2) added CSV import features you may want to try.

  • DrMLBBecker Level 1 Level 1 (0 points)

    When I do this, a message says "Numbers cannot open .rtf files"

  • Badunit Level 6 Level 6 (11,380 points)

    DrMLBBecker wrote:

     

    When I do this, a message says "Numbers cannot open .rtf files"

     

    A proper CSV file will be filename.csv not filename.rtf. RTF is "rich text format".  TextEdit defaults to rich text format for new files. In TextEdit, use the menu command Format/Make Plain Text then save the file with a csv extension (not txt). 

  • SGIII Level 5 Level 5 (5,245 points)

    If you're doing this a lot, you may find this CSV to Tabs on Clipboard (Dropbox download) useful.

     

    To install in your Finder > Services menu, double-click the workflow package.  (You may need to click 'Open Anyway' in System Preferences > Security & Privacy).

     

    To use,

     

    1. In Finder right-click the CSV (comma, tab or semi-colon separated) file and choose the service from the Services Menu.
    2. Then in Numbers, click once in a cell, and command-v to paste.

     

    That's it!  The Numbers table will expand automatically if needed.

     

    Thanks to Badunit who helped convert the AppleScript into a service and to MacScripter contributor Nigel Garvey for posting the intrepid AppleScript handler (subroutine) that can handle all kinds of challenging situations with character-separated-values that stymy many other scripts.  If for some reason this can't handle your particular files, you can also search the boards for an equally sophisticated script by Hiroto.

     

    I find this approach much faster and more convenient than the built-in Numbers import.  Among other things, I like being able target exactly where the data goes in an existing document by simply clicking the "upper left" cell of the range where I want it.

     

    SG

  • DrMLBBecker Level 1 Level 1 (0 points)

    Thanks.

  • DrMLBBecker Level 1 Level 1 (0 points)

    Have you seen a script to convert a tabbed list (or a cvs file) into a group in Contacts?  It is tedious manually.

  • Badunit Level 6 Level 6 (11,380 points)

    The script for converting a CSV into a tab-delimited file for pasting into Numbers is really nice. You might want to consider using it. It does a better job of converting CSV files than Numbers 3.0 does itself.

     

    I have not seen a script to convert a tabbed list into a group in contacts. My personal address book is small and I've had no need for such a script.