Win32com Python script/MS-Access Please help!!
roelsch at acm.org
Thu Jun 20 06:56:22 CEST 2002
You've done a good job of re-posting (my) code from
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
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.
"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
> 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
> 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 =
> lines1 = Plant1.readlines()
> import win32com.client
> engine = win32com.client.Dispatch("DAO.DBEngine.35")
> I have several problems :
> Firstly, when I read the lines, I notice that not all of them are
> I need to ignore <html>, <body>, etc. at the beginning of the file, as
> as the column headers, so that the action really begins from line 4 of
> 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
> string to get to each field. Here's what I think part of the code
> look like :
> for line in lines1.readlines():
> inOrderID,inOrderDate,inRegionID,inProductID,inOrderQty =
> 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
> 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
> as a Date/Time type.
> Finally, I need to get the same information from 2 other plants and tag
> onto the same table...from the web page
> 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 -
> 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
> 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
> the same 5 columns, but put from a file into 5 columns on excel, and
> 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.Range("A1:B1").Value = ("ProductID:",ProductID)
> x1.Range("A2:B2").Value = ("OrderQty:",OrderQty)
> for line in CSV.readlines():
> inOrderID,inOrderDate,inRegionID,inProductID,inOrderQty = line.split
> if float(inProductID) == ProductID:
> if float(inOrderQty) >= OrderQty:
> xl.ActiveCell.Range("A1:E1").Value =
> 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
> Thank you so much.
More information about the Python-list