[Python] How to read empty string as well as NULL values from a csv file in pyspark?

TL;DR; -- How to use "" as empty value and nothing as NULL in a csv file?

I have a need where I need to specify an empty string in a csv file, which also has some NULL values. I'm trying to use "" as empty value and nothing as NULL, My expectation was that nullValue=None and emptyValue="" should do what I want, but both get interpreted as NULL.

I tried all combinations of nullValue and emptyValue options.

with open("/dbfs/tmp/c.csv", "w") as f:
f.write('''id,val
1,
2,""
3,str1
''')

for e, n in [('', None), ('', ''), (None, None), (None, '')]:
print(f'e: "{e}", n: "{n}"')
df = spark.read.csv('dbfs:/tmp/c.csv', header=True, emptyValue=e, nullValue=n).show()


prints:

e: "", n: "None"
+---+-----+
| id| val|
+---+-----+
| 1| NULL|
| 2| NULL|
| 3| str1|
+---+-----+

e: "", n: ""
+---+-----+
| id| val|
+---+-----+
| 1| NULL|
| 2| NULL|
| 3| str1|
+---+-----+

e: "None", n: "None"
+---+-----+
| id| val|
+---+-----+
| 1| NULL|
| 2| NULL|
| 3| str1|
+---+-----+

e: "None", n: ""
+---+-----+
| id| val|
+---+-----+
| 1| NULL|
| 2| NULL|
| 3| str1|
+---+-----+


PS: It works in scala, just not in python. So I'm guessing it might have something to do with the fact that print("true" if "" else "false") prints "false" in python.

spark.read
.option("header", "true")
.option("emptyValue", "")
.option("nullValue", null)
.csv("dbfs:/tmp/c.csv").show()


prints:

+---+-----+
| id| val|
+---+-----+
| 1| NULL|
| 2| |
| 3| str1|
+---+-----+


I've read:

• spark.read. reading empty string as null when data is read from part file
• Read spark csv with empty values without converting to null
• Spark CSV file read option to read blank/empty value from file as empty value only instead Null
• CSV Files

Continue reading...