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

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

sarvesh — Wed, 17 Nov 2021 15:00:21 GMT

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.

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

Hubert-Dudek — Wed, 17 Nov 2021 15:26:29 GMT

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")

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

jose_gonzalez — Wed, 17 Nov 2021 18:59:01 GMT

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

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

-werners- — Thu, 18 Nov 2021 08:54:58 GMT

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.

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

sarvesh — Fri, 19 Nov 2021 04:20:05 GMT

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

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

sarvesh — Fri, 19 Nov 2021 04:21:24 GMT

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.

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

sarvesh — Fri, 19 Nov 2021 04:22:22 GMT

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

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

sarvesh — Fri, 19 Nov 2021 04:35:25 GMT

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

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

Anonymous — Fri, 19 Nov 2021 16:52:28 GMT

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

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

sarvesh — Mon, 22 Nov 2021 08:56:46 GMT

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