473,287 Members | 1,582 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

Searching for txt file and importing to ms access

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 = 'b*******@pop3.abilitec.com'
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'



Oct 20 '05 #1
3 2386
"Mark Line" <ml***@abilitec.com> writes:
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
--
Mike Meyer <mw*@mired.org> http://www.mired.org/home/mwm/
Independent WWW/Perforce/FreeBSD/Unix consultant, email for more information.
Oct 20 '05 #2
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
"Mike Meyer" <mw*@mired.org> wrote in message
news:86************@bhuda.mired.org...
"Mark Line" <ml***@abilitec.com> writes:
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
--
Mike Meyer <mw*@mired.org> http://www.mired.org/home/mwm/
Independent WWW/Perforce/FreeBSD/Unix consultant, email for more
information.

Oct 21 '05 #3
"Mark Line" <ml***@abilitec.com> wrote in message
news:dj**********@nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com...
Hello!

<snip>
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
Oct 21 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

11
by: Grim Reaper | last post by:
I am importing a .csv file into Access that has 37 fields. My problem is that sometimes the last field only has data at the end of the column (it looks like when you import a file into Access, for...
6
by: Paul | last post by:
I was wondering if anyone has had an issue where using vba code to read an excel file and import the data into an access table some records are not imported from the excel file. It seems looking at...
1
by: sparks | last post by:
I have never done this and wanted to ask people who have what is the best way. One person said import it to excel, then import it into access table. but since this will be done a lot, I am...
3
by: George Yeh | last post by:
Hi There! Currently I am importing a .csv file that eventually grows over time. I have setup my table with an unique id field that is the primary key and indexed. I have also indicated in the...
2
by: nutthatch | last post by:
I want to be able to import an Excel spreadsheet into Access 2K using the macro command Transferspreadsheet. However, the file I am importing (over which I have no control) contains some records...
1
by: Geoff Jones | last post by:
Hi I have a question which I hope somebody can answer. I have written a VB application with which I want to import an Excel file, analyze the data within it and do some calculations. There are...
1
by: Alan | last post by:
OK this is a weird one. I've got an import routine going whereby name and address data is pulled into a table from a csv file. I'm having strange results when importing postcode/zip data into...
1
by: Phil | last post by:
I want to add a button to a form to import a text delimited file. The File is delimited by "^" and so I have created a specification file by manually impoting the text file, using the advanced...
4
by: Alvin SIU | last post by:
Hi all, I have 6 tables inside a MS Access 2003 mdb file. I want to convert them as DB2 version -8 tables in AIX 5.2. I have exported them as 6 XML files. The XML files look fine. Each...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.