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

Date field getting changed when reading from excel file to dataframe

sreedata — Thu, 31 Mar 2022 13:47:42 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/2022.

In dataframe it is 1/24/22

Code used:

from pyspark.sql.functions import *

import pyspark.sql.functions as sf

import pyspark.sql.types

import pandas as pd

import os

import glob

filenames = glob.glob(PathSource + "/*.xls")

dfs = []

for df in dfs:

xl_file = pd.ExcelFile(filenames)

df=xl_file.parse('Sheet1')

dfs.concat(df, ignore_index=True)

display(df)

Thanks in Advance for any help or guidance.

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

merca — Sat, 02 Apr 2022 17:31:28 GMT

@srikanth nair , Have you checked the output in pandas and eventually pass the parse_dates=False to ignore dates. Pandas uses dateutil.parser.parser as default

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

Anonymous — Thu, 19 May 2022 06:16:10 GMT

Hi @sreedata (Customer) , Just a friendly follow-up. Do you still need help, or @merca (Customer) 's response help you to find the solution? Please let us know.

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

sreedata — Thu, 19 May 2022 13:45:57 GMT

working fine now thanks

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

Pradeep_Namani — Thu, 17 Nov 2022 14:56:19 GMT

Hi Team, @Merca Ovnerud

I am also facing same issue , below is the code snippet which I am using

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

I have a couple of date columns , all are showing dd/mm/yy format but it has to come as dd/mm/yyyy format

source file has: 26-03-1950

Dataframe has : 26-03-50

I have used parse_dates=False but it is not working, Can any one help on this