Win32com Python script/MS-Access Please help!!

Thu Jun 20 00:56:22 EDT 2002

Dave:

You've done a good job of re-posting (my) code from
http://aspn.activestate.com/ASPN/Mail/Message/1225515 as
your own work. Yes, you changed two "int"s to "float"s and
added a "c:/" to the "open" call, and removed the "def main():",
but that's about it.

Good luck with your homework. Let's see, that would be "OPIM
101: Introduction to Computers as Analysis Tools" at the Wharton
School, University of Pennsylvania. See also
http://opim-web.wharton.upenn.edu/home/coursedes.html#OPIM101

Next time, you might just ask for help at opim-help at wharton.upenn.edu

One of the stated aims of that course is "Build student skill and
comfort using the computer to solve problems"; I don't
think that they mean for you to use the computer to find
others to do your work for you.

Bob

"Dave" <dave5774 at hotmail.com> wrote in message
news:aeo6h1$itf$1 at netnews.upenn.edu...
> 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
>
>
>
>
>
>