PHP - 使用 LOAD DATA INFILE 将 CSV 文件导入 mysql 数据库

Question

提问by chimbu

I have a .csv file data like that

我有一个这样的 .csv 文件数据

Date,Name,Call Type,Number,Duration,Address,PostalCode,City,State,Country,Latitude,Longitude
"Sep-18-2013 01:53:45 PM","Unknown","outgoing call",'123456',"0 Secs","null","null","null","null","null",0.0,0.0,,,
"Sep-18-2013 01:54:14 PM","Unknown","outgoing call",'1234567890',"0 Secs","null","null","null","null","null",0.0,0.0,,,
"Sep-18-2013 01:54:37 PM","Unknown","outgoing call",'14772580369',"1 Secs","null","null","null","null","null",0.0,0.0,,,

and I'm using the following code to insert the data into database

我正在使用以下代码将数据插入数据库

$sql = "LOAD DATA INFILE `detection.csv`
              INTO TABLE `calldetections`
              FIELDS TERMINATED BY '".@mysql_escape_string(",").
             "` OPTIONALLY ENCLOSED BY `".@mysql_escape_string("\"").
             "` OPTIONALLY ENCLOSED BY `".@mysql_escape_string("\'").
             "` ESCAPED BY `".@mysql_escape_string("\").
              "` LINES TERMINATED BY `".",,,\r\n".
             "`IGNORE 1 LINES `"

             ."(`date`,`name`,`type`,`number`,`duration`,`addr`,`pin`,`city`,`state`,`country`,`lat`,`log`)";
      $res = @mysql_query($con,$sql);

but nothing is inserted; where is the mistake?

但没有插入；错误在哪里？

Answer 1

回答by peterm

If you'd do echo($sql);before you execute it you'd see that syntax of your query is incorrect for following reasons:

如果您在执行echo($sql);之前这样做，您会发现查询的语法不正确，原因如下：

Filename should be enclosed in quotes rather than backticks because it's a string literal not an identifier.
There is absolutely no need to call mysql_escape_string()to specify a delimiter in FIELDS TERMINATED BYand ENCLOSED BYand ESCAPED BYclauses.
You overuse backticks. In fact in your case, since there are no reserved words used, you ditch them all. They only add clutter.
At the end of the very first line of your CSV file you have to have,,,because you use them as part of a line delimiter. If you won't do that you'll skip not only first line but also second one that contains data.
You can't use ENCLOSED BYclause more than once. You have to deal with Numberfield in a different way.
Looking at your sample rows IMHO you don't need ESCAPED BY. But if you feel like you need it use it like this ESCAPED BY '\\'.

文件名应该用引号而不是反引号括起来，因为它是字符串文字而不是标识符。
绝对不需要mysql_escape_string()在FIELDS TERMINATED BYandENCLOSED BY和ESCAPED BY子句中指定分隔符。
你过度使用反引号。实际上，在您的情况下，由于没有使用保留字，因此您将它们全部丢弃。他们只会增加混乱。
在 CSV 文件的第一行末尾，您必须拥有，,,,因为您将它们用作行分隔符的一部分。如果你不这样做，你不仅会跳过第一行，还会跳过包含数据的第二行。
您不能ENCLOSED BY多次使用子句。你必须以Number不同的方式处理场。
恕我直言，查看您不需要的示例行ESCAPED BY。但是，如果您觉得需要它，请像这样使用它ESCAPED BY '\\'。

That being said a syntacticly correct statement might look like this

话虽如此，句法正确的语句可能看起来像这样

LOAD DATA INFILE 'detection.csv'
INTO TABLE calldetections
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"' 
LINES TERMINATED BY ',,,\r\n'
IGNORE 1 LINES 
(date, name, type, number, duration, addr, pin, city, state, country, lat, log)

Now IMHO you need to transform quite a few fields while you load them:

现在恕我直言，您需要在加载它们时转换相当多的字段：

if datein your table is of datetimedata type then it needs to be transformed, otherwise you'll get an error
Incorrect datetime value: 'Sep-18-2013 01:53:45 PM' for column 'date' at row
you have to deal with single qoutes around values in Numberfield
you most likely want to change "null"string literal to actual NULLfor addr, pin, city, state, countrycolumns
if duration is always in seconds then you can extract an integer value of seconds and store it that way in your table to be able to easily aggregate duration values later.

如果date您的表中是datetime数据类型，则需要对其进行转换，否则会出现错误
日期时间值不正确：“日期”列的“2013 年 9 月 18 日 01:53:45 PM”在行
你必须处理Number字段值周围的单个 qoutes
您很可能希望将列的"null"字符串文字更改为实际NULL值addr, pin, city, state, country
如果持续时间总是以秒为单位，那么您可以提取秒的整数值并将其以这种方式存储在您的表中，以便以后能够轻松地聚合持续时间值。

That being said a useful version of the statement should look something like this

话虽如此，该声明的有用版本应该看起来像这样

LOAD DATA INFILE 'detection.csv'
INTO TABLE calldetections
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"' 
LINES TERMINATED BY ',,,\r\n'
IGNORE 1 LINES 
(@date, name, type, @number, @duration, @addr, @pin, @city, @state, @country, lat, log)
SET date = STR_TO_DATE(@date, '%b-%d-%Y %h:%i:%s %p'),
    number = TRIM(BOTH '\'' FROM @number),
    duration = 1 * TRIM(TRAILING 'Secs' FROM @duration),
    addr = NULLIF(@addr, 'null'),
    pin  = NULLIF(@pin, 'null'),
    city = NULLIF(@city, 'null'),
    state = NULLIF(@state, 'null'),
    country = NULLIF(@country, 'null')

Below is the result of executing the query on my machine

下面是在我的机器上执行查询的结果

mysql> LOAD DATA INFILE '/tmp/detection.csv'
    -> INTO TABLE calldetections
    -> FIELDS TERMINATED BY ','
    -> OPTIONALLY ENCLOSED BY '"' 
    -> LINES TERMINATED BY ',,,\n'
    -> IGNORE 1 LINES 
    -> (@date, name, type, @number, @duration, @addr, @pin, @city, @state, @country, lat, log)
    -> SET date = STR_TO_DATE(@date, '%b-%d-%Y %h:%i:%s %p'),
    ->     number = TRIM(BOTH '\'' FROM @number),
    ->     duration = 1 * TRIM(TRAILING 'Secs' FROM @duration),
    ->     addr = NULLIF(@addr, 'null'),
    ->     pin  = NULLIF(@pin, 'null'),
    ->     city = NULLIF(@city, 'null'),
    ->     state = NULLIF(@state, 'null'),
    ->     country = NULLIF(@country, 'null');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from calldetections;
+---------------------+---------+---------------+-------------+----------+------+------+------+-------+---------+------+------+
| date                | name    | type          | number      | duration | addr | pin  | city | state | country | lat  | log  |
+---------------------+---------+---------------+-------------+----------+------+------+------+-------+---------+------+------+
| 2013-09-18 13:53:45 | Unknown | outgoing call | 123456      |        0 | NULL | NULL | NULL | NULL  | NULL    | 0.0  | 0.0  |
| 2013-09-18 13:54:14 | Unknown | outgoing call | 1234567890  |        0 | NULL | NULL | NULL | NULL  | NULL    | 0.0  | 0.0  |
| 2013-09-18 13:54:37 | Unknown | outgoing call | 14772580369 |        1 | NULL | NULL | NULL | NULL  | NULL    | 0.0  | 0.0  |
+---------------------+---------+---------------+-------------+----------+------+------+------+-------+---------+------+------+
3 rows in set (0.00 sec)

And finally in php assigning a query string to $sqlvariable should look like this

最后在 php 中将查询字符串分配给$sql变量应该是这样的

$sql = "LOAD DATA INFILE 'detection.csv'
        INTO TABLE calldetections
        FIELDS TERMINATED BY ','
        OPTIONALLY ENCLOSED BY '\"' 
        LINES TERMINATED BY ',,,\r\n'
        IGNORE 1 LINES 
        (@date, name, type, @number, @duration, @addr, @pin, @city, @state, @country, lat, log)
        SET date = STR_TO_DATE(@date, '%b-%d-%Y %h:%i:%s %p'),
            number = TRIM(BOTH '\'' FROM @number),
            duration = 1 * TRIM(TRAILING 'Secs' FROM @duration),
            addr = NULLIF(@addr, 'null'),
            pin  = NULLIF(@pin, 'null'),
            city = NULLIF(@city, 'null'),
            state = NULLIF(@state, 'null'),
            country = NULLIF(@country, 'null') ";

Answer 2

回答by krunal panchal

Insert bulk more than 7000000 record in 1 minutes in database(superfast query with calculation)

1分钟内批量插入超过7000000条记录到数据库中（带计算的超快速查询）

    mysqli_query($cons, '
    LOAD DATA LOCAL INFILE "'.$file.'"
    INTO TABLE tablename
    FIELDS TERMINATED by \',\'
    LINES TERMINATED BY \'\n\'
    IGNORE 1 LINES
    (isbn10,isbn13,price,discount,free_stock,report,report_date)
     SET RRP = IF(discount = 0.00,price-price * 45/100,IF(discount = 0.01,price,IF(discount != 0.00,price-price * discount/100,@RRP))),
         RRP_nl = RRP * 1.44 + 8,
         RRP_bl = RRP * 1.44 + 8,
         ID = NULL
    ')or die(mysqli_error());
    $affected = (int) (mysqli_affected_rows($cons))-1; 
    $log->lwrite('Inventory.CSV to database:'. $affected.' record inserted successfully.');

RRP and RRP_nl and RRP_bl is not in csv but we are calculated that and after insert that.

RRP 和 RRP_nl 和 RRP_bl 不在 csv 中，但我们计算出来并在插入之后。

PHP - 使用 LOAD DATA INFILE 将 CSV 文件导入 mysql 数据库

提问by chimbu

回答by peterm

回答by krunal panchal

Insert bulk more than 7000000 record in 1 minutes in database(superfast query with calculation)

1分钟内批量插入超过7000000条记录到数据库中（带计算的超快速查询）

相关推荐

最近更新

标签

PHP - 使用 LOAD DATA INFILE 将 CSV 文件导入 mysql 数据库

提问by chimbu

回答by peterm

回答by krunal panchal

Insert bulk more than 7000000 record in 1 minutes in database(superfast query with calculation)

1分钟内批量插入超过7000000条记录到数据库中（带计算的超快速查询）

相关推荐

php PHPExcel 阅读器日期格式问题 - rangeToArray

php PHPMailer 仅在 SMTPDebug = true 时发送电子邮件

PHP jQuery json_encode

如何在下一个查询中使用选择选项值作为 PHP 变量？

相关推荐

最近更新

标签