将CSV文件保存到mysql数据库中 [英] Save CSV files into mysql database

问题描述

我在一个目录中有很多csv文件。使用这些文件，我必须编写一个脚本，将其内容放在我的数据库的正确字段和表中。我几乎是PHP语言的初学者：我在互联网上找到了一些位的代码。它似乎工作，但我被困在一个阶段。一些主题在这个网站上相关，但我没有发现ecat问题。

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.

我写了一个php脚本，它允许获取这些文件的路径和名称。我管理也创建一个名称取决于每个csv的表（例如：file'data_1.csv'给出my_store中的表data_1.csv）。所有表都有三个相同的字段，id，url，value。
最后要做的是填充这些表，通过读取每个文件，并将值以|字符分隔在正确的表中。例如，如果一行data_1.csv是

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

8756 | htttp：//example.com |

8756|htttp://example.com|something written

我想在data_1.csv表中获取一个记录，其中8756是在id，htttp：//example.com在url字段，以及写在value字段。
我找到了用fcsvget函数读取和打印这些csv的方法。但我不知道如何使这些行进入SQL数据库。

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?

以下是我的脚本

<?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(); 

?> 

推荐答案

MySQL提供了一个很棒的功能，

MySQL provides a wonderful feature that allows you to import a CSV file directly, in a single query.

您要查找的SQL命令是 LOAD DATA INFILE

The SQL command you're looking for is LOAD DATA INFILE

手动页面： http://dev.mysql.com/doc/refman/5.1/en/load-data.html

快速示例：

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.

希望有帮助。

这篇关于将CSV文件保存到mysql数据库中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！