PHP - 使用 LOAD DATA INFILE 将 CSV 文件导入 mysql 数据库 [英] PHP - Import CSV file to mysql database Using LOAD DATA INFILE

查看：26
本文介绍了PHP - 使用 LOAD DATA INFILE 将 CSV 文件导入 mysql 数据库的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

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

日期、名称、呼叫类型、号码、持续时间、地址、邮政编码、城市、州、国家、纬度、经度"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,,,

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

$sql = "LOAD DATA INFILE `detection.csv`INTO TABLE`调用检测`字段由 '".@mysql_escape_string(",") 终止."` 可选地包含在 `".@mysql_escape_string("\"")."` 可选地包含在 `".@mysql_escape_string("\'")."` ESCAPED BY `".@mysql_escape_string("\\")."` 行以 `" 结尾.",,,,\\r\\n"."`忽略 1 行 `"."(`date`,`name`,`type`,`number`,`duration`,`addr`,`pin`,`city`,`state`,`country`,`lat`,`log`)";$res = @mysql_query($con,$sql);

但没有插入任何内容；错在哪里?

解决方案

如果你在执行之前执行 echo($sql); 你会发现你的查询语法不正确出于以下原因:

  1. 文件名应该用引号而不是反引号括起来，因为它是字符串文字而不是标识符.

  2. 绝对不需要在FIELDS TERMINATED BY和ENCLOSED BY和mysql_escape_string()中指定分隔符code>ESCAED BY 子句.

  3. 你过度使用反引号.实际上，在您的情况下，由于没有使用保留字，因此您将它们全部丢弃.它们只会增加混乱.

  4. 在 CSV 文件的第一行末尾，您必须 ,,, 因为您将它们用作行分隔符的一部分.如果你不这样做，你不仅会跳过第一行，还会跳过包含数据的第二行.

  5. 您不能多次使用 ENCLOSED BY 子句.您必须以不同的方式处理 Number 字段.

  6. 恕我直言，看看您的示例行，您不需要ESCAPED BY.但是，如果您觉得需要它，请像这样使用 ESCAED BY '\\'.

据说一个语法正确的语句可能看起来像这样

加载数据输入文件'detection.csv'INTO TABLE调用检测以 ',' 结尾的字段可选用 '"' 括起来以 ',,,,\r\n' 结尾的行忽略 1 行(日期、名称、类型、数字、持续时间、地址、引脚、城市、州、国家、纬度、日志)

<小时>

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

  1. 如果表中的 date 是 datetime 数据类型，则需要对其进行转换，否则会报错

    <块引用>

    不正确的日期时间值:'Sep-18-2013 01:53:45 PM' 列'date' 在行

  2. 你必须处理 Number 字段中值的单个 qoutes

  3. 您很可能想将 "null" 字符串文字更改为实际的 NULL addr, pin, city, state, country> 列

  4. 如果持续时间总是以秒为单位，那么您可以提取秒的整数值并将其存储在您的表中，以便以后能够轻松地聚合持续时间值.

据说一个有用的语句版本应该是这样的

加载数据输入文件'detection.csv'INTO TABLE调用检测以 ',' 结尾的字段可选用 '"' 括起来以 ',,,,\r\n' 结尾的行忽略 1 行(@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),持续时间 = 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')

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

<前>mysql> 加载数据文件'/tmp/detection.csv'-> INTO TABLE 调用检测-> 以 ',' 结尾的字段-> 可选用 '"' 括起来-> 以 ',,,\n' 结尾的行-> 忽略 1 行-> (@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),-> 持续时间 = 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');查询正常，3 行受影响(0.00 秒)记录:3 删除:0 跳过:0 警告:0mysql> select * from calldetections;+-------------+---------+---------------+-------------+---------+------+------+------+---------+---------+------+------+|日期 |姓名 |类型 |数量 |持续时间 |地址 |针|城市 |状态 |国家 |纬度 |日志 |+-------------+---------+---------------+-------------+---------+------+------+------+---------+---------+------+------+|2013-09-18 13:53:45 |未知 |拨出电话 |123456 |0 |空 |空 |空 |空 |空 |0.0 |0.0 ||2013-09-18 13:54:14 |未知 |拨出电话 |1234567890 |0 |空 |空 |空 |空 |空 |0.0 |0.0 ||2013-09-18 13:54:37 |未知 |拨出电话 |14772580369 |1 |空 |空 |空 |空 |空 |0.0 |0.0 |+-------------+---------+---------------+-------------+---------+------+------+------+---------+---------+------+------+3 行(0.00 秒)

<小时>

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

$sql = "LOAD DATA INFILE 'detection.csv'INTO TABLE调用检测以 ',' 结尾的字段可选用 '\"' 括起来以 ',,,,\\r\\n' 结尾的行忽略 1 行(@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),持续时间 = 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')";

I have a .csv file data like that

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?

解决方案

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

  1. Filename should be enclosed in quotes rather than backticks because it's a string literal not an identifier.

  2. There is absolutely no need to call mysql_escape_string() to specify a delimiter in FIELDS TERMINATED BY and ENCLOSED BY and ESCAPED BY clauses.

  3. You overuse backticks. In fact in your case, since there are no reserved words used, you ditch them all. They only add clutter.

  4. 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.

  5. You can't use ENCLOSED BY clause more than once. You have to deal with Number field in a different way.

  6. 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 '\\'.

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:

  1. if date in your table is of datetime data 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

  2. you have to deal with single qoutes around values in Number field

  3. you most likely want to change "null" string literal to actual NULL for addr, pin, city, state, country columns

  4. 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.

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 $sql variable should look like this

$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') ";

这篇关于PHP - 使用 LOAD DATA INFILE 将 CSV 文件导入 mysql 数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
PHP最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆