Python xlrd使用日期转换来解析Excel xlsx到csv [英] Python xlrd parse Excel xlsx to csv with date conversion
本文介绍了Python xlrd使用日期转换来解析Excel xlsx到csv的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我试图将Excel.xlsx文件解析为csv文件。这是Excel文件:
日期人1人2
02/03/2015 Bob James A
03/03/2015 Billy Nic
04/03/2015 Sally Mark
05/03/2015 Alan James A
06/03/2015 James W James A
我的Python脚本:
import xlrd
import csv
book = xlrd.open_workbook('rota.xlsx')
sheet = book.sheet_by_name('Sheet1')
csvfile = open('output.csv','wb')
wr = csv.writer(csvfile,quoting = csv.QUOTE_ALL)
for rownum in range sheet.nrows):
wr.writerow(sheet.row_values(rownum))
csvfile.close()
但是它输出的日期如下:
Date,Person1,Person2
41884,Bob,James B
41885,Billy,Nic
41886,Sally,Mark
41887,Alan,James A
41888,James W,James A
我知道xldate_as_tuple函数或类似的东西将输出转换为有意义的值,但我不能找出如何使用它。
解决方案
这是一个可能的解决方案:
import xlrd
pre>
import csv
从datetime import datetime
book = xlrd.open_workbook ('rota.xlsx')
sheet = book.sheet_by_name('Sheet1')
csvfile = open('output5.csv','wb')
wr = csv .writer(csvfile,quoting = csv.QUOTE_ALL)
wr.writerow(sheet.row_values(0))
用于范围内的rownum(1,sheet.nrows):
年,月,日,时,分,秒= xlrd.xldate_as_tuple(int(sheet.row_values(rownum)[0]),book.datemode)
py_date = datetime(年,月,日, )
wr.writerow([py_date] + sheet.row_values(rownum)[1:])
csvfile.close()
输出:
Date ,Person 2
2015-02-03 00:00:00,Bob,James A
2015-03-03 00:00:00,Billy ,Nic
2015-04-03 00:00:00,Sally,Mark
2015-05-03 00:00:00,Alan James A
2015-06-03 00:00:00,James W,James A
版本2:
代码:
#! / usr / bin / python
import xlrd
import csv
从datetime import datetime
book = xlrd.open_workbook('rota.xlsx')
sheet = book.sheet_by_name('Sheet1')
csvfile = open('output5.csv','wb')
wr = csv.writer(csvfile,quoting = csv.QUOTE_ALL)
for rownum in range(sheet.nrows):
date = sheet.row_values(rownum)[0]
if isinstance(date,float)or isinstance(date,int ):
年,月,日,小时,分钟,秒= xlrd.xldate_as_tuple(date,book.datemode)
py_date =%02d /%02d /%04d年)
wr.writerow([py_date] + sheet.row_values(rownum)[1:])
else:
wr.writerow(sheet.row_values(rownum))
csvfile.close()
输出:
Date,Person 1,Person 2
02/03/2015,Bob,James A
03 / 03/2015,Billy,Nic
04/03/2015,Sally,Mark
05/03/2015,Alan,James A
06/03/2015,James W,James A
I am trying to parse an Excel.xlsx file to a csv file. Here is the Excel file:
Date Person 1 Person 2 02/03/2015 Bob James A 03/03/2015 Billy Nic 04/03/2015 Sally Mark 05/03/2015 Alan James A 06/03/2015 James W James A
My Python script:
import xlrd import csv book = xlrd.open_workbook('rota.xlsx') sheet = book.sheet_by_name('Sheet1') csvfile = open('output.csv', 'wb') wr = csv.writer(csvfile, quoting=csv.QUOTE_ALL) for rownum in range(sheet.nrows): wr.writerow(sheet.row_values(rownum)) csvfile.close()
however it outputs the dates like this:
Date,Person1,Person2 41884,Bob,James B 41885,Billy,Nic 41886,Sally,Mark 41887,Alan,James A 41888,James W,James A
I am aware of the xldate_as_tuple function or something like this to convert the output to meaningful values but I can't figure out how to use it. Any help I would be most grateful.
解决方案Here is one possible solution:
import xlrd import csv from datetime import datetime book = xlrd.open_workbook('rota.xlsx') sheet = book.sheet_by_name('Sheet1') csvfile = open('output5.csv', 'wb') wr = csv.writer(csvfile, quoting=csv.QUOTE_ALL) wr.writerow(sheet.row_values(0)) for rownum in range(1,sheet.nrows): year, month, day, hour, minute, sec = xlrd.xldate_as_tuple(int(sheet.row_values(rownum)[0]), book.datemode) py_date = datetime(year, month, day, hour, minute) wr.writerow([py_date] + sheet.row_values(rownum)[1:]) csvfile.close()
Output:
"Date "," Person 1","Person 2" "2015-02-03 00:00:00"," Bob ","James A " "2015-03-03 00:00:00"," Billy ","Nic " "2015-04-03 00:00:00"," Sally ","Mark " "2015-05-03 00:00:00"," Alan ","James A " "2015-06-03 00:00:00","James W ","James A "
Version2:
Code:
#! /usr/bin/python import xlrd import csv from datetime import datetime book = xlrd.open_workbook('rota.xlsx') sheet = book.sheet_by_name('Sheet1') csvfile = open('output5.csv', 'wb') wr = csv.writer(csvfile, quoting=csv.QUOTE_ALL) for rownum in range(sheet.nrows): date = sheet.row_values(rownum)[0] if isinstance( date, float) or isinstance( date, int ): year, month, day, hour, minute, sec = xlrd.xldate_as_tuple(date, book.datemode) py_date = "%02d/%02d/%04d" % (month, day,year) wr.writerow([py_date] + sheet.row_values(rownum)[1:]) else: wr.writerow(sheet.row_values(rownum)) csvfile.close()
Output:
"Date "," Person 1","Person 2" "02/03/2015"," Bob ","James A " "03/03/2015"," Billy ","Nic " "04/03/2015"," Sally ","Mark " "05/03/2015"," Alan ","James A " "06/03/2015","James W ","James A "
这篇关于Python xlrd使用日期转换来解析Excel xlsx到csv的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文