-
Reading Excel files with PHP
03:56:56 pm on May 7, 2008 | # |
Ok so I just spent the better part of a day trying to parse .xls files with php to create tables in mysql from the spreadsheets. I tried a bunch of different classes, tried export them a .csv files, all with varying levels of success.
This might really only be an answer to my particular problem and set of .xls files, but I had some .xls files with thousands of rows, and it worked successfully on them. I post this in the hopes maybe I can give someone else a little help.
How it works is it globs for a list of *.xls files, and then loops through reading each one, creating the table SQL and INSERT SQL and then writing that to a file. The table is named according to the .xls file name, and the columns are generated via the first row in the file. For any empty columns, it will create a column called emptyColumn(n). You can then take the generated sql file and import it into the db. Feel free to modify the script to directly connect to the db (which would allow you to use mysql_real_escape_string instead of the poorer addslashes (unless you are connected to a db, PHP will cough in its pants if you try to use mysql_real_escape_string).
The reason I didn’t just use fgetcsv is that when I exported the xls file as a csv from Excel, it wouldn’t output a consistent number of rows/columns. So while I might have 30 headers, as it went line by line in the csv file, it might vary from 28 to 30. Which would mean my insert statement would get all jacked up.
The zip file contains the sample import script (that I ran via command line with php 5.2.x), and the excel reader class I found online. Not the most elegant thing I’ve ever made, but hey, it works?
