sql - How to avoid read_csv converting empty strings to NULL? - Stack Overflow

admin管理员组

文章数量:1349231

I'm using DuckDB to read a CSV file that contains empty strings in some fields. However, DuckDB converts these empty strings to NULL.

CSV Sample Data:

"path","comment"
"","[{""comment"":""grand total"",""updatedByUser"":"",""dateTime"":""2025-03-27 09:08:38"",""threadStatus"":null,""threadAssignee"":null}]"
"East","[{""comment"":""Sub total"",""updatedByUser"":"",""dateTime"":""2025-03-27 09:09:03"",""threadStatus"":null,""threadAssignee"":null}]"

Query I'm Using:

SELECT * FROM read_csv('${path}', delim=',', header=True, columns={"path":"VARCHAR","comment":"VARCHAR"});

I want empty strings ("") in the path column to remain empty strings, but they are instead being converted to NULLs.

How can I have these values remain empty strings?

I'm using DuckDB to read a CSV file that contains empty strings in some fields. However, DuckDB converts these empty strings to NULL.

CSV Sample Data:

"path","comment"
"","[{""comment"":""grand total"",""updatedByUser"":"",""dateTime"":""2025-03-27 09:08:38"",""threadStatus"":null,""threadAssignee"":null}]"
"East","[{""comment"":""Sub total"",""updatedByUser"":"",""dateTime"":""2025-03-27 09:09:03"",""threadStatus"":null,""threadAssignee"":null}]"

Query I'm Using:

SELECT * FROM read_csv('${path}', delim=',', header=True, columns={"path":"VARCHAR","comment":"VARCHAR"});

I want empty strings ("") in the path column to remain empty strings, but they are instead being converted to NULLs.

How can I have these values remain empty strings?

• sql
• data-analysis
• duckdb

Share Improve this question Follow edited Apr 2 at 9:33 AD7six 66.5k1414 gold badges110110 silver badges156156 bronze badges asked Apr 2 at 5:22 Ms.anonymousMs.anonymous 1322 bronze badges New contributor Ms.anonymous is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct. 8

• updatedByUser has a blank string. What's different about that field? – Tim Roberts Commented Apr 2 at 5:29
• 1 Yeah i just wanted to confirm if this is an expected behavior. Using force_not_null works. – Ms.anonymous Commented Apr 2 at 6:23
• But why do you want empty strings? What does an empty string represent? – jarlh Commented Apr 2 at 6:30
• In my dataset, each row has a 'path' value that we use to join two tables. However, the grand total row represents an aggregate of all data and does not have a specific 'path'. NULL, which would prevent it from matching in a join – Ms.anonymous Commented Apr 2 at 6:37
• 1 Correct your query instead of saving empty strings. – jarlh Commented Apr 2 at 6:40

 |  Show 3 more comments

2 Answers 2

Sorted by: Reset to default 1

By default, DuckDB will treat empty strings as NULLs upon import. This behavior can be modified using force_not_null:

Do not match values in the specified columns against the NULL string. In the default case where the NULL string is empty, this means that empty values are read as zero-length strings instead of NULLs.

You could use COALESCE to change the NULL values.

select 
       coalesce(path, '') as path
      ,comment 
from  read_csv('${path}')

本文标签： sqlHow to avoid readcsv converting empty strings to NULLStack Overflow