帮一个中科院小学妹写的一个小脚本,主要是利用python中的pandas,xlrd,dbfpy包将excel中的xls/csv文件中转化成dbf文件
一、安装相关包
pip install xlrdpip install dbfpy
二、实现
代码如下
# -*- coding: utf-8 -*-__author__ = 'lihailin' __mail__ = '415787837@' __date__ = '-05-04' __version__ = 1.0 import pandas as pdimport os,sysfrom dbfpy import dbfimport csvdef xls2csv(xlsFile, csvFileName):# excel文件转csvdata = pd.read_excel(xlsFile, 'pcp_lookup', index_col=0)data.to_csv(csvFileName, encoding='utf-8')def genDbfHeader(dbfDb, csvHeader):'''生成dbf文件表头Args:dbfDb: Dbf类实例csvHeader: list, 其值为字符串用于创建dbf表头的字段'''for filed in csvHeader:# print filed,'--------'tmp = (filed, 'C', 25) # 定义字段dbfDb.addField(tmp)return dbfDbdef writeDbfData(dbfDb, dbfData):'''写数据到dbf文件Args:dbfDb: Dbf类实例dbfData: dict, key为Dbf的header中的字段'''for onedata in dbfData:# print onedatarec = dbfDb.newRecord()for key in onedata.keys(): # 对每条记录的所有字段写数据# print(key)rec[key] = onedata[key]rec.store()def csv2dbf(csvFile, dbfFile):# csv文件转dbf文件with open(csvFile, 'rb') as csvfile:# dbf写表头reader = csv.reader(csvfile, delimiter=',', quotechar='|')header_row = next(reader)db = dbf.Dbf(dbfFile, new=True)db = genDbfHeader(db, header_row)with open(csvFile, 'rb') as csvfile:# 写dbf数据reader = csv.DictReader(csvfile) # 字典形式writeDbfData(db, reader)db.close()def main():# 遍历一个文件夹下的所有.xlsx文件,并将其转换成.dbf文件xlsDictory = '.' #文件夹for xlsFile in os.listdir(xlsDictory):tmp = xlsFile.split('.')[0]# if '.xlsx' in xlsFile:# continue# if '.csv' not in xlsFile:#continuecsvFileName = '%s.csv' % tmpif '.xlsx' in xlsFile: # 如果是.xlsx先转成.csv xls2csv(xlsFile, csvFileName)dbfFileName = '%s.dbf' % tmpcsv2dbf(csvFileName, dbfFileName)os.system('mkdir dbfFile')os.system('mv *.dbf dbfFile')if __name__ == '__main__':main()