I tried to search for some plugins to import Excel file into MySQL database, one of them is http://code.google.com/p/php-excel-reader/
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!
I hope it's the same problem as I had: In excel_reader2.php on line 1120, replace
$retstr = ($asciiEncoding) ? $retstr : $this->_encodeUTF16($retstr);
with
$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.
Test the value of $arr[$i][1] and only do your insert if it's not empty
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);
}
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:
Get-Service | Export-Csv -Path c:tempServices.csv -NoTypeInformation
Get-Process | Export-Csv -Path c:tempProcesses.csv -NoTypeInformation
$doc = New-SLDocument -WorkbookName bigxldoc -Path C:temp -PassThru -Verbose
Get-ChildItem -Path C:temp -Filter *.csv |
Import-CSVToSLDocument -WorkBookInstance $doc -AutofitColumns -Verbose
$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
'Sheet1' and Save Document$doc | Remove-SLWorkSheet -WorkSheetName sheet1 -Verbose
$doc | Save-SLDocument -Verbose
This is best plugin with proper documentation and examples
https://github.com/PHPOffice/PHPExcel
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.