How can import excel file data in mysql
with the help of php.
To import excel data into php-mysql
records first create a table with required fields. Make database
connection. Open excel file and read columns one by one and store in
variables.
First Method******************* import excel data to php code****************/
$handle = fopen("Book1.csv", "r");
while (($data = fgetcsv($handle)) !== FALSE) {
$num = count($data);
$row;
echo "INSERT into importing(text,number)values('$data[0]','$data[1]')";
echo "<br>";
}
/************************************************************************/
/***Second Method************ import excel data to php code and mysql***************/
$handle = fopen("Book1.csv", "r");
$fields=array('category','datatype','date','value');
$table='test';
$sql_query = "INSERT INTO $table(". implode(',',$fields) .") VALUES(";
while (($data = fgetcsv($handle)) !== FALSE) {
foreach($data as $key=>$value) {
$data[$key] = "'" . addslashes($value) . "'";
}
$rows[] = implode(",",$data);
}
$sql_query .= implode("),(", $rows);
$sql_query .= ")";
echo $sql_query;
/****************************************************************************/
/***Third Method*********** import excel data to php code and mysql****************/
require_once 'Excel/reader.php'; // http://code.google.com/p/php-excel-reader/downloads/list
$data = new Spreadsheet_Excel_Reader();
$data->setOutputEncoding('CP1251');
$data->read('Book1.xls');
for ($x = 2; $x<=count($data->sheets[0]["cells"]); $x++) {
$name = $data->sheets[0]["cells"][$x][1];
$extension = $data->sheets[0]["cells"][$x][2];
$email = $data->sheets[0]["cells"][$x][3];
$sql = "INSERT INTO mytable (name,extension,email) VALUES ('$name',$extension,'$email')";
echo $sql."\n";
echo "<br>";
}
/*****************************************************************************/
