Newline in column names after importing a CSV

[nileshtrivedi]

I imported a large CSV file into DuckDB and then realized that many column names have newline (\n) characters. Some even have question mark (?) and apostrophe ('). Here is the output of the DESCRIBE table query (in JSON mode so you can see exact characters):

{"column_name":"Cancelled By?","column_type":"VARCHAR","null":"YES","key":null,"default":null,"extra":null},
{"column_name":"Item's total\nA","column_type":"VARCHAR","null":"YES","key":null,"default":null,"extra":null},
{"column_name":"Packing & Service charges\nB","column_type":"VARCHAR","null":"YES","key":null,"default":null,"extra":null},
{"column_name":"Merchant Discount\nC1","column_type":"VARCHAR","null":"YES","key":null,"default":null,"extra":null},
{"column_name":"Exclusive Offer\nC2","column_type":"VARCHAR","null":"YES","key":null,"default":null,"extra":null},

I am not able to refer to these columns in a SELECT query at all (using DuckDB CLI). To isolate the problem, I restricted to just one column whose name includes \n:

D select "Merchant Discount\nC1" from mytable;
Binder Error: Referenced column "Merchant Discount\nC1" not found in FROM clause!
Candidate bindings: "mytable.Merchant Discount
C1"
LINE 1: select "Merchant Discount\nC1" from mytable...
               ^
D select "Merchant Discount\\nC1" from mytable;
Binder Error: Referenced column "Merchant Discount\\nC1" not found in FROM clause!
Candidate bindings: "mytable.Merchant Discount
C1"
LINE 1: select "Merchant Discount\\nC1" from mytable...
               ^
D select "Merchant Discount\\\nC1" from mytable;
Binder Error: Referenced column "Merchant Discount\\\nC1" not found in FROM clause!
Candidate bindings: "mytable.Merchant Discount
C1"
LINE 1: select "Merchant Discount\\\nC1" from mytable..

How do I escape my query so that this column can actually be read? If this is not possible, shouldn't DuckDB have cleaned up the column names and stripped invalid characters while importing the CSV?

[nileshtrivedi]

I have confirmed that DBeaver's DuckDB driver has the same issue. While select * queries work fine, select "Merchant Discount\nC1" from table fail with the error: SQL Error: java.sql.SQLException: Binder Error: Referenced column "Merchant Discount\nC1" not found in FROM clause!. No number of escaping backslashes help with this problem.

[Tishj]

There is a normalize_names option to the CSV import which I think fixes problems like this
We don't do this automatically

[nileshtrivedi]

Thank you. This fixed it for me. 👍

[nileshtrivedi]

@Tishj Although, I do think if DuckDB successfully imported a CSV, it should be usable too.