topic Date field getting changed when reading from excel file to dataframe in pyspark in Data Engineering

Date field getting changed when reading from excel file to dataframe in pyspark

Pradeep_Namani — Thu, 24 Nov 2022 06:40:57 GMT

The date field is getting changed while reading data from source .xls file to the dataframe. In the source xl file all columns are strings but i am not sure why date column alone behaves differently

In Source file date is 1/24/1947.

In pyspark dataframe it is 1/24/47

Code used:

df=spark.read.format("com.crealytics.spark.excel").option("header","true").load("/mnt/dataplatform/Tenant_PK/Results.xlsx")

If I use option("inforscheme","true") the data coming properly , but I dont want use inforschema, Can any one suggest me any solution.

Thanks in advance

Re: Date field getting changed when reading from excel file to dataframe in pyspark

yogu — Thu, 24 Nov 2022 06:52:24 GMT

hi @Pradeep Namani ,

could you plz try to run below one. I hope so it will work without inferschema

df=spark.read.format("csv").option("header","true").load("/mnt/dataplatform/Tenant_PK/Results.xlsx")

Re: Date field getting changed when reading from excel file to dataframe in pyspark

Pradeep_Namani — Thu, 24 Nov 2022 07:12:54 GMT

Thank you @Yogita Chavan for replying , but when I am reading file as csv it is showing all data in different format, I am attaching the screen shot

Re: Date field getting changed when reading from excel file to dataframe in pyspark

yogu — Thu, 24 Nov 2022 07:13:17 GMT

also u can refer below one

https://mayur-saparia7.medium.com/reading-excel-file-in-pyspark-databricks-notebook-c75a63181548

Re: Date field getting changed when reading from excel file to dataframe in pyspark

-werners- — Thu, 24 Nov 2022 10:37:36 GMT

how about using inferschema one single time to create a correct DF, then create a schema from the df-schema.

something like this f.e.

from pyspark.sql.types import StructType    
 
# Save schema from the original DataFrame into json:
schema_json = df.schema.json()
 
# Restore schema from json:
import json
new_schema = StructType.fromJson(json.loads(schema_json))

Re: Date field getting changed when reading from excel file to dataframe in pyspark

Pradeep_Namani — Thu, 24 Nov 2022 11:08:37 GMT

I have tried the option which we have give in above url but no use, still I am facing same issue