XLS : How can I read a non-open file format?

[KimmoA]

GAH! I'm going nuts! My provider of goods refuses to give me any other means of updating my own database but a closed, unknown XLS (Excel) file. Grrr...

So... I obviously don't run Microsoft technologies (Unix, PHP, MySQL, etc...), and therefore have to find a way to read it anyway.

I have found a commercial solution, but that's out of the question. I have also found several free scripts, but they simply don't work. For example, we have "Spreadsheet_Excel_Reader". It works fine for its EXAMPLE file, but when I feed it my actual XLS, it "cannot read it". Ie: it's not working. It's broken.

How the heck am I supposed to read a non-open file format?

[Red_Eye]

You could try open office I think that the speadsheet component can open and edit excel files. But I have never used it so not sure.

[KimmoA]

Maybe I was unclear. This is something that needs to be done automatically with a PHP script.

[Red_Eye]

Sorry, I thought you just wanted to edit it. Could you use csv files, these are compatable with excel and are simple to create from php

[KimmoA]

Please read my original post again :/

[coopster]

Just for clarification, is the file truly an xls format? Or does it just have an .xls extension? Open it in a text editor and see if it is possibly tab-delimited or comma-separated, etc. Just curious ...

[madmac]

If you cannot buy one of the commercial XLS readers for PHP (the open source ones suck and do not work 1/2 the time), then open the file with Excel or Open Office and save it as something that PHP can easily read, like CSV or a Text Tab Delimited file.

Those are your options:
- buy a commercial PHP XLS reader that actually works --or--
- open and resave the file in a format PHP can easily work with.

[KimmoA]

coopster: It's an actual XLS file. At least TextPad opens it in hexadecimal (binary file) mode.

madmac: Well... the idea was to automate it. I've kind of settled with doing a weekly download of their inventory XLS file, manually save it as CVS (tabs and lines) and then run a PHP script to truncate the MySQL table and insert the new data.

I just wish the format was open. I like automated stuff.

[madmac]

>> I just wish the format was open

Excel files are in BIFF format, and Microsoft publishes the format information in their Excel SDK. How do you think all those other programs are able to import XLS files? Most probably don't "hack" it but read it in binary format and convert. This is how the 2 commercial PHP solutions that I know about work. It does take alot of work and knowledge to do this though, which is why you'll find they are not free. One is pretty expensive, but the other is pretty reasonable. If you want to automate it, an $80 investment isn't that much.

You could also read the BIFF format docs and write your own, but IMO, $80 is probably cheaper.

[KimmoA]

"Excel files are in BIFF format, and Microsoft publishes the format information in their Excel SDK. How do you think all those other programs are able to import XLS files?"

Reverse engineering... The only available docs that I know of are made by people other than MS...

[madmac]

Microsoft publishes the format as part of the Excel SDK. They used to also have the docs online, but took them off. A few online places have copies of them posted though...

[edited by: jatar_k at 5:59 pm (utc) on Sep. 14, 2005]

[dmorison]

If OpenOffice will read your Excel file you may be able to roll something using the Scripting framework...

[framework.openoffice.org...]

[jatar_k]

as many solutions were offered, I call this thread done and locked