Searching for txt file and importing to ms access

[Mark Line]

Hello!



I'm a python n00b!



I've been writing in c++ for a few years so programming's not new to me,
just python that I don't know the syntax!



What I'm trying to do is get an email from an pop3 account, and then search
the email for some CVS data and import that information into MS Access.



I'm managed to get some code to download a message from the email account
and save it to a text file, does any one have a link to some sample code to
search though a file until a string of characters is matched? Or could
point me to some functions that may help me with this?



I've also managed to connect to my access database, and just print out a
field in a table, but I cant find anywhere on the web that will help me to
import data? Any help would be great?!



By the way I'm running on windows, and have installed the windows add on's.



Thanks in advance

Mark



email code



import sys, poplib


f = open('c:\\myfile.txt', 'w')

mailserver = 'pop3.abilitec.com'
mailuser = '(e-mail address removed)'
mailpasswd = '***********'



print 'Connecting...'
server = poplib.POP3(mailserver)
server.user(mailuser) # connect, login to mail server
server.pass_(mailpasswd) # pass is a reserved word

try:
print server.getwelcome() # print returned greeting message
msgCount, msgBytes = server.stat()
print 'There are', msgCount, 'mail messages in', msgBytes, 'bytes'
print server.list()
print '-'*80

for i in range(msgCount):
hdr, message, octets = server.retr(i+1) # octets is byte count
for line in message:
# retrieve, print all
mail
f.write(line)
f.write('\n')
print '-'*80 # mail box locked till
quit
if i < msgCount - 1:
raw_input('[Press Enter key]')
finally: # make sure we unlock
mbox
server.quit() # else locked till
timeout
print 'Bye.'

f.close()





access



import sys, win32com.client

print 'start'

connection = win32com.client.Dispatch(r'ADODB.Connection')
DSN = 'PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA
SOURCE=c:\\booking.mdb;/root/blackcomb'
connection.Open(DSN)
recordset = win32com.client.Dispatch(r'ADODB.Recordset')
recordset.Open('SELECT * FROM tblBooking', connection, 1, 3)
fields_dict = {}
for x in range(recordset.Fields.Count):
fields_dict[x] = recordset.Fields.Item(x).Name
print fields_dict[x], recordset.Fields.Item(x).Value

print 'end'

 

[Mike Meyer]

I'm managed to get some code to download a message from the email account
and save it to a text file, does any one have a link to some sample code to
search though a file until a string of characters is matched? Or could
point me to some functions that may help me with this?

datafile = open("c:\\myfile.txt", "r")
data = datafile.read()
datafile.close()
start = data.index(myindicator)

will leave start as the index in data where the the string in
myindicator first appears. If you want the end of myendicator, use
start = data.find(myindicator) + len(myindicator).

Have you considered not saving the message to disk? You can manipulate
it all in memory.

I've also managed to connect to my access database, and just print out a
field in a table, but I cant find anywhere on the web that will help me to
import data? Any help would be great?!

Can't help with that. The phrase "win32com" comes to mind, but I'm not
a windows person.

<mike

 

[Mark Line]

Once i have this working i was planing to kept all the txt files as logs,
i'd have to give them a real name and stuff.

But thanks for you help so far

Mark

 

[J Correia]

Hello!

I've also managed to connect to my access database, and just print out a
field in a table, but I cant find anywhere on the web that will help me to
import data? Any help would be great?!

<snip>

Another method of talking to MS Access is to set up an ODBC datasource...
Control Panel > Data Sources (ODBC). Then download and import
the mx.ODBC module... this worked a lot faster in my setup than using the
win32com route and i find the clear SQL layout simpler to understand.

Attached is some sample code I used to quickly get some data from SQL Server,
process it, and load into Access, both set up as ODBC data sources.

<Python Code>
import mx.ODBC.Windows

dbc1 = mx.ODBC.Windows.Connect('<SQLServer source>', user='<user>',
password='xxx', clear_auto_commit=0)
dbc2 = mx.ODBC.Windows.Connect('<MS Access source>', user='<user>',
password='xxx', clear_auto_commit=0)

# Create cursors on databases.
crsr1 = dbc1.cursor()
crsr2 = dbc2.cursor()

# Get record(s) from SQL Server database.
try:
crsr1.execute(
"""
SELECT product_id, image
FROM SUP_CATALOGUE_PRODUCT
""")
except Exception, err:
print "*** Error extracting records from SQL Server***"
print "Exception:", Exception, "Error:", err
sys.exit()
else:
results = crsr1.fetchall() # fetch the results all at once into a
list.
if not len(results): # No records found to be processed.
print "No records returned from SQL Server table, aborting..."
sys.exit()
else: # Have records to work with, continue processing
print len(results), "records to be updated..."

i = 0
for item in results:
....
.... < processing of each record goes here.>
....

# Now update 1 record in the Access table.
try:
crsr2.execute(
"""
UPDATE SUP_CATALOGUE_PRODUCT
SET image = '%s'
WHERE product_id = %d
""" % (new_image, product_id)
)
except Exception, err:
print "*** Error updating records in MS Access***"
print "Exception:", Exception, "Error:", err
sys.exit()

i += 1

print "All done... records written:", i

</Python Code>


HTH,

JC