Win32com Python script/MS-Access Please help!!

Tue Jun 18 16:51:42 EDT 2002

Hey all,
I'm having trouble writing a Python script relating to Microsoft
Access.  If
anyone could help me I would greatly appreciate it.  Here's the
problem :

I built an MS-Access table with 5 columns(fields) : OrderID, OrderDate,
PlantID, ProductID, OrderQty, with OrderID as the primary key.  All
fields
are numeric, except for OrderDate(which is a date field).

Now I want to populate this table with data from a web page
(http://opim.wharton.upenn.edu/~opim101/spring02/Plant1.html).  please
check
the site.

My object is to read the the data and insert it record by record into my
Access table.

Here's what I have so far :

import urllib, string
Plant1 =
urllib.urlopen
('http://opim.wharton.upenn.edu/~opim101/spring02/Plant1.html'
)
lines1 = Plant1.readlines()
import win32com.client
engine = win32com.client.Dispatch("DAO.DBEngine.35")
db=engine.OpenDatabase(r'd:\Plants.mdb')

I have several problems :
Firstly, when I read the lines, I notice that not all of them are
relevant.
I need to ignore <html>, <body>, etc. at the beginning of the file, as
well
as the column headers, so that the action really begins from line 4 of
the
file and ends with </body>
Also, I need to eliminate <p> from every line in the file in order to
get to
the five comma-delimited fields that follow, and then split the rest of
the
string to get to each field.  Here's what I think part of the code
should
look like :

for line in lines1.readlines():
    line=line.rstrip()
    inOrderID,inOrderDate,inRegionID,inProductID,inOrderQty =
line.split(",")

Something like that, I'm not sure...
Lastly, now that I've read all the fields, I need to insert them each
as a
single record into my table of my access database using Python DAO
using the
Insert statement.  Also, the OrderDate field that I read in, looks like
a
text type to Access, so I need to pad it with the # sign on either side
before inserting it into the Access database, so that Access interprets
it
as a Date/Time type.

Finally, I need to get the same information from 2 other plants and tag
them
onto the same table...from the web page
http://opim.wharton.upenn.edu/~opim101/spring02/PlantPortal.html
where plant1's data shows up first, plant2's data shows up below it, and
plant3's data shows up last...so all plants' data in the same table.

I would like
to use more standard commands.  I can use SQL, and I know Python -
Access
commands like OpenRecordSet(), TableDefs.count(), Execute(), while not
rs.EOF, Fields().....
Is there any other way to write the program without SGML?  To give you
more
of an idea of the way I'd like the program, I wrote another program for
doing a similar thing in Excel, not Access, for a file named sales.txt
and
the same 5 columns, but put from a file into 5 columns on excel, and
certain
parameters to enter at the beginning... :

ProductID = float(raw_input("Please enter the ProductID: "))
OrderQty = float(raw_input("Please enter the cutoff order quantity: "))
CSV = open('c:/sales.txt','r')
import win32com.client
x1=win32com.client.Dispatch('Excel.Application')
x1.visible=1
x1.Workbooks.Add()
x1.Range("A1").Select()
x1.Range("A1:B1").Value = ("ProductID:",ProductID)
x1.Range("A2:B2").Value = ("OrderQty:",OrderQty)
x1.ActiveCell.Offset(4).Activate()
x1.ActiveCell.Range("A1:E1").Value=
("OrderID","OrderDate","RegionID","Produc
tID","OrderQty")
x1.ActiveCell.Offset(2).Activate()
for line in CSV.readlines():
    line=line.rstrip()
inOrderID,inOrderDate,inRegionID,inProductID,inOrderQty = line.split
(",")
if float(inProductID) == ProductID:
       if float(inOrderQty) >= OrderQty:
           xl.ActiveCell.Range("A1:E1").Value =
(inOrderID,inOrderDate,inRegionID,inProductID,inOrderQty)
xl.ActiveCell.Offset(2).Activate()

I just don't know how to adjust this type of thing for Access...

If anyone could help me by writing the script I would greatly appreciate
it...
Thank you so much.
Sincerely,
Dave