upload excel files - suggestions

[vj5]

Hi,I want to upload excel files to mysql database using php. Can some one please help and give suggestions on how to implement? Thanks.

[teng84]

this might help read your csv file parse it so you can insert it into your db...

[vj5]

this might help read your csv file parse it so you can insert it into your db...

Thanks, how to convert excel files to csv files. will fgetcsv command convert the excel file to csv file. Sorry, I am not able to understand. Could you explain.

[vj5]

Has anyone done uploading excel files to mysql database. If so, please help. I have found a way to convert excel file to csv file. now I need to upload to mysql using php. Please help...

[justsomeguy]

If you don't know how, check the manual to see how to handle uploaded files.http://www.php.net/manual/en/features.file-upload.phpOnce the file is uploaded, you can use file_get_contents to get the CSV data and store it in the database like you would with any other data. http://www.php.net/manual/en/function.file-get-contents.phpYou can also use this function to read CSV data into an array:http://www.php.net/manual/en/function.fgetcsv.phpThere are also scripts out there that will let you use the Excel file in the native XLS format. Do a Google search for PHP and Excel to find out how to do that.Also.. are you just trying to store the data in the file into a field in the database, or are you trying to export everything in the Excel file as a database structure, so that each row in the Excel file would be a row in a database table, and each column in the Excel file would be a field in the database?

[vj5]

If you don't know how, check the manual to see how to handle uploaded files.http://www.php.net/manual/en/features.file-upload.phpOnce the file is uploaded, you can use file_get_contents to get the CSV data and store it in the database like you would with any other data. http://www.php.net/manual/en/function.file-get-contents.phpYou can also use this function to read CSV data into an array:http://www.php.net/manual/en/function.fgetcsv.phpThere are also scripts out there that will let you use the Excel file in the native XLS format. Do a Google search for PHP and Excel to find out how to do that.Also.. are you just trying to store the data in the file into a field in the database, or are you trying to export everything in the Excel file as a database structure, so that each row in the Excel file would be a row in a database table, and each column in the Excel file would be a field in the database?

Thanks for the information. I am trying to store the data in the file into a field in the database. I have created manually the table which has same number of columns as in the file. Now, I want a php code to convert the excel file into a csv file and insert into the database.

[zppblood]

I would think Microsoft Office Excel can save it as a csv file, go to File -> Save As -> Choose file type and see if you can, because OpenOffice does (free, open-source software that is like Microsoft Office products).

[vj5]

I would think Microsoft Office Excel can save it as a csv file, go to File -> Save As -> Choose file type and see if you can, because OpenOffice does (free, open-source software that is like Microsoft Office products).

Yes, microsoft office does save as csv file but I am more looking for php to do that job on the code instead of manually saving as csv file. Once, that is done, I don't know how to insert that data to the database. Does anyone know a good PHP reader that does this job for free?

[justsomeguy]

Thanks for the information. I am trying to store the data in the file into a field in the database. I have created manually the table which has same number of columns as in the file. Now, I want a php code to convert the excel file into a csv file and insert into the database.

It sounds like you're trying to import the spreadsheet data into a table in the database. Storing the file data into one field means you essentially want to save the file in a database instead of on a disk. That would be this:

$fdata = file_get_contents('/path/to/file');mysql_query("INSERT INTO files (filedata) VALUES ('" . mysql_real_escape_string($fdata) . "')");

Then you could read the file back out of the database and have the user save or download it.But it sounds like you're trying to do the second option:

or are you trying to export everything in the Excel file as a database structure, so that each row in the Excel file would be a row in a database table, and each column in the Excel file would be a field in the database

Since you created a table with the same number of columns as the spreadsheet, it sounds like that's what you're trying to do.But there's no point in having PHP convert an XLS file to a CSV file. Just read the XLS file and use that. Do a Google search for PHP and Excel and you will find things that you can use with PHP to read XLS files. It will typically read things into an array that you can loop through to add them to the database. Using CSV instead of XLS would save you the trouble of needing to use something else to read the file when PHP already has native support for reading CSV data structures.Check the MySQL section of the manual to see how to connect and use the database:http://www.php.net/manual/en/ref.mysql.phpAlso, if you save the file as a CSV file then you can use something like phpMyAdmin to import that into a MySQL database.