MySQL Forums
Forum List  »  Data Warehouse

Re: how to upload data from an excel sheet into a database table.....
Posted by: Lars Johansson
Date: May 03, 2008 03:27AM

Sorry I missed the point, this was about upload from Excel to Mysql. In my reply below, I describe how to download from mysql into Excel. Sorry about that.
But I have done some uploading from Excel also.
look at http://www.codeplex.com/PHPExcel, Maarten Balliauw has some cool examples
reading from Excel. I used one example and built me an excel->mysql uploader.
This PHP snippet converts all sheets to CSV files, which subsequently goes into normal sql scripts:

require 'PHPExcel/Reader/Excel2007.php';
include 'PHPExcel/Writer/Excel2007.php';
include 'PHPExcel/Writer/csv.php';
include 'PHPExcel/Reader/csv.php';
$wbname = 'myexcel.xlsx';
if (!file_exists("$wbname")) {
$log->logit('Error',"Excel workbook $wbname missing!");
return FALSE;
}
$objReader = new PHPExcel_Reader_Excel2007;
$objPHPExcel = $objReader->load("$wbname");
$objWriter = new PHPExcel_Writer_CSV($objPHPExcel);
$objWriter->setDelimiter(';');
$objWriter->setEnclosure('');
$objWriter->setLineEnding("\n");
for ($si = $wbsheets[0]; $si <= $wbsheets[1];$si++) {
$objWriter->setSheetIndex($si);
$objWriter->save('excel2csv'. "$si.csv");
}

- - - - - - - - - - - - - - - - - - - - - - - -

Hi,
here is a simple PHP snippet that creates an XLS file (I found it on the net somewhere) (with this I create simple Excel's in Windows/Linux)
while ($row = $result->fetch_row()) {
$rowstr='';
foreach($row as $value) {
if(!isset($value) || $value == "") $value = "\t";
else {
$value = str_replace('"','""', $value);
$value = str_replace('.',',', $value);
$value = '"' . $value . '"' . "\t";
}
$rowstr .= $value;
}
$report .= trim($rowstr)."\n";
}
$report = str_replace("\r", "", $report);
file_put_contents('myexcel.XLS',$report);

If you like to do some real fancy stuff you should look at:
http://www.codeplex.com/PHPExcel
This is great. The man done this, Maarten Balliauw is really THE MAN.

Using Maartens Excel classes I create Excel 2007 sheets in Windows/Linux, easy peasy.

I set up a jobcontroller that takes XML like:

<job name='generate_Excel2007_report' type='sql'>
<sql>
USE @C_DB;
select 'LIST' tab, a.* from @TABPFXLIST a where @SEL1;
select 'PUREQ_CMT' tab, a.* from @TABPFXPUREQ_CMT a where @SEL2;
</sql>
<sqlconverter>
<name>sqlconverter_Excel2007.php</name><c 'using Maarten Balliauw code here'/>
<target>report</target>
</sqlconverter>
</job>
<job name='send_mail' type='sendmail'>
<recipients>kalle.kula@se.busken.com</recipients>
<subject>Project Excel2007 </subject>
<body>Excel attached with all info found on project=@S_PROJ.</body>
<attachment>
<name>project.XLS</name><file>@J_generate_report/report0.XLS</file>
</attachment>
</job>



Edited 2 time(s). Last edit at 05/04/2008 01:11AM by Lars Johansson.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: how to upload data from an excel sheet into a database table.....
36386
May 03, 2008 03:27AM
7107
August 28, 2009 09:43PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.