使用Python将文本文件导入Access 2003数据库 [英] import text file to Access 2003 database using Python

问题描述

我正在尝试使用正在使用的Python 3.4应用程序将管道分隔的文本文件导入到具有现有表的访问数据库中，但是遇到了一些麻烦.

I'm trying to import a pipe delimited text file into an access database with an existing table using a Python 3.4 application I'm working on, but I'm having some trouble.

该应用程序将用于导入不同的文本文件，因此我使用条目小部件写入文件名，并且希望将输入文件的内容加载到表中.我尝试使用access将文件加载到功能上，并且工作正常，因此格式应该不错.下面是我为功能尝试的一些代码，无济于事.

The application will be used to import different text files so I'm using an entry widget to write in the filename and I want the contents of the file entered to be loaded into my table. I tried loading the file into the able just using access and it worked fine so the formatting should be good. Below is some of the code I've tried for my function to no avail.

def insert_data():
inputfile = filepath.get()
fobj = open(inputfile)

cur.execute("""SELECT * INTO text_file_data
            FROM [odbc;Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq='{fobj}';];)"""
            .format(fobj=fobj))
conn.commit()

给出以下内容:

Tkinter回调中的异常追溯(最近一次通话):调用中的文件"C:\ Python34 \ lib \ tkinter__init __.py"，行1487返回self.func( args)运行中的文件"C:/Users/amarquart/PycharmProjects/Database Testing/Source/DBTesting.py"，第267行insert_data()在insert_data中的第25行，文件"C:/Users/amarquart/PycharmProjects/Database Testing/Source/DBTesting.py".format(fobj = fobj))KeyError:"Microsoft文本驱动程序("

Exception in Tkinter callback Traceback (most recent call last): File "C:\Python34\lib\tkinter__init__.py", line 1487, in call return self.func(args) File "C:/Users/amarquart/PycharmProjects/Database Testing/Source/DBTesting.py", line 267, in run insert_data() File "C:/Users/amarquart/PycharmProjects/Database Testing/Source/DBTesting.py", line 25, in insert_data .format(fobj=fobj)) KeyError: 'Microsoft Text Driver ('

和

def insert_data():
inputfile = filepath.get()
fobj = open(inputfile)

cur.execute("""SELECT * INTO text_file_data
                FROM [Text;HDR=Yes;FMT=Delimited;Database=C:\Users\amarquart\Documents\functionDB.mdb].;{fobj}')"""
                .format(fobj=fobj))
conn.commit()   

给出以下内容:

文件"C:/Users/amarquart/PycharmProjects/Database Testing/Source/DBTesting.py"，第24行FROM [文本; HDR =是; FMT =定界;数据库= C:\ Users \ amarquart \ Documents \ functionDB.mdb].; {fobj}')"SyntaxError:(unicode错误)"unicodeescape"编解码器无法解码位置93-94中的字节:截断的\ UXXXXXXXX转义

File "C:/Users/amarquart/PycharmProjects/Database Testing/Source/DBTesting.py", line 24 FROM [Text;HDR=Yes;FMT=Delimited;Database=C:\Users\amarquart\Documents\functionDB.mdb].;{fobj}')""" SyntaxError: (unicode error) 'unicodeescape' codec can't decode bytes in position 93-94: truncated \UXXXXXXXX escape

其他相关信息

数据库文件路径:C:\ Alex \ functionDB.mdb

database filepath: C:\Alex\functionDB.mdb

与fobj变量一起使用的文件路径:C:\ Users \ amarquart \ Documents \ 5.txt

filepath being used with fobj variable: C:\Users\amarquart\Documents\5.txt

表名:text_file_data

table name: text_file_data

使用pyodbc建立我的连接

using pyodbc for my connection

任何帮助将不胜感激.

谢谢

亚历克斯

编辑我的文本文件没有标题，下面是其中的一个示例

EDIT My text file has no headers, an example from it is posted below

D | 1 | 502 | 2013 | 073306586 | 479.18

D|1|502|2013|073306586|479.18

最新尝试:

def insert_data():
inputfile = filepath.get()
fobj = open(inputfile)

cur.execute("""INSERT INTO text_file_data (Letter, [Number], Batch, [Year], Parcel, Amount)
         VALUES ([Text;FMT=Delimited(|);HDR=NO;DATABASE=C:\Alex\functionDB.mdb].
         ['{fobj}')]""").format(fobj=fobj)

conn.commit()

给我以下内容:

Tkinter回调中的异常追溯(最近一次通话):调用中的文件"C:\ Python34 \ lib \ tkinter__init __.py"，行1487返回self.func(* args)运行中的文件"C:/Users/amarquart/PycharmProjects/Database Testing/Source/DBTesting.py"，第269行insert_data()insert_data中的文件"C:/Users/amarquart/PycharmProjects/Database Testing/Source/DBTesting.py"，第26行['{fobj}')]").format(fobj = fobj)pyodbc.Error :("21S01"，"[21S01] [Microsoft] [ODBC Microsoft Access驱动程序]查询值和目标字段的数量不同.(-3520)(SQLExecDirectW)")

Exception in Tkinter callback Traceback (most recent call last): File "C:\Python34\lib\tkinter__init__.py", line 1487, in call return self.func(*args) File "C:/Users/amarquart/PycharmProjects/Database Testing/Source/DBTesting.py", line 269, in run insert_data() File "C:/Users/amarquart/PycharmProjects/Database Testing/Source/DBTesting.py", line 26, in insert_data ['{fobj}')]""").format(fobj=fobj) pyodbc.Error: ('21S01', '[21S01] [Microsoft][ODBC Microsoft Access Driver] Number of query values and destination fields are not the same. (-3520) (SQLExecDirectW)')

编辑知道了

这与我在互联网上找到的所有内容都不同，但是可以使用.它将所有数据从文本文件存储到数据库中，虽然数据的顺序不同，但这并不重要.

This is different from everything I found on the internet, but it works. It got all the data from the text file into the database, the data wasn't in the same order, but that really doesn't matter.

def insert_data():
inputfile = filepath.get()
fobj = open(inputfile)
for line in fobj:
    column = line.split('|')
    a = str(column[0])
    b = int(column[1])
    c = int(column[2])
    d = str(column[3])
    e = str(column[4])
    f = Decimal(column[5])

    cur.execute('''INSERT INTO text_file_data ([Letter], [Number], [Batch], [Year], [Parcel], [Amount])
         VALUES ('{a}', '{b}', '{c}', '{d}', '{e}', '{f}')'''.format(a=a, b=b, c=c, d=d, e=e, f=f))

conn.commit()

编辑，再次获得

def insert_data():
inputfile = filepath.get()

qry = """INSERT INTO text_file_data ([Letter], [Number], [Batch], [Year], [Parcel], [Amount])
VALUES (?,?,?,?,?,?)"""

with open(inputfile) as pipeFile:
    for line in pipeFile:
        cur.execute(qry, line.split("|"))
conn.commit()

这也可行，并且可能更好吗?

this works too, and is possibly better?

感谢大家的帮助！

推荐答案

在使用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()

这篇关于使用Python将文本文件导入Access 2003数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！