Convert Excel to CSV - Properly Convert Date Fields

Question

So, I now have my Excel2CSV function working, but encountered another problem wherein the Date Fields in my Excel File ('Date Opened', 'Date Closed') which are formatted as Date in Excel are being written as an integer value when converted to the CSV (ex. 5/1/1995 converts to 34820).

I'd like it to just write those dates out as plain text (ie. 5/1/1995 -or- May 1, 1995, or something along those lines. Something human readable.)

def Excel2CSV(ExcelFile, SheetName, CSVFile):
     import xlrd
     import csv
     print "Acquiring " + ExcelFile + "..."
     workbook = xlrd.open_workbook(ExcelFile)
     print "Locating " + SheetName + " Worksheet..."
     worksheet = workbook.sheet_by_name(SheetName)
     print "Creating " + CSVFile + "..."
     csvfile = open(CSVFile, 'wb')
     print "Preparing to write CSV file..."
     wr = csv.writer(csvfile, quoting=csv.QUOTE_ALL)
     print "Writing CSV..."
     for rownum in xrange(worksheet.nrows):
         wr.writerow(
             list(x.encode('utf-8') if type(x) == type(u'') else x
                  for x in worksheet.row_values(rownum)))
     print "Closing CSV File..."
     csvfile.close()
     print "CSV successfully written."
     return CSVFile

I'm not sure how to capture the date fields by name or value and then convert the values to plain text properly. Any help is appreciated.

score 1 · Accepted Answer · answered Sep 25 '14 at 16:31

1

To convert from the date number to a Python datetime use the following formula:

dt = datetime.datetime(1899, 12, 30) + datetime.timedelta(days=num)

Once you've done that you can convert it to the default format with str(dt) or specify your own format with dt.strftime(format). See the datetime documentation

answered Sep 25 '14 at 16:31

Mark Ransom

299,747
42
398
622

R u sure it's always correct? [http://www.cpearson.com/excel/datetime.htm](http://www.cpearson.com/excel/datetime.htm) specifies you also need to subtract 1 day for dates after `1900-Feb-28`, at least, and there may be a fractional part as well. – ivan_pozdeev Sep 25 '14 at 16:39
@ivan_pozdeev yes that's true. If you care about dates that old you probably shouldn't be handling them in Excel though, so I didn't feel the need to point it out. – Mark Ransom Sep 25 '14 at 16:42
@ivan_pozdeev the `days` parameters handles a float so fractional parts are OK too. – Mark Ransom Sep 25 '14 at 16:44
How would I go about only doing this on values that are actually supposed to be a date though (ie. they are in one of my two date fields). There are other fields in the Excel spreadsheet that have integer values which I wouldn't want to accidentally convert to a date. – MrBubbles Sep 25 '14 at 16:45
@JohnDye: look at the [Cell.ctype](https://secure.simplistix.co.uk/svn/xlrd/trunk/xlrd/doc/xlrd.html?p=4966#sheet.Cell-class) attribute. – ivan_pozdeev Sep 25 '14 at 16:49

Convert Excel to CSV - Properly Convert Date Fields

1 Answers1

Linked