Parsing Excel spreadsheet to db

[Phil Powell]

What would one best recommend to parse an existing Excel spreadsheet (was
done in Excel 97 or 2000 not sure to be honest)? I am looking for the most
practical way of parsing an existing spreadsheet to place contents into a
mySQL db.

Thanx, Phil

PS: can you use fopen() to read the contents and parse? The spreadsheet is
on a remote site.

[Randell D.]

Re: Parsing Excel spreadsheet to db


"Phil Powell" <soazine@erols. com> wrote in message
news:aws7b.1456 05$xf.35957@lak eread04...[color=blue]
> What would one best recommend to parse an existing Excel spreadsheet (was
> done in Excel 97 or 2000 not sure to be honest)? I am looking for the[/color]
most[color=blue]
> practical way of parsing an existing spreadsheet to place contents into a
> mySQL db.
>
> Thanx, Phil
>
> PS: can you use fopen() to read the contents and parse? The spreadsheet is
> on a remote site.
>
>[/color]

i don't know how big your spreadsheet is, but you could save the excel
spreadsheet as a "csv" type file, then use fopencsv() to read it, then use
explode() to create columns out of each row (thus giving you a
multi-dimensional array breaking values down to an excel cell)... You could
then use this array as your source data and send it to mysql.

[Phil Powell]

Re: Parsing Excel spreadsheet to db

I would, but I have neither Microsoft Excel nor do I have the spreadsheet
locally (were I to do so since not having Excel it would not be able to do
anything with it.)

Since I can't convert it to .csv because of that, what do I do?

Thanx
Phil

"Randell D." <you.can.email. me.at.randelld@ yahoo.com> wrote in message
news:g7t7b.9326 07$3C2.21222945 @news3.calgary. shaw.ca...[color=blue]
>
> "Phil Powell" <soazine@erols. com> wrote in message
> news:aws7b.1456 05$xf.35957@lak eread04...[color=green]
> > What would one best recommend to parse an existing Excel spreadsheet[/color][/color]
(was[color=blue][color=green]
> > done in Excel 97 or 2000 not sure to be honest)? I am looking for the[/color]
> most[color=green]
> > practical way of parsing an existing spreadsheet to place contents into[/color][/color]
a[color=blue][color=green]
> > mySQL db.
> >
> > Thanx, Phil
> >
> > PS: can you use fopen() to read the contents and parse? The spreadsheet[/color][/color]
is[color=blue][color=green]
> > on a remote site.
> >
> >[/color]
>
> i don't know how big your spreadsheet is, but you could save the excel
> spreadsheet as a "csv" type file, then use fopencsv() to read it, then use
> explode() to create columns out of each row (thus giving you a
> multi-dimensional array breaking values down to an excel cell)... You[/color]
could[color=blue]
> then use this array as your source data and send it to mysql.
>
>[/color]

[Randell D.]

Re: Parsing Excel spreadsheet to db


"Phil Powell" <soazine@erols. com> wrote in message
news:NIv7b.1463 97$xf.117696@la keread04...[color=blue]
> I would, but I have neither Microsoft Excel nor do I have the spreadsheet
> locally (were I to do so since not having Excel it would not be able to do
> anything with it.)
>
> Since I can't convert it to .csv because of that, what do I do?
>
> Thanx
> Phil
>
> "Randell D." <you.can.email. me.at.randelld@ yahoo.com> wrote in message
> news:g7t7b.9326 07$3C2.21222945 @news3.calgary. shaw.ca...[color=green]
> >
> > "Phil Powell" <soazine@erols. com> wrote in message
> > news:aws7b.1456 05$xf.35957@lak eread04...[color=darkred]
> > > What would one best recommend to parse an existing Excel spreadsheet[/color][/color]
> (was[color=green][color=darkred]
> > > done in Excel 97 or 2000 not sure to be honest)? I am looking for the[/color]
> > most[color=darkred]
> > > practical way of parsing an existing spreadsheet to place contents[/color][/color][/color]
into[color=blue]
> a[color=green][color=darkred]
> > > mySQL db.
> > >
> > > Thanx, Phil
> > >
> > > PS: can you use fopen() to read the contents and parse? The[/color][/color][/color]
spreadsheet[color=blue]
> is[color=green][color=darkred]
> > > on a remote site.
> > >
> > >[/color]
> >
> > i don't know how big your spreadsheet is, but you could save the excel
> > spreadsheet as a "csv" type file, then use fopencsv() to read it, then[/color][/color]
use[color=blue][color=green]
> > explode() to create columns out of each row (thus giving you a
> > multi-dimensional array breaking values down to an excel cell)... You[/color]
> could[color=green]
> > then use this array as your source data and send it to mysql.
> >
> >[/color]
>
>[/color]


If you don't have the Excel file locally, how do you expect PHP to process
it? If you can get it via a hyperlink, then I make the assumption then that
you have an "xls" type file - I don't believe there's an existing function
that would open it for you.

Is it a one off task? If so, and you don't know someone who has Excel to
convert it for you, then try OpenOffice.org (free and works on all versions
of office documents up to Office 2000). Then re-save it as a "csv" type
file.
--
A: Because it messes up the order in which people normally read text.
Q: Why is top-posting such a bad thing?
A: Top-posting.
Q: What is the most annoying thing on usenet?

[Phil Powell]

Re: Parsing Excel spreadsheet to db

See Below

"Randell D." <you.can.email. me.at.randelld@ yahoo.com> wrote in message
news:8py7b.9333 94$3C2.21274441 @news3.calgary. shaw.ca...[color=blue]
>
> "Phil Powell" <soazine@erols. com> wrote in message
> news:NIv7b.1463 97$xf.117696@la keread04...[color=green]
> > I would, but I have neither Microsoft Excel nor do I have the[/color][/color]
spreadsheet[color=blue][color=green]
> > locally (were I to do so since not having Excel it would not be able to[/color][/color]
do[color=blue][color=green]
> > anything with it.)
> >
> > Since I can't convert it to .csv because of that, what do I do?
> >
> > Thanx
> > Phil
> >
> > "Randell D." <you.can.email. me.at.randelld@ yahoo.com> wrote in message
> > news:g7t7b.9326 07$3C2.21222945 @news3.calgary. shaw.ca...[color=darkred]
> > >
> > > "Phil Powell" <soazine@erols. com> wrote in message
> > > news:aws7b.1456 05$xf.35957@lak eread04...
> > > > What would one best recommend to parse an existing Excel spreadsheet[/color]
> > (was[color=darkred]
> > > > done in Excel 97 or 2000 not sure to be honest)? I am looking for[/color][/color][/color]
the[color=blue][color=green][color=darkred]
> > > most
> > > > practical way of parsing an existing spreadsheet to place contents[/color][/color]
> into[color=green]
> > a[color=darkred]
> > > > mySQL db.
> > > >
> > > > Thanx, Phil
> > > >
> > > > PS: can you use fopen() to read the contents and parse? The[/color][/color]
> spreadsheet[color=green]
> > is[color=darkred]
> > > > on a remote site.
> > > >
> > > >
> > >
> > > i don't know how big your spreadsheet is, but you could save the excel
> > > spreadsheet as a "csv" type file, then use fopencsv() to read it, then[/color][/color]
> use[color=green][color=darkred]
> > > explode() to create columns out of each row (thus giving you a
> > > multi-dimensional array breaking values down to an excel cell)... You[/color]
> > could[color=darkred]
> > > then use this array as your source data and send it to mysql.
> > >
> > >[/color]
> >
> >[/color]
>
>
> If you don't have the Excel file locally, how do you expect PHP to process
> it? If you can get it via a hyperlink, then I make the assumption then[/color]
that[color=blue]
> you have an "xls" type file - I don't believe there's an existing function
> that would open it for you.
>
> Is it a one off task? If so, and you don't know someone who has Excel to
> convert it for you, then try OpenOffice.org (free and works on all[/color]
versions[color=blue]
> of office documents up to Office 2000). Then re-save it as a "csv" type
> file.
> --[/color]

I can't install OpenOffice! the setup.exe file does absolutely nothing. I
went to www.openoffice.org and downloaded the entire package for Windows
2000, and nothing happens. So I'm back to the drawing board!

Phil


[color=blue]
> A: Because it messes up the order in which people normally read text.
> Q: Why is top-posting such a bad thing?
> A: Top-posting.
> Q: What is the most annoying thing on usenet?
>
>[/color]

[Zurab Davitiani]

Re: Parsing Excel spreadsheet to db

Phil Powell wrote on Tuesday 09 September 2003 23:34:
[color=blue]
> I can't install OpenOffice! the setup.exe file does absolutely nothing. I
> went to www.openoffice.org and downloaded the entire package for Windows
> 2000, and nothing happens. So I'm back to the drawing board!
>[/color]

There is one other way. If you have or have access to Excel ODBC drivers,
you could set up an ODBC data source pointing to the specified Excel file
with defined tables inside the spreadsheet. Then you could use the ODBC
connection from PHP to access data from Excel spreadsheet like you would to
other databases.

One thing to keep in mind is that - Excel file has a proprietary format. If
you do not have MS Office with Excel, you don't have COM components that
access Excel spreadsheets, you don't have Excel ODBC drivers, and you
cannot install any other applications that would export Excel spreadsheet
to a non-proprietary format (like text CSV or tab-separated file), then you
are running out of ways of accessing data stored in that file format.


--
Business Web Solutions
ActiveLink, LLC

https://www.active-link.com/intranet/

[Phil Powell]

Re: Parsing Excel spreadsheet to db

See below - thanx
Phil

"Zurab Davitiani" <agt@mindless.c om> wrote in message
news:%9B7b.2891 $NS1.2574@newss vr25.news.prodi gy.com...[color=blue]
> Phil Powell wrote on Tuesday 09 September 2003 23:34:
>[color=green]
> > I can't install OpenOffice! the setup.exe file does absolutely nothing.[/color][/color]
I[color=blue][color=green]
> > went to www.openoffice.org and downloaded the entire package for Windows
> > 2000, and nothing happens. So I'm back to the drawing board!
> >[/color]
>
> There is one other way. If you have or have access to Excel ODBC drivers,
> you could set up an ODBC data source pointing to the specified Excel file
> with defined tables inside the spreadsheet. Then you could use the ODBC
> connection from PHP to access data from Excel spreadsheet like you would[/color]
to[color=blue]
> other databases.
>
> One thing to keep in mind is that - Excel file has a proprietary format.[/color]
If[color=blue]
> you do not have MS Office with Excel, you don't have COM components that
> access Excel spreadsheets, you don't have Excel ODBC drivers, and you
> cannot install any other applications that would export Excel spreadsheet
> to a non-proprietary format (like text CSV or tab-separated file), then[/color]
you[color=blue]
> are running out of ways of accessing data stored in that file format.
>
>[/color]

Exactly. I don't have Office so I wouldn't have the Excel ODBC drivers. I
wound up sending the file to a colleague who converted it for me into CSV.
Hopefully a one-time solution, other than that, I am powerless to do
anything else.

Phil

[color=blue]
> --
> Business Web Solutions
> ActiveLink, LLC
> www.active-link.com/intranet/[/color]