topic Re: Spark CSV file read option to read blank/empty value from file as empty value only instead Null in Data Engineering

Spark CSV file read option to read blank/empty value from file as empty value only instead Null

RakeshRakesh_De — Thu, 18 Apr 2024 09:25:08 GMT

Hi,

I am trying to read one file which having some blank value in column and we know spark convert blank value to null value during reading, how to read blank/empty value as empty value ?? tried DBR 13.2,14.3

I have tried all possible way but its not working

display(spark.read.option("emptyValue", "").csv('/FileStore/tables/test2.csv',header=True,inferSchema=True))

display(spark.read.option("emptyValue","None").csv('/FileStore/tables/test2.csv',header=True,inferSchema=True))

spark.read.option("nullValue", "None").csv('/FileStore/tables/test2.csv',header=True,inferSchema=False)

Sample file below as input csv

Re: Spark CSV file read option to read blank/empty value from file as empty value only instead Null

-werners- — Thu, 18 Apr 2024 10:06:48 GMT

May I ask why you do not want null? It is THE way to indicate a value is missing (and gives you filtering possibilities using isNull/isNotNull).

Re: Spark CSV file read option to read blank/empty value from file as empty value only instead Null

RakeshRakesh_De — Thu, 18 Apr 2024 10:48:03 GMT

Hi @-werners- , User wants data in landing table like this only, they have some data like None as well... And can have some case when statement based on blank value and null value in next layer

Re: Spark CSV file read option to read blank/empty value from file as empty value only instead Null

-werners- — Thu, 18 Apr 2024 10:51:31 GMT

.option("nullValue", "") should do the trick.

Re: Spark CSV file read option to read blank/empty value from file as empty value only instead Null

Riyakh — Thu, 18 Apr 2024 14:16:13 GMT

.option(nullValue, "")
empty strings are interpreted as null values by default. If you set nullValue to anything but "", like "null" or "none", empty strings will be read as empty strings and not as null values anymore.

Please check-
dataframe - Read spark csv with empty values without converting to null - Stack Overflow

Re: Spark CSV file read option to read blank/empty value from file as empty value only instead Null

RakeshRakesh_De — Thu, 18 Apr 2024 14:34:29 GMT

dont quote something from stackoverflow because those are old version in spark tried.. have you tried the thing on your own to verify if this really working or not in spark3??

Re: Spark CSV file read option to read blank/empty value from file as empty value only instead Null

-werners- — Thu, 18 Apr 2024 15:40:35 GMT

afaik nullValue, "" should do the trick. But I tested myself on your example and indeed it does not work.
Gonna do some checking...

Re: Spark CSV file read option to read blank/empty value from file as empty value only instead Null

-werners- — Wed, 24 Apr 2024 07:43:18 GMT

OK, after some tests:
The trick is in surrounding text in your csv with quotes. Like that spark can actually make a difference between a missing value and an empty value. Missing values are null and can only be converted to something else implicitely (by using coalesce f.e.).
When a column contains '', nullvalue = "''" will create an empty value and not null.
The same for emptyValue if you want.
Not sure if it is workable for you though.