Searching for txt file and importing to ms access

Discussion in 'Python' started by Mark Line, Oct 20, 2005.

  1. Mark Line

    Mark Line Guest

    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 = ''
    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'
     
    Mark Line, Oct 20, 2005
    #1
    1. Advertisements

  2. Mark Line

    Mike Meyer Guest

    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.
    Can't help with that. The phrase "win32com" comes to mind, but I'm not
    a windows person.

    <mike
     
    Mike Meyer, Oct 20, 2005
    #2
    1. Advertisements

  3. Mark Line

    Mark Line Guest

    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
     
    Mark Line, Oct 21, 2005
    #3
  4. Mark Line

    J Correia Guest

    <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
     
    J Correia, Oct 21, 2005
    #4
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.