Query results in csv file include 'null' string for blank cell
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
12-18-2023 11:31 AM
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
01-25-2024 09:46 AM
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
04-26-2024 07:42 AM
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
04-30-2024 11:03 PM
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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
01-14-2025 01:58 PM
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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
01-17-2025 03:15 AM
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.

