JOV Download

How to open a CSV file on a Mac so the columns line up

. 4 minute read. By JOV

A CSV file is a table saved as plain text. Each line is a row, and a separator, usually a comma, sits between the values. Banks, shops, survey tools and databases all export CSV because every program can read it. The trouble starts when the program that opens it guesses wrong about the separator or about what the values mean.

Why you see commas instead of columns

If a CSV opens in TextEdit, you see the raw text:

order,postcode,amount
1001,02134,19.50
1002,90210,7.00

Nothing is wrong with the file. TextEdit is a text editor and shows text. To see columns you need to open the file in an app that reads the separators. Right-click the file in Finder, choose Open With, and pick a spreadsheet app.

For a quick look, select the file and press the Space bar. Quick Look shows a CSV as a table without opening anything.

Why everything lands in one column

Sometimes a spreadsheet app opens the file but puts each whole line into column A. That happens when the file uses a different separator from the one the app expects. Files exported in much of Europe use a semicolon, because the comma is the decimal mark there. Other files use a tab or a vertical bar.

The fix is to tell the app which separator the file uses. Where you do that depends on the app, and some apps only offer it in an import dialog, not when you double-click the file.

Numbers

Numbers opens a CSV as a table and usually guesses the separator correctly. If it guesses wrong, the message that appears after the import has a button for adjusting the import settings, where you can pick the separator and the text encoding.

Numbers turns the CSV into a Numbers document. To get a CSV again you choose File > Export To > CSV, which writes a new file. It also decides for itself what each value is, which leads to the next problem.

Leading zeros and long numbers

Spreadsheet apps like to turn anything that looks like a number into a number. That is what you want for prices. It is not what you want for:

  • postcodes and ZIP codes such as 02134, which become 2134
  • phone numbers and account numbers that start with zero
  • long identifiers such as 16-digit order or card reference numbers, which are rounded or shown as 1.23E+15
  • product codes such as 3-10, which can become a date

The file on disk is still correct at that point. The damage is done when you save, because the app writes back what it now believes the values are. If you only opened the file to look, close it without saving.

In Excel and Numbers the way around this is to import the file and mark those columns as text before the data is loaded. It works, but you have to remember to do it every time.

JOV Office

JOV Office opens a CSV as a table and treats every value as the text that is in the file. A postcode that starts with zero keeps its zero, and a long number is shown digit for digit. Columns that hold numbers are aligned to the right so they read like numbers, but nothing is converted.

It works out the separator (comma, semicolon, tab or vertical bar) and the text encoding from the file. If the guess is uncertain, a bar above the table says so and offers CSV Options, where you can choose the separator, the encoding and whether the first row is a header. Changing these only changes how the file is read. The file itself is not touched.

When you edit and save, the file stays a CSV. Rows you didn’t change are written back exactly as they were, with the same separator, quoting and line endings, so a system that reads the file afterwards sees only your edits.

Two things a CSV can’t hold, in any app:

  • Formatting. Colours, column widths and frozen rows are not part of a CSV, so they are gone when you close the file.
  • Formulas. If you type a formula, its result is saved, not the formula.

If you want to keep either, choose File > Export To > Excel Workbook and carry on in the .xlsx copy. Going the other way, File > Export To > CSV saves the current sheet of a workbook as a UTF-8 CSV with the values as they are shown.

JOV Office needs macOS 14 Sonoma or later and is free for 7 days, then $2 a month or $15 a year. Your files stay on your Mac.

A short checklist before you send a CSV on

  1. Open the file in a text editor, or press Space in Finder, and look at the first lines. You will see the separator and whether there is a header row.
  2. Check one value that starts with zero and one long number after opening it in your spreadsheet app.
  3. If you only needed to look, close without saving.
  4. If the file goes into another system, keep it as CSV. Sending an .xlsx to a system that expects CSV rarely works.

Make CSV files open in the app you want

  1. Select a .csv file in Finder and press Command-I.
  2. Under “Open with”, choose the app.
  3. Click Change All, then Continue.

If the file is really an Excel workbook, see how to open an Excel file on a Mac without Excel.

All posts