import text file to Access 2003 database using Python

Question

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.

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()

gives the following:

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 ('

and

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()

gives the following:

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

other related info

database filepath: C:\Alex\functionDB.mdb

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

table name: text_file_data

using pyodbc for my connection

Any help would be much appreciated.

Thanks,

Alex

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

D|1|502|2013|073306586|479.18

LATEST ATTEMPT:

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()

is giving me the following:

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)')

EDIT GOT IT

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()

EDIT, GOT IT AGAIN

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?

Thanks for the help everyone!

I don't understand the FROM lines of your query. For example: FROM [Text;HDR=Yes;FMT=Delimited;Database=C:\Users\amarquart\Documents\functionDB.mdb].;{fobj}'. It looks like you're trying to embed the connection string in the SQL? Or am I misunderstanding? — Bryan
– Bryan, Commented Aug 27, 2014 at 19:07
I'm trying to add the values from the text file (fobj) to the database, but I'm pretty much just working with what I've been able to find online so the FROM is from random similar things I've found around the internet. There's been a few developments though, perhaps I'm closer. I'll edit my question to show where I am now. — AlMar89
– AlMar89, Commented Aug 27, 2014 at 19:22
Be aware that your revised approach uses dynamic SQL and consequently is vulnerable to SQL Injection problems. For example, e = "O'Leary" would cause your program to fail. That's why I used a parameterized query in my answer. — Gord Thompson
– Gord Thompson, Commented Aug 28, 2014 at 14:00
@GordThompson I don't think that would be an issue since the text files only include numbers and singular letters, but I'm always willing to try to make my program better. I think you're way would work for me if we could get the filename to be dynamic as the program will have to be used to import different files. Currently I'm inputting the filename to an entry widget and reading the entry widget to get the filename in the fobj variable. So I'd need something like with open (r"fobj", "r") as pipeFile: but that doesn't seem to be working. — AlMar89
– AlMar89, Commented Aug 28, 2014 at 14:39
@GordThompson wait, I think I've got it, check my edit on the original question — AlMar89
– AlMar89, Commented Aug 28, 2014 at 14:52

Gord Thompson · Accepted Answer · 2014-08-27 21:31:12Z

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)

... 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()

Thanks for the answer, you gave me the idea to try line.split("|") and it seems to be working!

AlMar89 · Accepted Answer · 2014-08-28 16:52:05Z

0

This is what did it for me

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()

Thanks everyone!

answered Aug 28, 2014 at 16:52

AlMar89

1752 silver badges9 bronze badges

Collectives™ on Stack Overflow

import text file to Access 2003 database using Python

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related