datatransfer

datatransfer.py

# coding=utf-8
import sys,os
import os.path
import xlrd
import pypyodbc
from  datetime  import  *
import time
import types


xls_path = './xls/'
#connect the database
conn = pypyodbc.connect('Driver={Microsoft Access Driver (*.mdb)};DBQ=./data/Data.mdb')
cur = conn.cursor()

colname = {}
colname['fahuoshijian']		= 1
colname['danwei']			= 2
colname['shouhuoren']		= 3
colname['dianhua']			= 4
colname['dizhi']			= 5
colname['songhuofangshi']	= 6
colname['huowuzhongliang']	= 7
colname['datong']			= 8
colname['xiaotong']			= 9
colname['fapiao']			= 10
colname['zhuaghuodizhi']	= 11
colname['zhuangcheshijian']	= 12
colname['yunfeijiesuan']	= 13
colname['jingbanren']		= 14
colname['bumen']			= 15
colname['weixianpin']		= 16
colname['yunfei']			= 17
colname['chengyunshang']	= 18
colname['siji']				= 19
colname['sijidianhua']		= 20
colname['chehao']			= 21
colname['zhengjianhao']		= 22
colname['songdashijian']	= 23
colname['beizhu']			= 24
colname['beiyong']			= 25
colname['beiyong1']			= 26
colname['beiyong2']			= 27

col_list = [];
for i in colname:
	col_list.append(i)

col_tpl = '(' + ','.join(col_list) + ')'


#ergodic the xls folder
for parent,dirnames,filenames in os.walk(xls_path):
	for filename in filenames:
		xls_file = os.path.join(parent,filename)
		book = xlrd.open_workbook(xls_file)
		table = book.sheet_by_index(0)
		#get row num
		nrows = table.nrows
		print("")
		print("=================")
		print("Filename:" + xls_file + "  Total Data:" + str(nrows-1) + "")

		data_list = []
		
		for i in range(nrows):
			if(i==0):
				continue
			#generate row data
			row_data_list = []
			for j in colname:
				ceil_data = table.row_values(i)[ colname[j] ]
				if(ceil_data==''):
					row_data_list.append("'" + str(ceil_data) + "'")
					continue
				if(j=='fahuoshijian' or j=='songdashijian'):
					tmp_time = xlrd.xldate_as_tuple(ceil_data, book.datemode)
					tmp_dttm = datetime(tmp_time[0], tmp_time[1], tmp_time[2], tmp_time[3], tmp_time[4], tmp_time[5])
					tmp_str = tmp_dttm.strftime('%Y-%m-%d %H:%M:%S')
					row_data_list.append("'#" + tmp_str + "#'")
				else:
					if(type(ceil_data)==type(1.0)):
						row_data_list.append( "'" + str(ceil_data) + "'" )
					else:
						row_data_list.append( "'" + ceil_data + "'" )
			row_data = '(' + ','.join(row_data_list) + ')'

			sql = 'insert into taizhang ' + col_tpl + ' values ' + row_data + ';'
			cur.execute(sql)
			cur.commit()

		print("Inserted " + str(nrows-1) + " rows")


conn.close()