I am using php to develop my website and I need to be able to browse for and upload an Excel sheet. I then need to store the information in an SQL database.
I realise this is an relatively open question but I am have looked for information on how to do this and have not got very far. Storing the data in the SQL database is fine but I am not sure how to upload the spreadsheet such that it can be read by the website.
If anyone can point me in the right direction I would be grateful.
This question contains a lot of topics, I'll try a short summary on what has to be taken care of in order to solve your issue at hand.
(1) File Upload
First you have to make an upload available. Basically you have at least two components.
Example HTML code:
<form action="your_upload_script.php" method="post" enctype="multipart/form-data">
Your XLS file: <input type="file" name="xlsfile" size="50" />
<input type="submit" name="submit" value="Submit" />
</form>
A simple, but solid example php code can be found here.
Keep in mind, uploading files can have a few more issues, p.e. regulated upload sizes and such.
(2) Parsing the Excel file
Now that your file is uploaded, you have to parse it. I recommend using PHPExcel, which is a ...
... Project providing a set of classes for the PHP programming language, which allow you to write to and read from different spreadsheet file formats, like Excel (BIFF) .xls, Excel 2007 (OfficeOpenXML) .xlsx, CSV, Libre/OpenOffice Calc .ods, Gnumeric, PDF, HTML, ...
(3) Storing the values in your tables
While or after parsing the spreadsheets you'll eventually have to store it in your tables. Whatever you do, please refrain from using the old mysql connector in php, since this one is old and deprecated. Use mysqli or PDO instead.
Again, this is only the short story. You'll eventually encounter more challenges and issues on the way.
You could export your file as CSV, upload it and parse it with fgetcsv(). From the PHP manual:
$row = 1;
if (($fh = fopen("excel.csv", "r")) !== FALSE) {
while (($data = fgetcsv($handle, 1000, ";")) !== FALSE) {
$num = count($data);
$row++;
for ($c=0; $c < $num; $c++) {
//make query and insert into database
}
}
fclose($handle);
}
You can read the uploaded excel file using PHPExcel
Download Link : https://github.com/PHPOffice/PHPExcel
First, your Excel file has to be in CSV format, otherwise it will be a lot more difficult to insert data in your database (you have to parse it with PhpExcel for example).
If your Excel file is CSV, then you have to use LOAD DATA INFILE.
You can see an example here : https://stackoverflow.com/a/10897669/1788704
LOAD DATA INFILEoption - which have you decided to use? And what's your question about it?