RE: Excel / my SQL

I've recieved several requests for this information privately so I figured
that I would send it to the entire list.

Basically, you have to define a named range in an excel spreadsheet, then
create the proper type of dsn, then you can query the file via ado as if it
were a database.

All of the necessary instructions can be found in this article on microsofts
web site:

HOWTO: Query and Update Excel Data Using ADO From ASP (Q195951)
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q195951

It worked like a charm for me and was fast.

My requirements were to create an Excel spreadsheet that could be used as a
template by the client.  The client was responsible for adding the data to
the spreadsheet correctly. Then, I had to create a page that would allow the
client to upload the excel spreadsheet to the server. Once the excel file
was uploaded, my application would then pull a recordset of all of the data
in the excel spreadsheet and then run through and populate a series SQL
Server tables with the Excel data. That data was then immediately available
on the web site.

If the site is hosted on a remote server, you may have to send the
instructions for creating the DSN to the admins, but that shouldn't be too
much of an issue.

Andre


---------------------------------------------------------






actually, Excell can be used as the db... Just define the area of data and
you can query it on the web via ado or oledb. I've done it and it was as
easy as querying any other db. If you want code and instructions let me know
and I will send it over.

-----Original Message-----
From: owner-hwg-techniques(at)hwg.org
[mailto:owner-hwg-techniques(at)hwg.org]On Behalf Of Frank Boumphrey
Sent: Wednesday, January 30, 2002 3:11 PM
To: Mike Taylor; Tamara Nelson
Cc: hwg-techniques(at)hwg.org
Subject: Re: Excel / my SQL


 > We have a client who wants to pull his MS Excel file into his website.
 > Currently he is on a IIS server - but he wants to switch to an Unix
server
 > so that we can pull it in through PHP.

Actually there is no need to switch to Unix (At least no reason to switch on
this account...). Both PHP and mySQL run perfectly well on IIS.

However to convert an excel file to a database will require a little bit of
work! I know that if you use ASP there are special objects available that
will directly read your Excel file, and this may be the way to go if you
need to do this on the fly.

To create a static page from an Excel spreadsheet see


'Creating Web Pages to Display Excel Data'
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odeopg/html
/deconcreatingwebpagestodisplayexceldata.asp

Also there is a ton of stuff if you search under 'Excel' on
http://msdn.microsoft.com

HTH

Frank

----- Original Message -----
From: "Mike Taylor" <lonewolf(at)one.net>
To: "Tamara Nelson" <tamara(at)sanctusmoo.com>
Cc: <hwg-techniques(at)hwg.org>
Sent: Wednesday, January 30, 2002 1:39 PM
Subject: Re: Excel / my SQL


 > There likely is, since we do the same thing with CSV files, importing
 > them into SQL using ASP.
 >
 > You should be able to hunt down info at www.php.net
 >
 > In fact, I see a function there that might help:
 > http://www.php.net/manual/en/function.fgetcsv.php
 >
 > Mike
 >
 >
 > On Wed, 30 Jan 2002, Tamara Nelson wrote:
 >
 > > We have a client who wants to pull his MS Excel file into his website.
 > > Currently he is on a IIS server - but he wants to switch to an Unix
server
 > > so that we can pull it in through PHP.
 > >
 > > I've read up a bit on PHP and mySQL, but I'm not sure I'm understanding
it
 > > well.
 > >
 > > Is it true that I can import a CSV file (created by Excel) into mySQL
using
 > > PHP?
 > >
 > > I think I need my hand held...
 > > TIA,
 > > Tamara Nelson
 > >
 > >
 > >
 >

HWG hwg-techniques mailing list archives, maintained by Webmasters @ IWA