importing xls file

Anon

hey guys,

does anyone have any insight on importing data from an .xls file? i'm trying to import the data directly from an xls file and insert certain fields into a MySQL database (fields 1,2,3 and 4 of each row, leaving out field 0). it cannot be a csv file because the client doesn't know how to convert an xls to a csv in excel.

-upload xls file
-parse file and extract fields
-insert data into MySQL

i've read several things on exporting data to an .xls file, but that doesn't help me.

thanks in advance!!

ugh82

This is NOT a very good solution but it is working.
You can make a Visual Basic program and
with ado connection connect to the Excel
file and to the mysql database server (use odbc drivers) and do whatever you want.

I have make a program like that to synchronize my SQL Server with MySQL and i don't have any problems.

=)

rrijnders

You can save the xls file as a CSV file (Comma Delimited) and then use the fgetcsv() function

http://www.phpbuilder.com/manual/function.fgetcsv.php

to read in the file line by line and process it any which way you want to.

-- Rich

bdbush

Without all the constraints you have listed, this is a very simple problem. Typically, you would just import the .xls file to Access and go from there. Also, you mentioned the client doesn't know how to create a CSV file. Just do a "Save As..." and CSV is an option.

Anyway, on to your real question. There are no "Great" php-based Excel reader/writers out there. There are a couple of basic "biff" ones, but they will not do what you want.

I suggest you use a Perl module called WriteExcel/ParseExcel. In this case, you will want the ParseExcel.

I am not sure what the exact output of your file will look like, but it will be in text format so that you can read it.

You could call the Perl script using an exec() call from php when you want to parse a file.

Ultimately, this is a very complex problem that would be better served by an article on this site.

--brian

robertjackson

When faced with this problem, we wrote an Excel macro that saved the desired worksheet to an anonymous ftp location as a .csv file. Of course, the macro could flip through all the worksheets in a workbook and upload each one. The macro also assigned a filename based on the tab of the worksheet and the date of upload.

The macro solved both the file conversion problem and the upload problem.

We then use a crontab to look for uploaded files and parse the files as Richard suggested.

Hope that helps.

Rob

Anon

wow. great information everyone. i'll look into everything.

thanks for your help!!