Analistable

Combining CSV files with different delimiters without shifted columns

By the Analistable team · Updated · 5 min read

Columns shift after combining when the files don't share a delimiter (comma, semicolon or tab) or when a field contains an unquoted delimiter. Find each file's separator, re-save them all with one delimiter using a parser that respects quotes, then stack them. Never fix it with find-and-replace on the raw text.

Part of our guide: How to merge CSV files into one

Why the delimiters differ

"Comma-separated" is a convention, not a rule. Excel saves CSVs using the list separator from the computer's regional settings, so a file saved on a machine set to German, French or Dutch usually uses semicolons, because those locales use the comma as the decimal separator. Database tools often export tab-separated text with a .csv name, and some systems use pipes (|).

Three exports of the same data, as raw text
FileFirst data lineDelimiter
uk.csv1001,Alder Ltd,1250.50comma
de.csv1002;Becker GmbH;980,25semicolon, comma decimals
db.csv1003→Cole & Co→430.00tab (shown as →)

Stack these as comma-separated and de.csv becomes two columns (1002;Becker GmbH;980 and 25) while db.csv stays a single column. That is the classic "columns shift after combining" result.

The other cause: commas inside fields

A properly written CSV wraps any field containing the delimiter in double quotes: 1004,"Dunn, Hart & Co",310.00. A parser treats that as three fields. Problems come from hand-made or badly exported files where the quotes are missing, which push every following column one place to the right on that row only. Count delimiters per line to find them:

# lines whose comma count differs from the header's (ignores quoting, so treat as a hint)
awk -F',' 'NR==1{n=NF} NF!=n{print FILENAME": line "FNR" has "NF" fields"}' exports/*.csv

Identify each file's delimiter

  • Open the file in a text editor (not Excel) and look at the header line.
  • A first line of sep=; is an Excel-specific hint that the file uses semicolons. Other tools treat it as data, so remove it before merging with code.
  • In Python, csv.Sniffer().sniff(sample) guesses the delimiter from a sample of text.
  • Excel's Data > From Text/CSV preview also detects it, and you can override it in the Delimiter box.

Normalise, then stack

Read every file with its own delimiter, then write them all out with one. A parser handles quoted fields correctly, which find-and-replace cannot: replacing every ; with , also breaks any field that contained a comma.

import csv, glob
import pandas as pd

frames = []
for f in sorted(glob.glob("exports/*.csv")):
    with open(f, newline="", encoding="utf-8-sig") as fh:
        sep = csv.Sniffer().sniff(fh.read(4096), delimiters=",;\t|").delimiter
    frames.append(pd.read_csv(f, sep=sep, dtype=str, encoding="utf-8-sig"))

pd.concat(frames, ignore_index=True).to_csv("merged.csv", index=False)

Reading as dtype=str keeps 980,25 intact; convert decimal commas deliberately afterwards (str.replace(',', '.') on that column, then to a number). In Power Query, import each file with From Text/CSV, set the right delimiter per query, then Append them; a From Folder query applies one delimiter to every file, so split the folder by delimiter first.

If you only need to stack files that already share a delimiter, the simpler routes in merging CSV files on Windows work. To match rows across files on a key instead of stacking them, see joining two CSV files by a column.

Check the result

  1. Every row has the same number of columns as the header (pandas raises an error on the read if a file is ragged, which is a useful warning).
  2. Numeric columns convert without errors: a stray name in an amount column means a row shifted.
  3. Totals per source file match the totals in each original.

Analistable reads CSVs with DuckDB's automatic dialect detection, which sniffs the delimiter and quoting per file when you load them, and keeps the data in your browser. It is still worth checking the column preview after loading, since detection on very short or unusual files can guess wrong. More methods are in the merge CSV files guide.

Decimal commas and other traps

Semicolon files often carry a second difference: numbers written with a decimal comma (980,25) and sometimes a full stop as the thousands separator (1.250,50). Stacked straight into comma-decimal files, these become text or, worse, are misread by orders of magnitude.

The same amounts in two conventions
Written asConventionCorrect valueMisread as UK number
1250.50UK / US1,250.501,250.50
1.250,50German / Dutch1,250.50text (not a number)
980,25German / Dutch980.2598,025 if the comma is taken as a thousands mark
1,250UK / US1,2501.25 in a decimal-comma locale

In Power Query, convert these with Change Type > Using Locale and pick the file's locale (for example German) with type Decimal Number; it then reads 1.250,50 as 1250.5. In pandas, pass decimal=',' and thousands='.' to read_csv for those files only. Do this per source, before appending, for the same reason as with the delimiter: once mixed, you cannot tell which convention a value used.

Two smaller traps. Line breaks inside a quoted field (common in address or notes columns) are valid CSV but break line-based tools like awk and cat pipelines, so prefer a real parser for anything with free text. And a file that ends with an empty line or a line of delimiters only (;;;) produces a blank row per file, which you can remove after stacking.

Frequently asked questions

Why does Excel save my CSV with semicolons?
Excel uses the list separator from your regional settings. Locales that use a decimal comma normally use a semicolon as the list separator.
Can I just replace semicolons with commas?
Only if no field contains a comma or semicolon. Decimal commas and addresses usually do, so use a parser that reads one delimiter and writes another.
Why are my columns shifted on only some rows?
Those rows have a delimiter inside a field without quotes, or a line break inside a field. Fix the source export or quote the field.
Is a tab-separated file a CSV?
Strictly it is TSV, but many systems name it .csv. Read it with a tab delimiter and it merges like any other file.
Why does Excel open my semicolon CSV as one column?
Your regional list separator is a comma, so Excel splits on commas only. Import the file with Data > From Text/CSV and choose Semicolon in the Delimiter box, or add a first line of sep=; if the file is only ever opened in Excel.
Does Power Query detect the delimiter automatically?
From Text/CSV guesses it from the first rows and shows a preview you can override. From Folder uses the settings of the sample file for every file, so a folder with mixed delimiters needs splitting or separate queries.

Related guides