Download the scripts and tests (ZIP)
Python scripts, tests, and ready-to-run merge, join, and date practice files with expected results. No signup required.
Your export has the right records, but the next tool expects commas between fields. Replacing tabs or semicolons with commas looks tempting until a note contains that same character or a quoted field spans two lines. The delimiter describes the table structure; the same character inside a quoted field is data.
This guide converts an explicitly chosen input format to comma-delimited UTF-8 CSV. You supply the expected header as an additional check. A wrong separator can otherwise produce a seemingly consistent one-column table, so a successful parse alone is not enough.
Tested on Python 3.10 / Windows, September 17, 2026 with synthetic files. The complete script and reproducible tests are in the download above. It uses only the standard library and processes local files.
A sample that breaks search-and-replace
Save the following as UTF-8 export.txt. The input separator is a semicolon; the amount values contain decimal commas, and the second note contains an embedded newline.
id;amount;note
001;12,50;"red; blue"
002;0,00;"line one
line two"
003;;"She said ""yes"""
The file contains a header and three data records, even though a text editor displays five physical lines. Our output will preserve 001, 12,50, the semicolon inside red; blue, the embedded newline, and the quotation marks around yes as field values.
This conversion does not turn decimal commas into decimal points. The amount stays text, and the output CSV writer quotes it because the output separator is a comma.
Complete converter
Save this as convert_csv_delimiter.py beside the input:
"""Convert an explicit delimited table to comma CSV with a required schema."""
import argparse
import csv
import io
from pathlib import Path
DELIMITERS = {'comma': ',', 'semicolon': ';', 'tab': '\t', 'pipe': '|'}
def convert_delimiter(source, output, delimiter, expected_columns):
if delimiter not in DELIMITERS:
raise ValueError('Unsupported delimiter name')
if (not expected_columns or any(not x.strip() for x in expected_columns)
or len(set(expected_columns)) != len(expected_columns)):
raise ValueError('Expected columns must be unique nonblank names')
with Path(source).open(encoding='utf-8-sig', newline='') as handle:
reader = csv.reader(handle, delimiter=DELIMITERS[delimiter], strict=True)
header = next(reader, None)
if header != list(expected_columns):
raise ValueError('Header does not match expected columns and order')
rows = [header]
for number, row in enumerate(reader, 2):
if len(row) != len(header):
raise ValueError('Record %d: expected %d fields, got %d' %
(number, len(header), len(row)))
rows.append(row)
buffer = io.StringIO(newline='')
writer = csv.writer(buffer, lineterminator='\r\n')
writer.writerows(rows)
encoded = buffer.getvalue().encode('utf-8')
with Path(output).open('xb') as handle:
handle.write(encoded)
return len(rows) - 1
def main():
parser = argparse.ArgumentParser(description=__doc__)
parser.add_argument('source', type=Path)
parser.add_argument('output', type=Path)
parser.add_argument('--delimiter', choices=DELIMITERS, required=True)
parser.add_argument('--columns', nargs='+', required=True)
args = parser.parse_args()
try:
count = convert_delimiter(args.source, args.output, args.delimiter, args.columns)
except (OSError, UnicodeError, ValueError, csv.Error) as exc:
parser.exit(1, 'Conversion failed: %s\n' % exc)
print('Converted %d records' % count)
if __name__ == '__main__':
main()
Run the sample:
python convert_csv_delimiter.py export.txt converted.csv --delimiter semicolon --columns id amount note
Expected message:
Converted 3 records
The output starts like this:
id,amount,note
001,"12,50",red; blue
002,"0,00","line one
line two"
003,,"She said ""yes"""
Each output record ends with CRLF. The newline stored inside the note remains whatever newline characters the input field contained; reading and writing with newline='' avoids translating those characters. Output quoting may differ from input quoting while the parsed cell values remain the same.
Select the input format explicitly
| Input structure | Option |
|---|---|
| Commas | --delimiter comma |
| Semicolons | --delimiter semicolon |
| Tab characters | --delimiter tab |
| Vertical bars | --delimiter pipe |
For a TSV export with columns id, name, and note, use:
python convert_csv_delimiter.py export.tsv converted.csv --delimiter tab --columns id name note
You type the word tab, not an actual tab or a backslash escape. The filename extension does not choose the separator. A file called data.csv can still contain semicolons, and a .txt file can contain a properly quoted table.
The --columns option is a required schema check, not a request to select or rename columns. Names and order must match the entire input header exactly. For a name containing spaces, quote that name in the command, for example --columns id "customer name" note. Leading and trailing spaces in real headers are retained and must match; the script does not silently trim them.
Verify values, not just the displayed file
Save the following as check_result.py in the sample directory and run python check_result.py:
import csv
with open('converted.csv', encoding='utf-8', newline='') as handle:
rows = list(csv.reader(handle))
assert rows[0] == ['id', 'amount', 'note']
assert len(rows) == 4
assert rows[1] == ['001', '12,50', 'red; blue']
assert rows[2][0:2] == ['002', '0,00']
assert rows[2][2].splitlines() == ['line one', 'line two']
assert rows[3] == ['003', '', 'She said "yes"']
print('Sample checks passed')
The downloadable tests compare parsed records across all four supported separators. They also check Unicode, UTF-8 BOM input, quoted newlines, empty fields, a header-only table, source preservation, and command-line exit codes. Wrong headers, the wrong separator for the expected schema, short or extra records, blank physical records outside quotes, malformed quoting, invalid UTF-8, and overwrite attempts have separate rejection checks.
Diagnose conversion failures
Header does not match: check the separator and the complete expected column list first. A comma-separated file read as semicolon-separated may yield one header field named id,name. The script rejects that when you asked for two columns, id and name. Do not change the expected list simply to silence the error without examining the source export.
Wrong field count: the error uses the CSV record number, counting the header as 1. A multiline quoted field is still one record. An unquoted separator inside a note can create an extra field; fix the producing application or correct a copy of the source.
Decode error: this script accepts UTF-8 with or without a BOM. It does not guess legacy encodings. The encoding conversion guide explains explicit decoding choices, but its supplied CSV validator assumes comma-separated records; it is not directly interchangeable with this delimiter converter for a legacy-encoded TSV. Obtain a UTF-8 export or deliberately adapt and test the input encoding before continuing.
File already exists: choose a fresh output filename. The script opens output in exclusive binary mode and refuses to overwrite an existing file, including the source. It also requires an existing parent directory.
Supported dialect and limits
The input parser uses double quotes around quoted fields, doubled quotes inside those fields, and no backslash escape character. It does not support every application’s custom dialect, a sep=; preamble, comment lines, multiple header blocks, or multiple-character separators. Strict parsing catches certain malformed quote patterns; it is not a guarantee that every possible irregular source has been interpreted as its author intended. The expected schema and sample comparisons provide additional checks.
All input rows and the serialized result are held in memory before output is opened. Validation errors therefore create no output file. Use this for manageable exports, not an unbounded data stream. A later disk or device failure during writing can still leave a partial file; only a successful exit confirms that the converter completed. Exit code 0 means success and 1 means a handled processing failure; argument usage errors are handled separately by argparse.
The converter retains field text, not original bytes, line layout, or optional quoting. It does not sort, deduplicate, infer dates, normalize numbers, or change formulas. Spreadsheet software may still reinterpret IDs or formula-like strings when opening the CSV. Import ID columns as text and review untrusted cell values before spreadsheet use.
After conversion, use CSV column selection if you need fewer fields, or CSV merging to combine exports with matching headers. Keep the original files until the downstream result has been checked.
Reference
Python’s official CSV documentation documents delimiters, quoting, string-valued records, strict parsing, and newline=''. The required schema, supported delimiter names, validation-before-write design, and overwrite refusal are deliberate choices in this example rather than universal CSV requirements.