topic Reading excel file using pandas on spark api not rendering #N/A values correctly in Data Engineering

Reading excel file using pandas on spark api not rendering #N/A values correctly

aa_204 — Fri, 28 Jul 2023 12:38:24 GMT

I am trying to read a .xlsx file using ps.read_excel() and having #N/A as a value for string type columns. But in the dataframe, i am getting "null" inplace of #N/A . Is there any option , using which we can read #N/A as a string in .xlsx file

Re: Reading excel file using pandas on spark api not rendering #N/A values correctly

daniel_sahal — Mon, 31 Jul 2023 05:09:31 GMT

@aa_204
Take a look at na_values parameter:

https://pandas.pydata.org/docs/reference/api/pandas.read_excel.html

Re: Reading excel file using pandas on spark api not rendering #N/A values correctly

aa_204 — Mon, 31 Jul 2023 09:31:54 GMT

Thanks @daniel_sahal . I have tried

na_Filter=False and na_values=['NA'],keep_default_na=False as well. But still not able to manipulate #N/A as string . . Each time it is getting converted to null or nan

Re: Reading excel file using pandas on spark api not rendering #N/A values correctly

vishwanath_1 — Tue, 23 Jan 2024 10:54:18 GMT

i am facing the same issue currently even after setting

keep_default_na = False still #N/A is being converted as null

does anyone know the solution here?

Re: Reading excel file using pandas on spark api not rendering #N/A values correctly

Soumik — Mon, 03 Mar 2025 08:51:39 GMT

Did you get a solution or workaround for this error, as I am also facing the same even after using dtype = str, na_filter= False, keep_default_na = False ?