Getting Null values at the place of data which was removed manually from excel file( solved )

[sarvesh]

I was reading an excel file with one column,

country

india

India

india

India

india

dataframe i got from this data : df.show()

+-------+ |country| +-------+ |

india |

| India |

| india |

| India |

| india | +-------+

In the next step i removed last value from the excel file manually by backspace and saved the file,

file now :

country

india

India

india

India

now when i run the same df.show(), this is what i get:

+-------+ |country| +-------+ |

india |

|India |

|india |

|India | |

null | +-------+

if i have removed the value why do i get a null value at its place?

and my code if someone needs it,

val spark = SparkSession

.builder

.appName("schemaTest")

.master("local[*]")

.getOrCreate()

val df = spark.read

.format("com.crealytics.spark.excel").

option("header", "false").

option("inferSchema", "true").

option("treatEmptyValuesAsNulls", "false").

option("addColorColumns", "False").

load("data/trimTest2.xlsx")

df.show()

edit : when i was changing some value in my excel file, i was using backspace instead of delete row in excel, which made excel thing that there is a row which is blank,

but if you use delete row then excel deletes the complete row and spark does not read anything there.

[Hubert-Dudek]

I think problem is on excel side. Personally when I got anything from excel I prefer to use tables in excel to control what I am getting. Alternatively just specify range:

.option("dataAddress", "'My Sheet'!B3:C35")

[sarvesh]

yes the problem is excel, i think there is no sure way other then reading it manually might go with it.

[jose_gonzalez]

hi @sarvesh singh​ ,

Have you try to use Koalas to read your Excel data instead of using this 3rd party library? You should be able to find many examples online on how to use Koalas to read Excel files. You can check Koalas's APIs here

[sarvesh]

Hi , well i work with scala and I tried but couldn't find koalas for scala.

[-werners-]

Excel internally still thinks the cell exists but with no content, hence the null.

Jose's and Hubert's answers are both viable options. Or use a text format (csv f.e.) which you can open in an ordinary text editor if that is an option of course.

I try to avoid receiving excel files, do not like it at all.

[sarvesh]

I have to handle an excel file in m project, i understand where this problem is coming from but reading a sheet manually is not a dynamic solution for me.

[sarvesh · Accepted solution]

I got the issue, i was using backspace to delete a value instead of delete rows command in excel.

[Anonymous]

@sarvesh singh​ - Thank you for letting us know. Would you be happy to mark the best answer so others can find the solution easily?

[sarvesh]

well i figured it out on my own, that is why i mentioned it my comment has the solution for anyone who needs it.