How to read a semicolon-separated file

To read a semicolon-separated file, use clickhouse local. It runs SQL directly on files from the command line, with no server to install. It's part of ClickHouse, so the same query scales to billions of rows when you outgrow your laptop.

Install it with clickhousectl:

Then set the field delimiter to ; and query:

CustomSeparatedWithNames reads the header row for column names and infers types from the data; the ; delimiter is just a setting. The file is read in place with no import step first. Semicolon files are the common European export from spreadsheets and ERP systems, where the comma is already the decimal separator. That detail has a sting in the tail; see the gotcha section below.

See the schema without declaring one #

CustomSeparatedWithNames takes the column names from the header and infers each type from the data. DESCRIBE shows what it found:

The header gave the names; the data gave the types. The format_custom_escaping_rule = 'CSV' setting means values follow CSV quoting rules, so a field that itself contains a ; can be wrapped in quotes and still parse. If your file has no header, use the CustomSeparated format instead of CustomSeparatedWithNames and pass an explicit schema as the third argument to file().

Aggregate directly on the file #

Because the file is a table, the full SQL surface works on it (WHERE, GROUP BY, JOIN, window functions) with no load step in between:

The decimal-comma gotcha #

This is the catch with European semicolon files, and it is exactly why the delimiter is ; rather than , in the first place: the comma is the decimal separator. A price written 1234,50 means one thousand two hundred thirty-four and a half. ClickHouse infers types per the C locale, where the decimal point is ., so it sees 1234,50 as text and types the whole column as String:

The fix is one expression: read the column as text, swap the comma for a point, and cast to a number. replaceOne(revenue, ',', '.')::Float64 does it inline:

Now revenue_eur is a real Float64 and aggregates correctly:

If thousands are also grouped with a separator (for example 1.234,50), strip the grouping dot first: replaceOne(replaceAll(revenue, '.', ''), ',', '.')::Float64. Do the conversion once at read time and the rest of your SQL stays clean.

The same SQL runs unchanged across dozens of file formats and remote sources, and against a ClickHouse server or ClickHouse Cloud when the data outgrows your machine.

Compressed files work too #

You do not unzip anything first. clickhouse local detects a .gz, .zst, .xz or similar suffix from the file name and decompresses on the fly, so a orders.csv.gz semicolon file is read exactly like the plain one — same file() call, same ; settings.

How fast is it? #

The import step you skipped was the slow part. On a 3,000,000-row, ~110 MB semicolon file (orders_large.csv), a full GROUP BY country with sum and avg over every row runs in:

~0.59 seconds, best of three with a warm OS page cache, on an Apple M4 Pro laptop (14 cores, 24 GB RAM; clickhouse local 26.6.1.117). That includes parsing the delimited text from scratch on every run; there is no cached table. Convert the same data to Parquet once and repeat scans get faster again, because Parquet is columnar and typed.

The same SQL scales unchanged #

The query you just ran on a laptop file is the same SQL you would run on a ClickHouse server or in ClickHouse Cloud. Nothing about SELECT ... WHERE ... GROUP BY changes. You swap file('orders.csv', ...) for a table name and the rest stays put. Prototype against a file on your machine, ship the identical logic to production.

Run it yourself #

The complete, runnable example lives in the ClickHouse examples repo. It has generate.sh (builds the demo file, the European decimal-comma file, and the ~110 MB perf file), run.sh (every command above), and expected_output.txt:

github.com/ClickHouse/examples → local-analytics/clickhouse-local-semicolon

Related #

• Run SQL on a CSV file
• Read a pipe-delimited file
• Read a file with any custom delimiter