ClickHouse Code 117 “Expected end of line”: causes and fixes

Code: 117. DB::Exception: Expected end of line

This error shows up when you load CSV into ClickHouse. You might be piping a file into clickhouse-client --query="INSERT INTO t FORMAT CSV", running INSERT ... FROM INFILE, or reading with the file(), url() or s3() table functions. ClickHouse read all the columns it expected for one row. The next byte was not a line break and not the end of the data.

So something in that row runs longer than the table expects. Usually it is one of three things: a quote that ClickHouse read differently from how the writer meant it, a row with more fields than the table, or a last-column value that does not fit its type. This page shows how to find the bad row, how to tell which of these you have, and how to fix each one.

What “Expected end of line” means in ClickHouse’s CSV parser

Code 117 is INCORRECT_DATA. The message comes from ClickHouse’s CSV row reader. In ClickHouse, a CSV line must end with \n, \r\n or \n\r, as a ClickHouse pull request on CR line endings spells out. Once the parser finishes the last column of a row, it expects one of those line endings or the end of the data. If it finds anything else, it stops with Expected end of line. A carriage return with no line feed after it gets a related message: Cannot parse CSV format: found \r (CR) not followed by \n (LF).

To use that: the parser decided the last field had ended, but there was still text left on the line. Each type’s reader takes only what fits. A number reader takes digits and stops at the first letter. A quoted string stops at its closing quote. Whatever comes after that point is what the parser trips on.

The CSV format docs state the rules that matter most here:

• A double quote inside a string is written as two double quotes, and there are no other escaping rules.
• Values can be read with or without quotes, and both double and single quotes are supported.
• The delimiter is , by default, set by format_csv_delimiter.
• Unquoted values are read up to the delimiter or the line feed.

All the causes below are a row breaking one of these rules.

graph TB A[Code 117: Expected end of line] --> B{Backslash before quotes?} B -->|yes| C[Double the quotes or fix the writer] B -->|no| D{More fields than table columns?} D -->|yes| E[Quote values or allow variable columns] D -->|no| F{Last value fits its column type?} F -->|no| G[Clean value or use default on bad values] F -->|yes| H{Single-quote setting on and in use?} H -->|yes| I[Set format_csv_allow_single_quotes to 0] H -->|no| J[Check line endings and truncated input] style A fill:#fee2e2,stroke:#fca5a5,color:#7f1d1d style C fill:#d1fae5,stroke:#6ee7b7,color:#065f46 style E fill:#d1fae5,stroke:#6ee7b7,color:#065f46 style G fill:#d1fae5,stroke:#6ee7b7,color:#065f46 style I fill:#d1fae5,stroke:#6ee7b7,color:#065f46

Find the broken row before you change anything

All the fixes depend on seeing the exact bytes of the row that failed.

1. Read the whole error output. On a parse failure, ClickHouse prints a diagnostic block for the row: one line per column with the text it parsed, then an ERROR: line. Note where the parsed text of the last column stops and what comes after it.
2. Print the raw line with hidden characters showing. If the row number is 2:

sed -n '2p' data.csv | cat -A

In GNU cat, -A shows a tab as ^I, other control characters such as a carriage return in ^ notation (^M), and a $ at the end of each line. Quotes and backslashes show up as they are.

3. Parse the file locally with the table’s structure. clickhouse local runs the same parser on your machine, so you can try settings without touching the server:

clickhouse local --query "
  SELECT *
  FROM file('data.csv', CSV, 'ts String, text String')
  LIMIT 10"

4. Collect every bad row in one pass. Each run stops at the first error. To list them all, allow some errors and record them. input_format_allow_errors_num and input_format_allow_errors_ratio both default to 0. The docs say to always set them as a pair. ClickHouse throws only when both limits are exceeded. input_format_record_errors_file_path names a file that records the errors.

clickhouse local --query "
  SELECT count()
  FROM file('data.csv', CSV, 'ts String, text String')
  SETTINGS input_format_allow_errors_num = 100000,
           input_format_allow_errors_ratio = 1,
           input_format_record_errors_file_path = 'csv_errors'"

Read the errors file, and you have a list of bad rows to work through. Use this to diagnose. Don’t keep these settings on a real load, because they drop rows.

Cause 1: quotes escaped with a backslash

This is a common case. Some writers escape a quote inside a quoted field with a backslash:

"Fri, 01 May 2020 06:47:05 UTC","<html id=\"html-div\">"

ClickHouse CSV knows only doubled quotes. It reads the second field as <html id=\, which ends at the quote right after the backslash. Then it finds html-div... where the line should end. A row with the same timestamp and a plain value such as "col2" loads fine, and that is the giveaway.

How to tell it’s yours: search for a backslash followed by a quote.

grep -c '\\"' data.csv

A count above zero, combined with the bad row from the steps above, confirms it.

Fix:

1. Fix the writer. Configure the tool that produces the file to escape quotes by doubling them, as the CSV spec and the ClickHouse docs require. Every later load then works without a cleanup step.
2. If you can’t change the writer, rewrite the escapes on the way in:

sed 's/\\"/""/g' data.csv | clickhouse-client --query="INSERT INTO logs.processing FORMAT CSV"

3. Check edge cases before you trust the rewrite. A field that really ends in a backslash, such as "C:\", gets changed too. Run the clickhouse local check from the previous section on the rewritten output, and compare row counts with wc -l.

Cause 2: the row has more fields than the table

If an unquoted value contains the delimiter, the row splits into one more field than you meant: 1,Acme, Inc.,2024-01-01 is four fields. The same thing happens when the file has an extra trailing column, or when the table has fewer columns than the file. The parser fills the last table column, then finds another delimiter followed by more data where the line should end.

How to tell it’s yours: in the diagnostic, the last column looks complete, and the leftover text after it starts with another delimiter. For files without quoted fields, count fields per line:

awk -F',' '{print NF}' data.csv | sort | uniq -c

If most lines have one count and a few have a higher one, those few are your rows. This count is wrong for quoted fields that contain commas, so use it only as a quick first look.

Fix:

1. Quote any value that can contain the delimiter, at the source.
2. If the commas belong to the data and the file really uses another separator, set it with format_csv_delimiter:

clickhouse-client --format_csv_delimiter="|" --query="INSERT INTO t FORMAT CSV" < data.csv

3. If extra columns are expected and you want them dropped, enable input_format_csv_allow_variable_number_of_columns. It defaults to 0. When on, ClickHouse ignores extra columns and fills missing fields with default values:

INSERT INTO t
SETTINGS input_format_csv_allow_variable_number_of_columns = 1
FORMAT CSV

Be careful with step 3. A row with a stray comma loads with its fields shifted and its last part thrown away, and you get no error. Use it only when you know where the extra columns are.

Cause 3: the last column’s value doesn’t fit its type

If the last column is a number and the row holds 12a or abc, the number reader takes what it can and stops. The rest of the value sits where the line should end. The same applies to any type whose reader stops early on bad input.

How to tell it’s yours: in the diagnostic, the last column’s parsed text is cut short, and the rest of the value comes after it. Compare it with the column type:

DESCRIBE TABLE t

Also check how the file writes nulls. ClickHouse reads \N as null by default, per format_csv_null_representation. A file that writes nulls as a word, such as Nothing or NULL, sends that word to the column’s type reader.

Fix:

1. Clean the value at the source, or change the column type to one that holds what the file really contains. For example, use String for a code column that sometimes has letters, or Nullable(...) for a column with gaps.
2. Match the null token the file uses:

SET format_csv_null_representation = 'Nothing'

3. If losing the bad value is acceptable, input_format_csv_use_default_on_bad_values (default 0) writes the column default when a field fails to parse. Note that this replaces bad data with zeros or empty strings, and you get no error.

Cause 4: a field starts with a single quote

This cause only applies where format_csv_allow_single_quotes is set to 1, on an older version or through explicit config. The CSV docs list the setting as on by default, but a ClickHouse issue reports that line is wrong and the real default is false. So start by checking the setting on your server:

SELECT name, value
FROM system.settings
WHERE name LIKE 'format_csv_allow_%'

If it returns 1, an unquoted field that happens to start with an apostrophe, such as 's-Hertogenbosch or 'Best' plan, is read as a single-quoted string. It ends at the next apostrophe, and whatever follows is left on the line.

How to tell it’s yours: the setting is 1, and you find an apostrophe right after a delimiter or at the start of a line:

grep -nE "(^|,)'" data.csv | head

Fix: turn single-quote parsing off for the load. Values in double quotes keep working.

INSERT INTO t
SETTINGS format_csv_allow_single_quotes = 0
FORMAT CSV

Cause 5: line endings and input that stops mid-row

Carriage returns on their own

Windows \r\n endings are fine: the docs list Unix, Windows and Mac OS Classic line feeds as supported. A bare \r with no \n after it gives the related CR message, as the pull request shows. cat -A shows these lines ending in ^M with no $ straight after. You can convert the file:

tr '\r' '\n' < data.csv > data_lf.csv

Or allow them with input_format_csv_allow_cr_end_of_line, which defaults to 0.

The stream was cut

If the data stops partway through a row, the last row is broken. This can happen when a pipe through a container resets, when an upload is partial, or when a file is still being written. A connection reset next to the error, or an error that only appears on the full file and never on a sample, points here.

1. Look at the end of the file: tail -c 300 data.csv | cat -A. A final line with no $, or one that stops mid-value, means the file itself is truncated.
2. Compare wc -l data.csv with SELECT count() FROM t before you retry, so you don’t load the start of the file twice.
3. Load from a file the client can read directly, and skip the pipe. The CSV guide shows INSERT INTO sometable FROM INFILE 'data_small.csv' FORMAT CSV, and it reads compressed files with a COMPRESSION 'gzip' clause, which shrinks what goes over the wire.
4. For very large files, split them (split -l 1000000 data.csv part_) and load the parts one by one. A failure then only costs you one part.

Stopping bad CSV before it reaches ClickHouse

• Fix quoting where the file is made. Quote every string field and double any quote inside it. That alone prevents causes 1, 2 and 4.
• Send a header and let ClickHouse check it. With CSVWithNames, input_format_with_names_use_header (default 1) matches columns by name. With CSVWithNamesAndTypes, input_format_with_types_use_header checks the types too. From version 23.1, the plain CSV format detects headers on its own, per the CSV guide.
• Run a pre-flight parse. In the job that produces the file, run the clickhouse local query from the first section with the target table’s structure, and fail the job if it errors. That catches a broken file before it reaches the server.
• Give batch loads a small, visible error budget. If a few bad rows are acceptable, set input_format_allow_errors_num and input_format_allow_errors_ratio to low values together with input_format_record_errors_file_path. Alert whenever the errors file is not empty, so skipped rows get noticed.
• Choose a format with no quoting. When both ends are under your control, as with a Spark job writing for ClickHouse, a binary columnar format such as Parquet avoids text quoting issues. The CSV guide links to the Parquet and Native format guides.
• Log the full error from load jobs. Keep the whole diagnostic block. The row number and the per-column parsed text are what turn a one-line error into a five-minute fix.

See Polylane as your AI SRE.