How can import excel file data in mysql with the help of php.
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>”;
}
/*****************************************************************************/
