Viewed   81 times

I tried to search for some plugins to import Excel file into MySQL database, one of them is

The tool is so powerful that it displays the entire excel content into html.

However, I think I just need to read the Excel file and extract the contents, for example, into an array, and then write a SQL statement for entering into the database.

Would there be any good codes and packages? Thanks!



This is best plugin with proper documentation and examples

Plus point: you can ask for help in its discussion forum and you will get response within a day from the author itself, really impressive.

Wednesday, December 21, 2022

I hope it's the same problem as I had: In excel_reader2.php on line 1120, replace

$retstr = ($asciiEncoding) ? $retstr : $this->_encodeUTF16($retstr);


$retstr = ($asciiEncoding) ? iconv('cp1250', 'utf-8', $retstr) : $this->_encodeUTF16($retstr);

That should fix it, however I suggest you use a different excel reader, such as PHPExcel to avoid problems like these.
Note that you need iconv extension enabled on the server.

Friday, November 4, 2022

Test the value of $arr[$i][1] and only do your insert if it's not empty

Saturday, December 3, 2022

You need to use PHP-Excel-Reader

For that you have to add excel reader library in your Vendor folder like this vendorsphp-excel-reader and yourdatafile.xls should be under webroot folder

function index(){
App::import('Vendor', 'php-excel-reader/excel_reader2');
$data = new Spreadsheet_Excel_Reader('yourdatafile.xls', true);

Saturday, September 3, 2022

if you work with the openxml format *.xlsx you can manipulate excel documents without having to have office installed.

There are a few powershell modules(powertools,epplus etc) you can use to accomplish what you want to do. Here is a solution using a powershell wrapper that I wrote for Spreadsheetlight:

Create some csv files

Get-Service | Export-Csv -Path c:tempServices.csv -NoTypeInformation
Get-Process | Export-Csv -Path c:tempProcesses.csv -NoTypeInformation

Create a new excel document to hold csv data

$doc = New-SLDocument -WorkbookName bigxldoc -Path C:temp -PassThru -Verbose

Import the csv files into excel

Get-ChildItem -Path C:temp -Filter *.csv | 
  Import-CSVToSLDocument -WorkBookInstance $doc  -AutofitColumns -Verbose 

Save the document

$doc | Save-SLDocument -Verbose

Note: this will only work with *.xlsx format and not *.xls


By default the data import starts at Row2, Column2 which can be changed easily:

Get-ChildItem -Path C:temp -Filter *.csv | 
    Import-CSVToSLDocument -WorkBookInstance $doc -ImportStartCell A1 -AutofitColumns -Verbose

Remove the default worksheet 'Sheet1' and Save Document

$doc | Remove-SLWorkSheet -WorkSheetName sheet1 -Verbose
$doc | Save-SLDocument -Verbose
Saturday, November 12, 2022
Only authorized users can answer the search term. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :