User GuideFAQHow to import Excel data to MySQL, SQL Server in PHP or Classic ASP
How to import Excel data to MySQL, SQL Server in PHP or Classic ASP
EasyXLS™ library allows you to import Excel data to SQL tables. The data can be imported from an Excel sheet or from the active Excel sheet. The entire sheet data or only data from ranges of cells can be imported.
EasyXLS can be successfully used to also import large Excel files having big volume of data with fast importing time.
EasyXLS permits you to import Excel files without Excel installed, without OLEDB, without Interop or any other additional software installed.
Step 3: Run PHP/ASP code that imports Excel file to SQL database
The below example shows how to import Excel file to SQL database in PHP and Classic ASP. The Excel will be imported to a list of Excel rows and later, the records can be inserted into the database using SQL syntax.
.NET:// Create an instance of the class that imports Excel files
$workbook = new COM("EasyXLS.ExcelDocument");
// Import Excel file to List
$rows = $workbook->easy_ReadXLSXActiveSheet_AsList("C:\\Samples\\Excel import.xlsx");
// Confirm Excel file importif ($workbook->easy_getError() == "")
{
// Iterate List valuesfor ($rowIndex=0; $rowIndex<$rows->size(); $rowIndex++)
{
$row = $rows->elementAt($rowIndex);
for ($cellIndex=0; $cellIndex<$row->size(); $cellIndex++)
{
echo"At row ".($rowIndex + 1).", column ".($cellIndex + 1).
" the value is '".$row->elementAt($cellIndex);
// SQL syntax to insert cell value into MySQL database: $row->elementAt($cellIndex)
...
}
}
}
else
echo"Error importing Excel file " . $workbook->easy_getError();
Java:// Create an instance of the class that imports Excel files
$workbook = new java("EasyXLS.ExcelDocument");
// Import Excel file to List
$rows = $workbook->easy_ReadXLSXActiveSheet_AsList("C:\\Samples\\Excel import.xlsx");
// Confirm Excel file importif ($workbook->easy_getError() == "")
{
// Iterate List valuesfor ($rowIndex=0; $rowIndex<(int)(string)$rows->size(); $rowIndex++)
{
$row = $rows->elementAt($rowIndex);
for ($cellIndex=0; $cellIndex<(int)(string)$row->size(); $cellIndex++)
{
echo"At row ".($rowIndex + 1).", column ".($cellIndex + 1).
" the value is '".$row->elementAt($cellIndex)."'<br>";
// SQL syntax to insert cell value into MySQL database: $row->elementAt($cellIndex)
...
}
}
}
elseecho"Error importing Excel file " . $workbook->easy_getError();
' Create an instance of the class that imports Excel filesset workbook = Server.CreateObject("EasyXLS.ExcelDocument")
'Import Excel file to Listset rows = workbook.easy_ReadXLSXActiveSheet_AsList("C:\Samples\Excel import.xlsx")
' Confirm Excel file importif workbook.easy_getError() = ""then' Display imported List valuesfor rowIndex = 0 to rows.size() - 1
set row = rows.elementAt(rowIndex)
for cellIndex = 0 to row.size - 1
response.write("At row " & (rowIndex + 1) & ", column " & (cellIndex + 1) &_
" the value is '" & row.elementAt(cellIndex) & "'< br>")
' SQL syntax to insert cell value into MySQL database: row.elementAt(cellIndex)
...
next
next
else
response.Write("Error importing Excel file " & workbook.easy_getError())
end if
Import Excel to MySQL, SQL Server or any other database
Once the Excel data is imported to List, the records can be processed and entered into database using INSERT, UPDATE or other SQL commands specific to SQL Server, MySQL, Oracle, MS Access or any other database.
EasyXLS enables you to import Excel data to SQL database either from the entire sheet or from a range of cells. Importing only a range of cells is a very useful option especially for large Excel files because it reduces the speed of the import process.
In order to import multiple cell ranges at once from Excel sheet, the range parameter must be passed to the method as union of ranges (multiple ranges separated by comma).
All the methods that allow importing Excel to List have parameters that permit importing only ranges of cells.
Import XLSX, XLSM, XLSB and XLS files to MySQL table
