php 将CSV文件保存到mysql数据库中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11432511/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Save CSV files into mysql database
提问by user1474018
I have a lot of csv files in a directory. With these files, I have to write a script that put their content in the right fields and tables of my database. I am almost beginner in php language : I have found some bits of code on the Internet. It seems to work, but I am stuck at a stage. Some topics are related on this website, but I did not found the ecat problem.
我在一个目录中有很多 csv 文件。使用这些文件，我必须编写一个脚本，将它们的内容放在我数据库的正确字段和表中。我几乎是 php 语言的初学者：我在互联网上找到了一些代码。它似乎有效，但我被困在一个阶段。这个网站上有一些主题是相关的，但我没有发现ecat问题。
I have written a php script that permits to get the path and the name of these files. I managed too to create a table whose name depends of each csv (e.g : file ‘data_1.csv' gives the table data_1.csv in my-sql). All the tables have the three same fields, id, url, value. The last thing to do is to populate these tables, by reading each file and put the values separated by ‘|' character in the right tables. For example, if a line of ‘data_1.csv' is
我编写了一个允许获取这些文件的路径和名称的 php 脚本。我也设法创建了一个名称取决于每个 csv 的表（例如：文件“data_1.csv”在 my-sql 中给出了表 data_1.csv）。所有的表都有三个相同的字段，id、url、value。最后要做的是填充这些表，通过读取每个文件并放置由“|”分隔的值 右表中的字符。例如，如果一行 'data_1.csv' 是
8756|htttp://example.com|something written
8756|http://example.com|写的东西
I would like to get a record in data_1.csv table where 8756 is in id, htttp://example.com in url field, and something written in value field. I have found the way to read and print these csv with fcsvget function. But I do not know how to make these lines go into the SQL database. Could anyone help me on this point?
我想在 data_1.csv 表中获取一条记录，其中 8756 在 id 中，htttp://example.com 在 url 字段中，以及一些写在 value 字段中的内容。我找到了使用 fcsvget 函数读取和打印这些 csv 的方法。但我不知道如何让这些行进入 SQL 数据库。在这一点上有人可以帮助我吗？
Here is my script below
下面是我的脚本
<?php
ini_set('max_execution_time', 300);
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = '';
$conn = mysql_connect($dbhost, $dbuser, $dbpass, true) or die ('Error connecting to mysql');
$dbname = 'test_database';
mysql_select_db($dbname);
$bdd = mysqli_connect($dbhost, $dbuser, $dbpass, $dbname);
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
else {
echo "hello <br>";
}
$dir = 'C:\wamp\www\test';
$imgs = array();
if ($dh = opendir($dir)) {
while (($file = readdir($dh)) !== false) {
if (!is_dir($file) && preg_match("/\.(csv)$/", $file)) {
array_push($imgs, $file);
}
}
closedir($dh);
} else {
die('cannot open ' . $dir);
}
foreach ($imgs as $idx=>$img) {
$class = ($idx == count($imgs) - 1 ? ' class="last"' : '');
$filePath=$dir . '\'. $img;
$file = fopen($filePath, "r") or exit("Unable to open file!");
$nom = 'FILE: ' . $dir . '\'. $img;
echo $nom;
settype($nom, "string");
echo '<br>';
$chemin = '<img src="' . $dir . $img . '" alt="' .
$img . '"' . $class . ' />' . "\n";
echo $chemin;
$file_name = basename($filePath);
$sql = 'CREATE TABLE `' . $file_name . '` (id int(20000), url varchar(15), value TEXT)';
mysql_query($sql,$conn);
$handle = fopen($filePath, 'r');
while (($row = fgetcsv($handle)) !== false) {
foreach ($row as $field) {
echo $field . '<br />';
}
}
fclose($handle);
}
echo ("VERIFY");
for ($i = 1; $i <= 1682; $i++) {
echo ("<br>A : " . $i);
$checkRequest= "select * from movies where movieID='". $i."'";
echo ("<br>". $checkRequest);
if ($result = $mysqli->query($checkRequest)) {
printf("<br> Select ". $i ."returned %d rows.\n", $result->num_rows);
if ($result->num_rows == 0) {
echo ("I : " . $i);
}
$result->close();
}
}
$mysqli->close();
?>
回答by SDC
MySQL provides a wonderful feature that allows you to import a CSV file directly, in a single query.
MySQL 提供了一个很棒的功能，允许您在单个查询中直接导入 CSV 文件。
The SQL command you're looking for is LOAD DATA INFILE
您正在寻找的 SQL 命令是 LOAD DATA INFILE
Manual page here: http://dev.mysql.com/doc/refman/5.1/en/load-data.html
手册页在这里：http: //dev.mysql.com/doc/refman/5.1/en/load-data.html
Quick example:
快速示例：
LOAD DATA INFILE 'fileName'
INTO TABLE tableName
FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(
field1,
field2,
field3,
@variable1,
@variable2,
etc
)
set
(
field4 = concat(@variable1,@variable2)
);
That's a fairly basic example, but it covers most of what you'd want. The manual page gives full details of how to do some very complex stuff with it.
这是一个相当基本的示例，但它涵盖了您想要的大部分内容。手册页提供了如何用它做一些非常复杂的事情的完整细节。
Hope that helps.
希望有帮助。
回答by Adrian
after
后
foreach ($row as $field) {
echo $field . '<br />';
You need to parse the result after ;like:
您需要;像这样解析结果：
$pieces = explode(";", $field);
and then insert every piece into the database
然后将每一块插入到数据库中
$sql = ' INSERT INTO X VALUES ("'.$pieces[0].'","'.$pieces[1].'","'.$pieces[2].'","'.$pieces[3].'","'.$pieces[4].'")';
mysql_query($sql, $conn);
回答by Bit-Man
Also mysqlimportcan be used
也可以使用mysqlimport
private function runImport($host, $username, $password, $schema, $csvFilePath) {
$output = array();
$cmd = "/usr/bin/mysqlimport --host=$host";
$cmd .= ' --fields-terminated-by=\',\' --fields-optionally-enclosed-by=\'"\' ';
$cmd .= ' --user=' . $username . ' --password='. $password;
$cmd .= " $schema $csvFilePath";
exec($cmd, $output, $retVal);
foreach ($output as $line) {
echo "$line\n";
}
echo "\n\nReturn code : $retVal";
}
