Re: [PHP] import spreadsheet
Richard Lynch wrote: On Fri, July 27, 2007 4:01 am, Angelo Zanetti wrote: Does anyone have any resources or links as to how to import a spreadsheet but it might have different number of columns and many sheets (those tab things at the bottom). What I thought of doing was creating a table that has 10 fields and if the file thats being imported only has 4 fields then the remaining six fields are blank. So basically my script must dynamically take the format (which changes) and try save it in the database in a semi standard format. If anyone thinks its possible or not please advise. Any help or advice would be greatly appreciated. I didn't actually import multiple sheets, but the tool I used returned an array "sheets" and the 0-th element was the only sheet in there, so I suspect a second sheet would have been the next element... It's a PECL package up on sourceforge: http://sourceforge.net/projects/phpexcelreader/ As far as columns and rows goes, it just built an array the right size to hold everything in the spreadsheet. Though if you've got a stray "space" in column ZZZ row 65535, then I suppose you could be in trouble... It's got a bug as of a couple weeks ago where it tries to 'include' a file that doesn't exist... Change that to include the similarly-named file that's actually there and bob's your uncle. YMMV PS Watch out for those serial killer dates... :-) I blogged a little bit about this here: http://richardlynch.blogspot.com/2007/07/php-microsoft-excel-reader-and-serial.html hi guys. Thanks to those that responded, I will check out all the responses! Much appreciated! regards Angelo -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] import spreadsheet
On Fri, July 27, 2007 4:01 am, Angelo Zanetti wrote: > Does anyone have any resources or links as to how to import a > spreadsheet but it might have different number of columns and many > sheets (those tab things at the bottom). > > What I thought of doing was creating a table that has 10 fields and if > the file thats being imported only has 4 fields then the remaining six > fields are blank. > > So basically my script must dynamically take the format (which > changes) > and try save it in the database in a semi standard format. > > If anyone thinks its possible or not please advise. Any help or advice > would be greatly appreciated. I didn't actually import multiple sheets, but the tool I used returned an array "sheets" and the 0-th element was the only sheet in there, so I suspect a second sheet would have been the next element... It's a PECL package up on sourceforge: http://sourceforge.net/projects/phpexcelreader/ As far as columns and rows goes, it just built an array the right size to hold everything in the spreadsheet. Though if you've got a stray "space" in column ZZZ row 65535, then I suppose you could be in trouble... It's got a bug as of a couple weeks ago where it tries to 'include' a file that doesn't exist... Change that to include the similarly-named file that's actually there and bob's your uncle. YMMV PS Watch out for those serial killer dates... :-) I blogged a little bit about this here: http://richardlynch.blogspot.com/2007/07/php-microsoft-excel-reader-and-serial.html -- Some people have a "gift" link here. Know what I want? I want you to buy a CD from some indie artist. http://cdbaby.com/browse/from/lynch Yeah, I get a buck. So? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] import spreadsheet
[snip] Does anyone have any resources or links as to how to import a spreadsheet but it might have different number of columns and many sheets (those tab things at the bottom). What I thought of doing was creating a table that has 10 fields and if the file thats being imported only has 4 fields then the remaining six fields are blank. So basically my script must dynamically take the format (which changes) and try save it in the database in a semi standard format. If anyone thinks its possible or not please advise. Any help or advice would be greatly appreciated. [/snip] Sure, it is possible but which data goes in which columns? A four column spreadsheet does not have the same textual context unless you make everyone start with the same 10 column spreadsheet and place their data in the right columns. If you are trying to save individual spreadsheets as data that are independent of the data in the other spreadsheets then that sort of eliminates the need for a database, but it can be done. You have to have a column or two that would contain the spreadsheet identifier and a date. Then your table could have as many columns (generically headed) for data. spreadsheetName date col1 col2 col3 col4 ... sales 2007-07-25 data data data data ... sales 2007-07-25 data data data data ... mileage 2007-07-25 data data mileage 2007-07-25 data data For each spreadsheet you would have to insert a row to hold column headers for that spreadsheet, so you may need a third column to designate what kind of row type (header, data) this is for that spreadsheet. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] import spreadsheet
Angelo Zanetti wrote: Hi guys Does anyone have any resources or links as to how to import a spreadsheet but it might have different number of columns and many sheets (those tab things at the bottom). What I thought of doing was creating a table that has 10 fields and if the file thats being imported only has 4 fields then the remaining six fields are blank. So basically my script must dynamically take the format (which changes) and try save it in the database in a semi standard format. If you're trying to be completely generic, why not have a table like: cells { id, - Auto increment, auto assign by DB file, - The file the sheet came from, if you're going to store more than one sheet, - The name of the sheet the cell is on column, - The column the cell is in row, - The row the cell is in value - The value or formula of the cell primary key(id), key (file, sheet, column, row), key(file, sheet), etc. } Then you can write your importer to go through every sheet/row/column and add cells to your database for each. Obviously, you don't bother to add empty cells. Once this is done, you can do things like: Get a cell directly: select * from cells where file='f' and sheet='x' and column='y' and row=z Get an entire column: select * from cells where file='f' and sheet='x' and row=z Get an entire row: select * from cells where file='f' and sheet='x' and column='y' Get a list of the available columns in a sheet: select distinct column from cells where file='f' and sheet='x' order by column Get a list of the sheets in use: select distinct sheet from cells where file='f' and order by sheet And so forth. The nice thing about this format is that it makes it really easy to do interesting things like write a Web front-end to spreadsheet data. You could have a little form that queries the list of files, and lets the user pick which they want. Then, for that file, you get the list of sheets. Once they select those, you get a list of all rows/columns in the sheet and use it to set up your table, and populate your grid with cells. With the above data structure, that's a few minutes' work. Regards, Chad -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php