6

I'm currently using the default Numbers application to open my CSVs. My current use case involves a file that I had to use a different type of separator (|). Is there any way to change the separator? Right now when I open the file it just puts the entire row in one column.

13

In Numbers you can use any separator when importing csv tables.

On the format sidebar, in the table tab, look for adjust Import settings. Inside it you have delimited options where you can even use custom delimiters

3
  • 1
    It's worth noting that this option is only shown when a CSV is already open, and it won't appear for non-CSV files :o) Apr 18 '19 at 14:16
  • no such option in Numbers 10.1 (6913)
    – DataGreed
    Jul 29 '20 at 14:07
  • 1
    Okay. for this to work you have to click on any cell for this options to appear.
    – DataGreed
    Jul 29 '20 at 14:11
3

Use sed; the Stream EDitor from Terminal.

sed -i -e 's/"|"/|,"/g' filename

s is used to replace the found expression "|" with ","

g is for "global", meaning do this to the whole line. If you omit the g option it will only replace the first instance, ignoring the rest

-i option is to "edit in place" meaning it changes the file itself.

-e option indicates the expression/command to run.

4
  • 1
    While this is very helpful if I were to convert the format, it does not help my current use case. I'm asking specifically to be able to use a different separator because my data contains commas. Thanks though, love how you explained what each part of the command helps do (plus I hadn't heard of sed before)
    – Tom Prats
    Jan 9 '17 at 14:20
  • It's not converting the format, it's still a CSV except that it's using a different separator character
    – Allan
    Jan 9 '17 at 14:35
  • That's what I meant by format. The separator is the problem here
    – Tom Prats
    Jan 9 '17 at 14:36
  • 2
    That's a limitation of Numbers. The only option is to work with your data as a CSV file then covert the separators using sed through Terminal or use Excel Online to save it as a CSV with your preferred format.
    – Allan
    Jan 9 '17 at 14:42
1
  • open your csv file in Numbers

  • You should see a Adjust Settings thing pop up, click it

  • You'll see an Import Settings dialog make sure you click on Delimited

  • in the Custom Delimiter field add |

  • Click Update Table

  • Adjust Settings

enter image description here

0

Another option, you can open it in OpenOffice/LibreOffice. It lets you pick exactly what the delimiters are. Then once it opens, save a copy in some other format, then you can open it in Pages.

0

Using the -i option for sed is courageous, but also dangerous. I would recommend the following:

sed -e 's/"|"/|,"/g' filename > tmp

Then, check that the file tmp is formatted as you wanted it to be. If it is ok, then you can replace the original file with tmp:

mv tmp filename
-2

Numbers >=5.0 after opening a csv-File you get a floating dialog "Table data was imported. Adjust Settings". When hitting "Adjust settings" you can control the delimiters. Found this solution in this tutorial

1
  • This answer was already provided in the accepted answer. To avoid downvotes you might want to edit the accepted answer to add the part about the floating sidebar which is just a different way to get to the same setting.
    – fsb
    Nov 6 '19 at 15:22

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .