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.
Add a comment
|
6 Answers
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
-
2It'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) Commented Apr 18, 2019 at 14:16
-
2
-
3Okay. for this to work you have to click on any cell for this options to appear. Commented Jul 29, 2020 at 14:11
-
What do I do when I want to change the delimeter while exporting the file? Right now the way you suggested just changes the actual table that's open but I want to save with a different delimeter. Commented Feb 18, 2022 at 11:52
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.
-
1While 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) Commented Jan 9, 2017 at 14:20
-
It's not converting the format, it's still a CSV except that it's using a different separator character– AllanCommented Jan 9, 2017 at 14:35
-
That's what I meant by format. The separator is the problem here Commented Jan 9, 2017 at 14:36
-
2That's a limitation of Numbers. The only option is to work with your data as a CSV file then covert the separators using
sedthrough Terminal or use Excel Online to save it as a CSV with your preferred format.– AllanCommented Jan 9, 2017 at 14:42
open your csv file in Numbers
You should see a
Adjust Settingsthing pop up, click itYou'll see an
Import Settingsdialog make sure you click onDelimitedin the Custom Delimiter field add
|Click
Update Table
-
-
@MemphisMeng Click anywhere in the table and then click on "Format" (top right) and then scroll down to the bottom, you'll find "Adjust Import Settings..." Commented Apr 8, 2023 at 9:16
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.
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
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
-
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.– fsbCommented Nov 6, 2019 at 15:22