读取xls，将所有日期转换为正确的格式， - >写入csv [英] read xls, convert all dates into proper format, -> write to csv

问题描述

我正在读取excel文件并将其作为csv写出。几个列包含格式化为excel中的浮点数的日期。在写入CSV之前，所有这些字段都需要转换为正确的日期时间（dd / mm / YY）。
我发现了一些关于如何工作的一般好文章，但努力得到工作在一个打开的工作表中的所有行。 （Newbie in Python）

I'm reading excel files and writing them out as csv. A couple of columns contain dates which are formatted as float number in excel. All those fields need to get converted to a proper datetime (dd/mm/YY) before I wrote to CSV. I found some good articles on how that works in general, but struggling to get that working for all rows in a opened sheet at once. (Newbie in Python)

现在代码如下：

wb = xlrd.open_workbook(args.inname)
    xl_sheet = wb.sheet_by_index(0)
    print args.inname
    print ('Retrieved worksheet: %s' % xl_sheet.name)
    print outname

    # TODO: Convert xldate.datetime from the date fileds to propper datetime

    output = open(outname, 'wb')
    wr = csv.writer(output, quoting=csv.QUOTE_ALL)

    for rownum in xrange(wb.sheet_by_index(0).nrows):
        wr.writerow(wb.sheet_by_index(0).row_values(rownum))

    output.close()

<确保我必须改变for rownum ....行，但我很努力做。我尝试了几个选项，都失败了。

I'm sure i have to change the "for rownum ...." line but I'm struggling doing it. I tried several options, which all failed.

感谢

推荐答案

您需要先通过该行，然后再将其写入文件，转换值。您是正确的，以确定它靠近 rownum 行：

You need to go through the row before you write it out to file, converting values. You are right to identify that it is near the for rownum line:

# You need to know which columns are dates before hand
# you can't get this from the "type" of the cell as they 
# are just like any other number

date_cols = [5,16,23]

... # Your existing setup code here #

# write the header row (in response to OP comment)
headerrow = wb.sheet_by_index(0).row_values(0)
wr.writerow(headerrow)

# convert and write the data rows (note range now starts from 1, not 0)
for rownum in xrange(1,wb.sheet_by_index(0).nrows):
    # Get the cell values and then convert the relevant ones before writing
    cell_values = wb.sheet_by_index(0).row_values(rownum)
    for col in date_cols:
        cell_values[col] = excel_time_to_string(cell_values[col])

    wr.writerow(cell_values)

正是你在 excel_time_to_string（）给你 - @MarkRansom的答案有一个合理的方法 - 或者你可以使用 xlrd 自己的包版本。

Exactly what you put in your excel_time_to_string() function is up to you - the answer by @MarkRansom has a reasonable approach - or you could use the xlrd own package versions outlined in this answer.

instance：

def excel_time_to_string(xltimeinput):
    return str(xlrd.xldate.xldate_as_datetime(xltimeinput, wb.datemode))

* EDIT *

响应在尝试后在评论中请求帮助。这是一个更加防错的版本的 excel_time_to_string（）

In response to request for help in comments after trying. Here's a more error-proof version of excel_time_to_string()

def excel_time_to_string(xltimeinput):
    try:
        retVal = xlrd.xldate.xldate_as_datetime(xltimeinput, wb.datemode)
    except ValueError:
        print('You passed in an argument in that can not be translated to a datetime.')
        print('Will return original value and carry on')
        retVal = xltimeinput

    return retVal

这篇关于读取xls，将所有日期转换为正确的格式， - &gt;写入csv的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！