CODEWITHSUNDEEP
pythonexcelcsv
user2916886
user2916886

Reputation: 845

wrong output in converting date format data from an excel sheet to csv file in python

I have this excel sheet and I am trying to convert this excel sheet to csv file. Among the columns in this sheet is a column with data in date format(like 7/4/2017). I wrote this code but this is not converting the date field data correctly:

import xlrd
import csv

def Excel2CSV(ExcelFile, SheetName, CSVFile):

    workbook = xlrd.open_workbook(ExcelFile)
    worksheet = workbook.sheet_by_name(SheetName)
    csvfile = open(CSVFile, 'w',encoding='utf8')
    wr = csv.writer(csvfile,delimiter=';')

    for rownum in range(worksheet.nrows):
        wr.writerow(worksheet.row_values(rownum))

    csvfile.close()

My sample data in excel is like this:

4/7/2017  value02  value03
4/5/2017  value12  value13
4/14/2017  value22  value23
4/10/2017  value32  value33

When I execute my above code this is what see in output:

42832.0;value02;value03
42830.0;value12;value13
42839.0;value22;value23
42835.0;value32;value33

As you can see that the date filed data is not getting converted correctly. What mistake I am making here?

Upvotes: 1

Views: 189

Answers (1)

Scott
Scott

Reputation: 26

Assuming you are using the XLRD package for reading the file you can find the answer at http://xlrd.readthedocs.io/en/latest/dates.html

Basically dates are stored as 'number of days since.....' and just formatted to appear as dates when viewed in Excel.

There are more details here http://xlrd.readthedocs.io/en/latest/api.html#module-xlrd.xldate

'xldate_as_tuple' is the function you want

Upvotes: 1

Related Questions