将Excel转换为CSV-正确转换日期字段 [英] Convert Excel to CSV - Properly Convert Date Fields

问题描述

因此，我现在可以使用Excel2CSV函数，但是遇到另一个问题，其中在Excel文件中将日期字段("Date Opened"(日期打开)，"Date Closed"(日期关闭))格式化为Excel中的日期"，并将其写入整数值.转换为CSV时 (例如1995年5月1日转换为34820).

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).

我希望将这些日期写成纯文本格式(例如1995年5月1日-或-1995年5月1日，或者类似的东西.人类可以理解的东西.)

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.

推荐答案

要将日期数字转换为Python datetime，请使用以下公式:

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

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

完成后，可以使用str(dt)将其转换为默认格式，或者使用dt.strftime(format)指定自己的格式.请参见 datetime文档

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

这篇关于将Excel转换为CSV-正确转换日期字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！