Win32com Python script/MS-Access Please help!!

Thu Jun 20 02:04:00 EDT 2002

Sprung !!!!!

However, I would like to know as well.  I live in Aust and find it
hard (and expensive) going to school/uni in other parts of the world
:-)

Bart

Robert Oelschlaeger wrote:
> 
> 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
> >
> >
> >
> >
> >
> >