473,320 Members | 2,107 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Handling .xls files with PHP - can it be done?

Hi All,

Up until now, I have been importing data from Excel spreadsheets into MySQL
by exporting them to either .csv files, or tab-delimited .txt files, then
using PHP scripts to insert each row. This works fine, but just adds
another step (open xls, save as csv, then import into MySQL). I am
wondering, has there been any work done on handling xls files, natively, in
PHP? The files I work with are very simple; just rows of data divided into
columns. No cells are calculated based on the value of other cells. It is
all text fields, with one field that holds a date value (not sure if Excel
stores that as text or some sort of timestamp?).

I am well aware of all the tools you can use to convert .xls to something
else (e.g., OpenOffice), but I would like to do it *natively* in PHP, or at
least using a program that I can kick off from within PHP. That way I can
upload an .xls file, and let my scripts worry about converting it to
something easily inserted in MySQL.

Any thoughts?

Sincerely,
-Josh
Jul 17 '05 #1
9 8916
Joshua Beall wrote:
Hi All,

Up until now, I have been importing data from Excel spreadsheets into MySQL
by exporting them to either .csv files, or tab-delimited .txt files, then
using PHP scripts to insert each row. This works fine, but just adds
another step (open xls, save as csv, then import into MySQL). I am
wondering, has there been any work done on handling xls files, natively, in
PHP? The files I work with are very simple; just rows of data divided into
columns. No cells are calculated based on the value of other cells. It is
all text fields, with one field that holds a date value (not sure if Excel
stores that as text or some sort of timestamp?).

I am well aware of all the tools you can use to convert .xls to something
else (e.g., OpenOffice), but I would like to do it *natively* in PHP, or at
least using a program that I can kick off from within PHP. That way I can
upload an .xls file, and let my scripts worry about converting it to
something easily inserted in MySQL.

Any thoughts?

Sincerely,
-Josh


good idea! write a php function set for this! we're all looking forward
to use it... ;)

ar.
Jul 17 '05 #2
"Allan Rydberg" <al****@southtech.net> wrote in message
news:cc**********@newshispeed.ch...
Joshua Beall wrote:
good idea! write a php function set for this! we're all looking forward
to use it... ;)


There are actually some commercial solutions for this, but I was hoping for
something Open Source.

There is also a PEAR package that lets you write XLS files, but none (afaik)
that let you read. Hmm.

Any pointers?
Jul 17 '05 #3
"Joshua Beall" <jb****@donotspam.remove.me.heraldic.us> wrote in message
news:yX*****************@nwrddc01.gnilink.net...
"Allan Rydberg" <al****@southtech.net> wrote in message
news:cc**********@newshispeed.ch...
Joshua Beall wrote:
good idea! write a php function set for this! we're all looking forward
to use it... ;)
There are actually some commercial solutions for this, but I was hoping

for something Open Source.

There is also a PEAR package that lets you write XLS files, but none (afaik) that let you read. Hmm.

Any pointers?


It is possible on Windos platforms by using COM objects, but I bet you want
NIX compatiblity don't you?
Jul 17 '05 #4
> It is possible on Windos platforms by using COM objects, but I bet you
want
NIX compatiblity don't you?


You're a mind reader ;-)
Jul 17 '05 #5
In article <VA******************@nwrddc03.gnilink.net>,
"Joshua Beall" <jb****@donotspam.remove.me.heraldic.us> wrote:
Hi All,

Up until now, I have been importing data from Excel spreadsheets into MySQL
by exporting them to either .csv files, or tab-delimited .txt files, then
using PHP scripts to insert each row. This works fine, but just adds
another step (open xls, save as csv, then import into MySQL). I am
wondering, has there been any work done on handling xls files, natively, in
PHP? The files I work with are very simple; just rows of data divided into
columns. No cells are calculated based on the value of other cells. It is
all text fields, with one field that holds a date value (not sure if Excel
stores that as text or some sort of timestamp?).

I am well aware of all the tools you can use to convert .xls to something
else (e.g., OpenOffice), but I would like to do it *natively* in PHP, or at
least using a program that I can kick off from within PHP. That way I can
upload an .xls file, and let my scripts worry about converting it to
something easily inserted in MySQL.

Any thoughts?

Sincerely,
-Josh


You'll have to 'roll your own' here. The various formats of Excel are
documented (Google showed the following):

http://chicago.sourceforge.net/devel/docs/excel
http://sc.openoffice.org/excelfileformat.pdf

The top link has two links to php code to WRITE excel, but they don't
work very well. I found the following to be much better:

http://www.phpclasses.org/browse/package/767.html

None of the code out there does native reading of Excel. You'll have to
do it yourself.

Now I ask you:

How important is this to you and your project?
How much time do you have to spend on this?
What would be the "return on investment" of having the feature?

Get 3-6 months to code this up and build it into php?

--
DeeDee, don't press that button! DeeDee! NO! Dee...

Jul 17 '05 #6
Joshua Beall wrote:
Up until now, I have been importing data from Excel spreadsheets into
MySQL by exporting them to either .csv files, or tab-delimited .txt files,
then using PHP scripts to insert each row.**This*works*fine,*but*just*adds
another step (open xls, save as csv, then import into MySQL).**I*am
wondering, has there been any work done on handling xls files, natively,
in PHP?**The*files*I*work*with*are*very*simple;*just* rows*of*data*divided
into columns.**No*cells*are*calculated*based*on*the*val ue*of*other*cells.*
It*is all text fields, with one field that holds a date value (not sure if
Excel stores that as text or some sort of timestamp?).

I am well aware of all the tools you can use to convert .xls to something
else (e.g., OpenOffice), but I would like to do it natively in PHP, or at
least using a program that I can kick off from within PHP.**That*way*I*can
upload an .xls file, and let my scripts worry about converting it to
something easily inserted in MySQL.


http://pear.php.net/package-info.php...t_Excel_Writer

--
Chris Hope - The Electric Toolbox - http://www.electrictoolbox.com/
Jul 17 '05 #7
Chris Hope wrote:
Joshua Beall wrote:
Up until now, I have been importing data from Excel spreadsheets into
MySQL by exporting them to either .csv files, or tab-delimited .txt
files, then using PHP scripts to insert each row.**This*works*fine,*but
just*adds another step (open xls, save as csv, then import into MySQL).*
I*am wondering, has there been any work done on handling xls files,
natively, in PHP?**The*files*I*work*with*are*very*simple;*just* rows*of
data*divided into columns.**No*cells*are*calculated*based*on*the*val ue*of
other*cells. It*is all text fields, with one field that holds a date
value (not sure if Excel stores that as text or some sort of timestamp?).

I am well aware of all the tools you can use to convert .xls to something
else (e.g., OpenOffice), but I would like to do it natively in PHP, or at
least using a program that I can kick off from within PHP.**That*way*I
can upload an .xls file, and let my scripts worry about converting it to
something easily inserted in MySQL.


http://pear.php.net/package-info.php...t_Excel_Writer


I'm still half asleep. There I was misreading what you wrote and thought you
were trying to write an xls not read one...

--
Chris Hope - The Electric Toolbox - http://www.electrictoolbox.com/
Jul 17 '05 #8
"Chris Hope" <bl*******@electrictoolbox.com> wrote in message
news:10**************@216.128.74.129...
http://pear.php.net/package-info.php...t_Excel_Writer


I'm still half asleep. There I was misreading what you wrote and thought you
were trying to write an xls not read one...

I am actually using that package right now to dump the contents, after
processing, back to an xls that the user can download.

I think, however (As Michael Vilain pointed out in another post), that if it
comes down to either writing my own .xls reader, or simply insisting that
users export to .csv before uploading, the latter is probably the better
solution, due to the narrow scope of the project I am working on. There
will not be more than a handful of people using this tool, and they too
would probably rather it be done now, and they have to export to .csv once
per week to upload to the website. Perhaps more to the point, they would
rather do that than *pay* me to roll my own .xls reader.

So, I guess that is the end of that. Although, if someone knows where I
should be looking, do let me know!

-jb
Jul 17 '05 #9
On Thu, 01 Jul 2004 16:27:10 +0000, Joshua Beall wrote:
"Allan Rydberg" <al****@southtech.net> wrote in message
news:cc**********@newshispeed.ch...
Joshua Beall wrote:
good idea! write a php function set for this! we're all looking forward
to use it... ;)


There are actually some commercial solutions for this, but I was hoping for
something Open Source.

There is also a PEAR package that lets you write XLS files, but none (afaik)
that let you read. Hmm.

Any pointers?


xls2csv

Haven't used it, but it might do what you want by calling it within PHP
with system() or the like.

http://www.45.free.net/~vitus/ice/catdoc/

later...

--
Jeffrey Silverman
je*****@pantsjhu.edu
Drop "pants" to reply by email

Jul 17 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

9
by: Hans-Joachim Widmaier | last post by:
Hi all. Handling files is an extremely frequent task in programming, so most programming languages have an abstraction of the basic files offered by the underlying operating system. This is...
0
by: Kamal Dhingra | last post by:
Hi Guys, I am developing an asp.net application .In error handling , I have 2 web.congif files ,one is in Web(which is the virtual directory) ,other i have created in a folder in Web/Admin. The...
16
by: dudufigueiredo | last post by:
I have one folder containing mp3 files, the folder is: C:\My Shared Folder\Rubber Soul And the files are: 01 drive my car.mp3 02 norwegian wood.mp3 03 you won't see me.mp3 04 nowhere man.mp3...
0
by: Kamal Dhingra | last post by:
Hi Guys, I am developing an asp.net application .In error handling , I have 2 web.congif files ,one is in Web(which is the virtual directory) ,other i have created in a folder in Web/Admin. The...
1
by: ss | last post by:
Hi Can anyone show a sample code where the error handling is done with the logging the errors. What are the best methods of logging the errors in .net perticularly in WEB Application ...
1
by: sona | last post by:
hi all i m using the function SetFilePointer to move the file pointer to a 64-bit offset. i m using it as follows n writting my data but everytime it jumps to the same location for ne offset...
2
by: Brianm27 | last post by:
Ok i am having the worst time ever trying to figure out how to do this project! Please can anyone help lead me into the rite direction! This is what I have to do: 1. Create a file with...
1
by: David C | last post by:
I have an asp.net VB application that gets a filename and tries to display it on a web page. It seems to be truncating the name when the name contains "&" in it. Below is what I am using to grab...
1
by: Mohit | last post by:
Hi All, 1. I have a context menu with some items in it. I have not assigned any key board shortcuts to any menu item. Now when the context menu is open and some key (say 'a') is pressed and if...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.