更新时间:2023-01-21 11:13:48
在使用python 2.7和pypyodbc时,我得到了一些工作...
Working with Python 2.7 and pypyodbc, I got this to sort-of-work ...
# -*- coding: utf-8 -*-
import os
import pypyodbc
workingFolder = "C:\\Users\\Gord\\Desktop\\"
pipeFileName = "stuff.txt"
commaFileName = "stuff.csv"
with open (workingFolder + pipeFileName, "r") as pipeFile:
data = pipeFile.read()
with open (workingFolder + commaFileName, "w") as commaFile:
commaFile.write(data.replace("|",","))
connStr = (
r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};' +
r'DBQ=C:\Users\Public\Database1.accdb'
)
db = pypyodbc.connect(connStr)
qry = """INSERT INTO text_file_data ([Letter], [Number], [Batch], [Year], [Parcel], [Amount])
SELECT F1, F2, F3, F4, F5, F6
FROM [Text;FMT=Delimited;HDR=NO;IMEX=2;CharacterSet=437;Database="""
qry += workingFolder + "].[" + commaFileName.replace(".","#") + "]"
crsr = db.cursor()
crsr.execute(qry)
db.commit()
db.close()
os.remove(workingFolder + commaFileName)
...但是从[Parcel]字段中去除了前导零.这似乎更好(尽管不确定速度):
... but it stripped the leading zero from the [Parcel] field. This seemed to work better (not sure about speed, though):
# -*- coding: utf-8 -*-
import pypyodbc
connStr = (
r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};' +
r'DBQ=C:\Users\Public\Database1.accdb'
)
db = pypyodbc.connect(connStr)
qry = """INSERT INTO text_file_data ([Letter], [Number], [Batch], [Year], [Parcel], [Amount])
VALUES (?,?,?,?,?,?)"""
crsr = db.cursor()
with open (r"C:\Users\Gord\Desktop\stuff.txt", "r") as pipeFile:
for line in pipeFile:
crsr.execute(qry, line.split("|"))
db.commit()
db.close()