Replace "" (empty string) to null using Spark dataframe.write method while writing to csv

Question

While writing a spark dataframe using write method to a csv file, the csv file is getting populated as "" for null strings

101|abc|""|555
102|""|xyz|743

Using the below code:

dataFrame
  .coalesce(16)
  .write
  .format("csv")
  .option("delimiter", "|")
  .option("treatEmptyValuesAsNulls", "true")
  .option("nullValue", null)
  .option("emptyValue", null)
  .mode(SaveMode.Overwrite)
  .save(path)

Expected output:

101|abc|null|555
102|null|xyz|743

Spark version 3.2 and Scala version 2.1

if you want null to be printed out as "null", then you need to map all null values to the string "null" somewhere (maybe in .option?) — invzbl3
– invzbl3, Commented Nov 1, 2022 at 8:53
There's only strings in CSV format, there's no such thing as null. The value for nullValue should be a string. — Gaël J
– Gaël J, Commented Nov 1, 2022 at 9:02
You can drop everything except option("nullValue", null). So, what happens when you replace this with option("nullValue", "null")? — invzbl3
– invzbl3, Commented Nov 1, 2022 at 11:06

Pritish · Accepted Answer · 2022-11-01 07:35:29Z

2

The issue seems to be in the option definition; the option values should be specified as String "null" instead of null, like:

dataFrame.coalesce(16).write.format("csv")
.option("delimiter", "|")
.option("treatEmptyValuesAsNulls", "true")
.option("nullValue", "null")
.option("emptyValue", "null")
.mode(SaveMode.Overwrite).save(path)

answered Nov 1, 2022 at 7:35

Pritish

5914 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Ikshwaku Baruah Over a year ago

In that case if I query my table like "Select * from mytable where col1 is null" will return no results.

Gaël J Over a year ago

What table? You're writing a CSV and CSV only has string types, null doesn't exist in CSV.

botchniaque Over a year ago

What is the definitin (DLL) of mytable? What is the .csv file's contents after applying @Pritish's advice?

Ikshwaku Baruah Over a year ago

Instead of "null", "\u0000" can be used. It solved my problem

Ikshwaku Baruah · Accepted Answer · 2022-11-01 09:39:37Z

0

dataFrame.coalesce(16).write.format("csv")
.option("delimiter", "|")
.option("treatEmptyValuesAsNulls", "true")
.option("nullValue", "\u0000")
.option("emptyValue", "\u0000")
.mode(SaveMode.Overwrite).save(path)

This solved the issue.

answered Nov 1, 2022 at 9:39

Ikshwaku Baruah

114 bronze badges

Collectives™ on Stack Overflow

Replace "" (empty string) to null using Spark dataframe.write method while writing to csv

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related