读取CSV文件并存储到MySQL数据库
问题描述:
我正在开发一个网站,允许用户从他们的计算机上浏览和选择.csv文件。
网站将读取.csv文件(用逗号分隔的项目)并将所有项目存储到列表或数组中?之后,它将存储到我的数据库中。读取CSV文件并存储到MySQL数据库
请问我该怎么做? 代码片段和参考将非常感谢。
在此先感谢!
答
看到fgetcsv - http://php.net/manual/en/function.fgetcsv.php,这里也有一个很好的例子,但是我不会先将它存储在数组中,直接放到数据库中,这样可以节省内存和额外的循环。
答
希望您使用PHP开发您的网站,并希望将用户CSV文件导入到您的MySQL服务器数据库中。
您可以使用下面的PHP类导入数据: http://www.legend.ws/blog/tips-tricks/csv-php-mysql-import/
假设您已经创建所需的数据库表。
或者,您可以使用MySQLImport实用程序将任何CSV文件导入到您的MySQL数据库中。请参阅http://dev.mysql.com/doc/refman/5.0/en/mysqlimport.html URL以获取有关mySQLImport命令的更多详细信息。
答
这将上传CSV文件并将数据导入指定的表格。
//Import the contents of a CSV file after uploading it
//http://www.bin-co.com/php/scripts/csv_import_export/
//Aruguments : $table - The name of the table the data must be imported to
// $fields - An array of fields that will be used
// $csv_fieldname - The name of the CSV file field
function CSVImport($table, $fields, $csv_fieldname='csv') {
if(!$_FILES[$csv_fieldname]['name']) return;
$handle = fopen($_FILES[$csv_fieldname]['tmp_name'],'r');
if(!$handle) die('Cannot open uploaded file.');
$row_count = 0;
$sql_query = "INSERT INTO $table(". implode(',',$fields) .") VALUES(";
$rows = array();
//Read the file as csv
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
$row_count++;
foreach($data as $key=>$value) {
$data[$key] = "'" . addslashes($value) . "'";
}
$rows[] = implode(",",$data);
}
$sql_query .= implode("),(", $rows);
$sql_query .= ")";
fclose($handle);
if(count($rows)) { //If some recores were found,
//Replace these line with what is appropriate for your DB abstraction layer
mysql_query("TRUNCATE TABLE $table") or die("MySQL Error: " . mysql_error()); //Delete the existing records
mysql_query($sql_query) or die("MySQL Error: " . mysql_error()); // and insert the new ones.
print 'Successfully imported '.$row_count.' record(s)';
} else {
print 'Cannot import data - no records found.';
}
}
有关详细信息,请参考以下链接:http://www.bin-co.com/php/scripts/csv_import_export/
答
CSV。从MySQL到'csv'。从'csv'到MySQL 此脚本将表数据转换为'csv'文件。您可以在另一台计算机中从'csv'文件 重新创建表格。
<?php
// connect to database
require_once '../db_config.php';
$conn = new mysqli ($dbhost, $dbuser, $dbpass, 'olimrefdb');
if ($conn->connect_errno) die ('no db connection');
$tableToConvert = 'mdata';
$csvFileName = $tableToConvert . '_' . date ("Ymd");//will contain 'csv' data
// -----------------------------MySQL table to CSV--------------------------
$tableSchema = $conn->query ("SHOW CREATE TABLE $tableToConvert")->fetch_assoc();
$result = $conn->query ("SELECT * FROM $tableToConvert");
$handle = fopen ($csvFileName.'.csv', 'w');//to store 'csv' data file
$handle_schema = fopen ($csvFileName.'_schema.csv', 'w');//to store table schema
fputcsv ($handle_schema, $tableSchema);
while ($row = $result->fetch_assoc()) {
fputcsv ($handle, $row);
}
fclose ($handle);
fclose ($handle_schema);
// -----------------------------CSV to MySQL table--------------------------
$handle = fopen ($csvFileName.'.csv', 'r');//open 'csv' data file
$handle_schema = fopen ($csvFileName.'_schema.csv', 'r');//open table schema
$tableSchema = fgetcsv ($handle_schema);
$newTableName = "$tableSchema[0]_" . date ("Ymd");
// change table name in schema
$tableSchema [1] = preg_replace ("/$tableSchema[0]/", $newTableName, $tableSchema [1], 1);
$conn->query ("DROP TABLE IF EXISTS $newTableName");
$conn->query ($tableSchema [1]);//CREATE TABLE
// $conn->query ("CREATE TABLE $newTableName LIKE $tableToConvert");//the same, but without using schema
while ($row = fgetcsv ($handle)) {
$data = implode ("','", $row); //convert array to string
if ($conn->query ("INSERT INTO $newTableName values('$data')")) {
} else {
echo '<span style="color:red">Problem:</span> ' . $data . '<br>';
var_dump ($conn->error_list);
fclose ($handle);
exit();
}
}
fclose ($handle);
echo 'Congratulation!!!' . '<br>';