Exel Worksheets within a Workbook

[willpower]

Has anyone any experience of parsing worksheet information into separate tables in a DB.  to give an illustration. I have a single workbook, in which there is a worksheet containing data relevant to some staff members...so worksheet 1 - staff member 1 , worksheet 2 - staff member 2 etc.  I need to separate this info into separate DB tables.  Any advice gratefully received.

 

Thanks

 

Will

[Mchl]

Windows or *nix?

 

[msaz87]

What I've done in the past is saved the excel sheets as a CSV file and then used phpMyAdmin to import it. I haven't dealt with multiple worksheets, so I'm not sure if you would have to condense them to one (assuming all the rows are the same between the staff members) or if you can export it and Excel will do it automatically.

 

If each worksheet is its own table in the DB, then just export each sheet individually.

 

It's pretty simple to do so long as the DB table matches up and you make sure there aren't hiccups like quote marks or apostrophes -- and make sure to change the delimiter in phpMyAdmin's import to a comma, since it's default as a ";".

 

Anywho -- if you're not using phpMyAdmin that's probably not helpful whatsoever.

[willpower]

Thanks so far...

 

I need to write a php script to parse the file without ANY manual input.  Ie this sheet will be ftp'd upstream and a cron job will then run the php script...so ALL csv or other processing will take place internally removing the ability to export each sheet separately...unless ofcourse we can establish how to do this in PHP alone.

 

 

[Mchl]

If you're on Windows you can use COM extension. It's slow (it actually needs to start Excel in background) and has some quirks, but works.

If you're on linux, you'll have to try using a library like PHPExcel. The problem with these libraries is they have problems with large spreadsheets (they essentialy try to load into memory entire spreadsheet at once)