Opening a Semicolon Separated CSV in Excel Without Mess

A semicolon separated file is not malformed. It is the normal CSV in a large part of the world, produced by Excel itself on any machine whose region uses a decimal comma, because a comma in the number would be ambiguous as a field separator. When that file reaches a machine set to a United States locale, Excel shows every row as one long cell, and the sender and the recipient each conclude the other did something wrong. Neither did. This article gives the ways to open the file properly, and the one trap in converting it that costs people their numbers. The general picture is in opening CSV files in Excel and Sheets without damage, and the cause is explained in why Excel shows a CSV in one column.

The file and the symptom

Here is a small export from a German accounting system. The separator is a semicolon, and the totals use a decimal comma:

order_id;customer;total
1001;Müller GmbH;1234,50
1002;Dupont & Fils;89,90

Double-click it on a machine with the US region and Excel puts order_id;customer;total in cell A1 and the same shape in every row below it. The data is intact, because the delimiter is a property of how Excel reads it and not of the file. On a machine set to German, the same double-click gives three tidy columns, and 1234,50 is recognized as a number. That asymmetry is the thing to remember: the file does not change between the two machines, only the setting that interprets it.

Import it and name the delimiter

The reliable route is to stop double-clicking. In current Microsoft 365, choose Data > From Text/CSV and pick the file. The preview dialog shows the delimiter Excel guessed. If it chose comma or showed one column, open the Delimiter dropdown and choose Semicolon. Excel redraws the preview. Check the File origin (UTF-8 for a modern export, or a Windows code page for an older one) so that Müller is not shown as Müller, then choose Load, or Transform Data if you want to set column types.

The dialog does not touch the decimal comma. In the preview, 1234,50 stays text under a US locale, and loading it leaves a column of text that formulas ignore. Power Query handles this with a locale-aware type change. In Transform Data, right-click the total column header, choose Change Type > Using Locale, pick Decimal Number and set the locale to German (Germany). The same step reads dates in the right order. That is the proper route for a feed you receive every week, as in importing a CSV into Excel with Power Query.

Split a file that is already open

If the file is already open as one column, you can repair it without re-importing. Select column A, choose Data > Text to Columns, pick Delimited, tick Semicolon and nothing else, and finish. Excel splits each cell in place.

There are two catches. Text to Columns applies Excel's type guessing to every new column, which is exactly the damage described in why Excel drops leading zeros. If a column holds codes with zeros, choose it in step three and set its column data format to Text before you finish. And any cell that has been through the one-column state has already been interpreted once, so a long number may have been rounded before you split it. Splitting in place is acceptable for a quick look and a poor way to get data you will keep.

Write the delimiter into the file

If you produce the file and your readers use Excel, you can tell Excel which separator to use for that one file. Add a first line that contains only sep=;:

sep=;
order_id;customer;total
1001;Müller GmbH;1234,50

Excel on Windows reads that line as an instruction and uses a semicolon regardless of the region. The price is that it is not part of the CSV format. RFC 4180 knows nothing of it, so Python, DuckDB, pandas and every database loader see sep=; as the header row, and the real header as data. I use it only for a file that I know will be opened by a person in Excel and by nothing else, and I put a note in the filename. If a program might read it too, skip it and use one of the other routes.

Change the list separator

The setting that decides a double-click lives in Windows: Settings > Time & language > Region > Additional date, time & regional settings > Change date, time or number formats > Additional settings, under List separator. Set it to a comma and a double-clicked comma file opens correctly; set it to a semicolon and a semicolon file does.

Consider the side effect before changing it. In locales that use a decimal comma, Excel also uses the list separator as the argument separator in formulas, so =SUM(A1;B1) becomes =SUM(A1,B1) and an old workbook with the semicolon form may show errors. The setting is per user and per machine, which is why a colleague's file will not open the way yours does. I would change it on my own laptop if I read mostly comma files and leave a shared machine alone.

Convert the file to commas properly

For a file that goes on to a database or a script, convert it once and keep the result. The obvious method is to read with one delimiter and write with another:

import csv

with open("semi.csv", newline="", encoding="utf-8") as src, \
     open("comma.csv", "w", newline="", encoding="utf-8") as dst:
    reader = csv.reader(src, delimiter=";")
    writer = csv.writer(dst)
    for row in reader:
        writer.writerow(row)

It looks right and it is not:

order_id,customer,total
1001,Müller GmbH,"1234,50"
1002,Dupont & Fils,"89,90"

The writer saw a comma inside 1234,50, so it quoted the field, correctly. A reader now gets a text column with commas in it, which is not a number, and a spreadsheet in a German locale would read it as one but a US one will not. The decimal separator has to change too, and a script that knows the type of each column can do it. In DuckDB:

COPY (
  SELECT * FROM read_csv('semi.csv', delim=';', decimal_separator=',',
         columns={'order_id':'INTEGER','customer':'VARCHAR','total':'DECIMAL(10,2)'},
         header=true)
) TO 'comma.csv' (HEADER, DELIMITER ',');

order_id,customer,total
1001,Müller GmbH,1234.50
1002,Dupont & Fils,89.90

The decimal_separator option and an explicit DECIMAL(10,2) type give 1234.50 and nothing is quoted. The explicit column list is deliberate, because a type you declare cannot be mis-sniffed when one row has an odd value; see DuckDB's CSV reading options. A text column that has a decimal comma inside it by coincidence, such as a free-form note, is left alone because only the total column is declared a decimal.

Other programs

Google Sheets detects a semicolon on import and also lets you choose it under File > Import > Separator type. LibreOffice Calc always shows a Text Import dialog on opening a .csv, with a separator checklist, which is why it rarely has this problem. Excel for Mac uses the system's region settings in a similar way to Windows and also offers Data > From Text with a delimiter choice. If you are unsure which delimiter a file uses, check the first lines before importing (telling what delimiter an unknown CSV file uses), and see comma, semicolon, tab or pipe for why the choice varies by region.

When you should ask the sender to change the file

Asking is right when the file feeds a pipeline. A semicolon with a decimal comma is a regional convention, and a pipeline that must accept files from several countries should state a single format in its contract: comma delimiter, decimal point, ISO 8601 dates, UTF-8. Say so in writing and reject files that differ, rather than guess. The format your tools prefer is also the one every database COPY and every library defaults to.

If the sender is an application that exports by region and cannot be changed, accept the file as it is and convert at the door with a script like the one above, with the delimiter and decimal separator declared as parameters of that source. Do not ask a person to open it in Excel and re-save, because that is where the second round of damage happens. Microsoft's import and export of text files page lists the import options, and DuckDB's CSV overview lists the reader parameters used above.