topic Re: Query results in csv file include 'null' string for blank cell in Get Started Discussions

Query results in csv file include 'null' string for blank cell

AlexG — Mon, 18 Dec 2023 19:31:46 GMT

After running a sql script, when downloading the results to a csv file, the file includes a null string for blank cells (see screenshot). Is ther a setting I can change to simply get empty cells instead?

Re: Query results in csv file include 'null' string for blank cell

jose_gonzalez — Thu, 25 Jan 2024 17:46:59 GMT

You need to use "emptyValue" when writing you data

df.write.csv(PATH, header=True, emptyValue='')

Here is a list of APIs references https://spark.apache.org/docs/latest/sql-data-sources-csv.html

Re: Query results in csv file include 'null' string for blank cell

AlexG — Fri, 26 Apr 2024 14:42:16 GMT

Thank you. How would you download to csv the results from a SQL script without the null strings in empty cells?

I mainly work with SQL notebooks

Re: Query results in csv file include 'null' string for blank cell

NandiniN — Wed, 01 May 2024 06:03:40 GMT

Hi AlexG,

I tested with the table content containing null and with empty data and it works as expected in the download option too.

Here is an eg:

CREATE TABLE my_table_null_test1 ( id INT, name STRING ); INSERT INTO my_table_null_test1 (id, name) VALUES (1, 'John Doe'), (2, NULL), (3, 'Jane Doe'), (NULL, 'Anonymous') (4, '');

If you do not want nulls, you may have to modify the query to use

coalesce(NULL, '') or

nvl(NULL, '');

Hope it helps. Thanks!

Re: Query results in csv file include 'null' string for blank cell

Gabriel2 — Tue, 14 Jan 2025 21:58:55 GMT

Also running into this, for fields with date datatype

NULL values end up with "null" strings in the downloaded csv file along with actual dates. This causes import problems when trying to load into other systems and the field is defined as date.

only solution is to cast null to a default date value like - '1900-01-01' in the query but this is not ideal. Would love a better built-in solution!

Re: Query results in csv file include 'null' string for blank cell

NandiniN — Fri, 17 Jan 2025 11:15:41 GMT

I understand, however this is more on CSV file format.

Save your data in Delta format instead of CSV or text-based formats. Delta tables handle empty strings and NULL values more effectively, ensuring that empty strings are preserved during data insertion.

https://kb.databricks.com/en_US/data/empty-string-values-convert-to-null-values-when-saving-a-table-as-csv-or-text-based-file-format