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

> ClickHouse Code 117 Expected end of line means the CSV parser found extra text after a row's last column. Find the bad row, then fix quotes, columns or types.

By Boris Tane, Founder of Polylane · Published October 17, 2026 · 11 min read
Canonical: https://polylane.com/learn/troubleshooting/clickhouse-code-117-db-exception-expected-end-of-line/

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

ClickHouse's CSV parser read every column it expected for a row, then found more text where it wanted a line break, so it threw INCORRECT_DATA (code 117). The usual fix is to repair the row. Rewrite backslash-escaped quotes as doubled quotes, quote any value that contains the delimiter, and make sure the last column's value fits its type.

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](https://github.com/ClickHouse/ClickHouse/pull/54340) 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](https://clickhouse.com/docs/interfaces/formats/CSV) 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.

```mermaid alt="Decision path from a ClickHouse Code 117 error to the cause and fix for the broken CSV row"
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:

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

In [GNU `cat`](https://www.gnu.org/software/coreutils/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:

```bash
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`](https://clickhouse.com/docs/reference/settings/formats/input-format#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`](https://clickhouse.com/docs/reference/settings/formats/input-format#input_format_record_errors_file_path) names a file that records the errors.

```bash
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:

```text
"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.

```bash
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:

```bash
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:

```bash
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`](https://clickhouse.com/docs/interfaces/formats/CSV):

```bash
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`](https://clickhouse.com/docs/reference/settings/formats/input-format#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:

```sql
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:

```sql
DESCRIBE TABLE t
```

Also check how the file writes nulls. ClickHouse reads `\N` as null by default, per [`format_csv_null_representation`](https://clickhouse.com/docs/integrations/data-formats/csv-tsv). 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:

```sql
SET format_csv_null_representation = 'Nothing'
```

3. If losing the bad value is acceptable, [`input_format_csv_use_default_on_bad_values`](https://clickhouse.com/docs/reference/settings/formats/input-format#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](https://github.com/ClickHouse/ClickHouse/issues/66772) reports that line is wrong and the real default is false. So start by checking the setting on your server:

```sql
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:

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

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

```sql
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](https://github.com/ClickHouse/ClickHouse/pull/54340) shows. `cat -A` shows these lines ending in `^M` with no `$` straight after. You can convert the file:

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

Or allow them with [`input_format_csv_allow_cr_end_of_line`](https://clickhouse.com/docs/reference/settings/formats/input-format#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](https://clickhouse.com/docs/integrations/data-formats/csv-tsv) 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`](https://clickhouse.com/docs/reference/settings/formats/input-format#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](https://polylane.com/use-cases/ai-sre/).

## Common questions

**What does error code 117 mean in ClickHouse?**

Code 117 is INCORRECT_DATA. With the message Expected end of line, it comes from the CSV row parser. After reading the last column of a row, the parser found something other than \n, \r\n or the end of the stream. The row has text left over after the field ClickHouse thinks is last.

**Can ClickHouse read CSV where quotes are escaped with a backslash?**

Not in the CSV format. The ClickHouse docs say a double quote inside a string is written as two double quotes, and there are no other escaping rules. Either set the writer to double its quotes, or rewrite the file on the way in with sed 's/\\"/""/g' before piping it to clickhouse-client.

**How do I find which line of a large CSV is broken?**

Read the diagnostic block ClickHouse prints after the error, which shows each column's parsed text. Print the line with sed -n 'Np' data.csv | cat -A to see hidden characters. To list every bad row at once, parse the file with clickhouse local and file(), with input_format_allow_errors_num, input_format_allow_errors_ratio and input_format_record_errors_file_path set.

**How do I skip a few bad rows and still load the rest of the file?**

Set input_format_allow_errors_num and input_format_allow_errors_ratio together. Both default to 0, and ClickHouse throws only when both limits are exceeded. Add input_format_record_errors_file_path so the skipped rows are written down, and alert on that file, because skipped rows are lost data.

**My CSV has more columns than my table. How do I load it?**

Enable input_format_csv_allow_variable_number_of_columns, which defaults to 0. ClickHouse then ignores extra columns and fills missing ones with defaults. Check first that the extra fields are real columns and not values split by an unquoted comma, because those rows would load with shifted data and no error.

**Does ClickHouse accept Windows line endings in CSV?**

Yes. The CSV docs list Unix LF, Windows CR LF and Mac OS Classic line endings as supported. A bare carriage return with no line feed after it fails with a related message. Convert those with tr '\r' '\n' or set input_format_csv_allow_cr_end_of_line = 1.

**Why does a value starting with an apostrophe break my CSV import?**

Only when format_csv_allow_single_quotes is set to 1. The CSV docs list it as on by default, but a ClickHouse issue reports the real default is false, so check system.settings first. With it on, a field like 's-Hertogenbosch is read as a quoted string up to the next apostrophe and the rest of the line is left over. Add SETTINGS format_csv_allow_single_quotes = 0 to the insert.

## Sources

- [CSV format · ClickHouse docs](https://clickhouse.com/docs/interfaces/formats/CSV)
- [input_format_* format settings · ClickHouse docs](https://clickhouse.com/docs/reference/settings/formats/input-format)
- [Working with CSV and TSV data in ClickHouse · ClickHouse docs](https://clickhouse.com/docs/integrations/data-formats/csv-tsv)
- [Fix CSV read while the end of line is CR · ClickHouse pull request 54340](https://github.com/ClickHouse/ClickHouse/pull/54340)
- [Incorrect describe csv format · ClickHouse issue 66772](https://github.com/ClickHouse/ClickHouse/issues/66772)
- [cat invocation · GNU Coreutils](https://www.gnu.org/software/coreutils/cat)

## About the author

Boris Tane is the founder of Polylane. He previously founded Baselime, observability for the future of the cloud, which Cloudflare acquired. At Cloudflare he built and led the Workers observability team.

## Related

- [ClickHouse native TCP client for .NET: setup and migration](https://polylane.com/learn/observability/clickhouse-native-tcp-client-for-net/): ClickHouse's new native TCP client for .NET, released 16 October 2026: what it is, when to pick it over HTTP, setup on port 9000, and the gotchas to plan for.
- [Fix 403 Forbidden (Access Denied) from Amazon S3](https://polylane.com/learn/troubleshooting/aws-403-forbidden/): Why Amazon S3 returns 403 Forbidden: Block Public Access, missing s3:ListBucket, object ownership, clock skew, expired presigned URLs and CloudFront. Fix each.
- [Fix 502 Bad Gateway on AWS API Gateway and Load Balancers](https://polylane.com/learn/troubleshooting/aws-502-bad-gateway/): Why AWS returns 502 Bad Gateway from API Gateway or an Application Load Balancer, how to find which hop wrote it, and the fix for each cause.
- [Fix AuthorizerConfigurationException in API Gateway](https://polylane.com/learn/troubleshooting/aws-authorizerconfigurationexception/): AuthorizerConfigurationException means API Gateway couldn't call your Lambda authorizer or couldn't read its response. Find the cause and fix it.
- [Fix Cloudflare Error 1037: Invalid rewrite rule](https://polylane.com/learn/troubleshooting/cloudflare-error-1037-invalid-rewrite-rule-failed-to/): Cloudflare Error 1037 means a rewrite expression failed to evaluate, often because a header or query argument is missing. How to find the rule and fix it.

Get started with one command: `curl -fsSL https://polylane.com/setup | bash` installs the CLI, connects your coding agents, and creates the account.