Clickhouse: Allow to use CRLF while formatting CSV (ex: CSV error is not helpful)

[vadimtk]

Loading broken CSV I am getting

cat foobar.csv | clickhouse-client --query="INSERT INTO foobar FORMAT CSV"
Code: 117. DB::Exception: Expected end of line

It would be helpful to see line number and what was received instead of end of line.

[Nina07]

I got the same error for my CSV file. Tried checking from online tool for CSV formatting issue, CSV Lint .
I got this error while I was trying to format my CSV to add single quotes to string data using LibreOffice Calc ( Ubuntu ) and the reason I got was ;

**Structural problem: Non-standard Line Breaks on row 1

Your CSV appears to use LF line-breaks. While this will be fine in most cases, RFC 4180 specifies that CSV files should use CR-LF (a carriage-return and line-feed pair, e.g. rn). This may be labelled as "Windows line endings" on some systems.**

LibreOffice is messing up my CSV structure.

[filimonov]

"Expected end of line" usually means that Clickhouse expects less columns that appears in CSV file.

So for example your table / or your input statement expect 3 columns, but your CSV have 4.

[Jackge1979]

The columns in your CSV file contain "," and you can include the column with "". For example: "abc", "123", "a, b, c"

[alexey-milovidov]

Now we have more verbose error messages:

$ clickhouse-local -S 'x String, y String, z String' --query="SELECT * FROM table" --input-format=CSV <<< "a,b,c"
a       b       c
$ clickhouse-local -S 'x String, y String, z String' --query="SELECT * FROM table" --input-format=CSV <<< "a,b,"
a       b
$ clickhouse-local -S 'x String, y String, z String' --query="SELECT * FROM table" --input-format=CSV <<< "a,b"
Code: 27, e.displayText() = DB::Exception: Cannot parse input: expected , before: \n: (at row 1)

Row 1:
Column 0,   name: x, type: String, parsed text: "a"
Column 1,   name: y, type: String, parsed text: "b"
ERROR: Line feed found where delimiter (,) is expected. It's like your file has less columns than expected.
And if your file have right number of columns, maybe it have unescaped quotes in values.

, e.what() = DB::Exception
$ clickhouse-local -S 'x String, y String, z String' --query="SELECT * FROM table" --input-format=CSV <<< "a,"
Code: 27, e.displayText() = DB::Exception: Cannot parse input: expected , before: \n: (at row 1)

Row 1:
Column 0,   name: x, type: String, parsed text: "a"
Column 1,   name: y, type: String, parsed text: <EMPTY>
ERROR: Line feed found where delimiter (,) is expected. It's like your file has less columns than expected.
And if your file have right number of columns, maybe it have unescaped quotes in values.

, e.what() = DB::Exception
$ clickhouse-local -S 'x String, y String, z String' --query="SELECT * FROM table" --input-format=CSV <<< "a"
Code: 27, e.displayText() = DB::Exception: Cannot parse input: expected , before: \n: (at row 1)

Row 1:
Column 0,   name: x, type: String, parsed text: "a"
ERROR: Line feed found where delimiter (,) is expected. It's like your file has less columns than expected.
And if your file have right number of columns, maybe it have unescaped quotes in values.

, e.what() = DB::Exception
$ clickhouse-local -S 'x String, y String, z String' --query="SELECT * FROM table" --input-format=CSV <<< ""
Code: 27, e.displayText() = DB::Exception: Cannot parse input: expected , before: \n: (at row 1)

Row 1:
Column 0,   name: x, type: String, parsed text: <EMPTY>
ERROR: Line feed found where delimiter (,) is expected. It's like your file has less columns than expected.
And if your file have right number of columns, maybe it have unescaped quotes in values.

, e.what() = DB::Exception
$ clickhouse-local -S 'x String, y String, z String' --query="SELECT * FROM table" --input-format=CSV <<< "a,b,c,d"
Code: 117, e.displayText() = DB::Exception: Expected end of line: (at row 1)

Row 1:
Column 0,   name: x, type: String, parsed text: "a"
Column 1,   name: y, type: String, parsed text: "b"
Column 2,   name: z, type: String, parsed text: "c"
ERROR: There is no line feed. "d" found instead.
 It's like your file has more columns than expected.
And if your file have right number of columns, maybe it have unquoted string value with comma.

, e.what() = DB::Exception

[alexey-milovidov]

CSV files should use CR-LF

@Nina07 We can add an option to format CSV with CRLF instead of LF.
This is quite easy task.

[millb]

As I understand this task has already been solved.

[alexey-milovidov]

No.

[alexey-milovidov]

I should have an option to format data like that:

1thellorn
2tworldrn

instead of

1thellon
2tworldn