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

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

问题描述

我有一个.csv档案资料，例如

 日期，姓名，电话类型，号码，时间，地址，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 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`detect.csv` 
 INTO TABLE`calldetections` 
 FIELDS TERMINATED BY'。@ mysql_escape_string（ ）。 
`可选择附加的`。@ mysql_escape_string（\）。
`OPTIONALLY ENCLOSED BY`@ mysql_escape_string（\'）。
 ESCAPED BY`。@ mysql_escape_string（\\）。
`LINES TERMINATED BY`。,,, \\r\\\\
。
` IGNORE 1 LINES`
 
。（`date`，`name`，`type`，`number`，`duration`，`addr`，`pin`，`city`，`state `，`country`，`lat`，`log`）; 
 $ res = @mysql_query（$ con，$ sql）;
解决方案

如果你' d do echo（$ sql）; 在执行之前，您会发现查询的语法不正确，原因如下：


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


  2. 绝对不需要调用 mysql_escape_string（）在 FIELDS TERMINATED BY 和<$ c $中指定分隔符c> ENCLOSED BY 和 ESCAPED BY 条款。


  3. 。事实上，在你的情况下，由于没有使用保留字，你把他们全部。


  4. 在CSV文件的第一行末尾，必须 ,,, ，因为您将它们用作行分隔符的一部分。


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


  6. 查看您的样本行不需要 ESCAPED BY 。但是如果你觉得你需要它使用像这样 ESCAPED BY'\\'。


这就是说句法上正确的语句可能如下所示：

  LOAD DATA INFILE'detect.csv'
 INTO TABLE calldetections 
由'，'
终止的字段可选地由''''
' \\ r \\\
'
 IGNORE 1 LINES 
（日期，名称，类型，数量，持续时间，地址，pin，城市，州，国家，纬度，日志）





现在，您需要在加载几个字段时进行转换：


  1. 如果表中的 date 为 datetime 数据类型，那么它需要进行转换，否则会收到错误


    -18-2013 01:53:45 PM'for row'date'in row



  2. 围绕数字字段中的值


  3. 您最有可能想要更改 null字符串文字到实际 NULL 为 addr，pin，city，state，country


  4. 如果持续时间始终以秒为单位，那么您可以提取秒的整数值，并以此方式将其存储在表中，


这是一个有用的语句应该看起来像这样

  LOAD DATA INFILE'detect.csv'
 INTO TABLE calldetections 
 FIELDS TERMINATED BY'，'
可选择由$ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $持续时间，@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'）

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

 
 mysql> LOAD DATA INFILE'/tmp/detection.csv'
  - > INTO TABLE calldetections 
  - > FIELDS TERMINATED BY'，'
  - >可选地由''附加
  - > LINES TERMINATED BY',,, \\\
'
  - > 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'\' ），
  - > duration = 1 * TRIM（TRAILING'Secs'FROM @duration），
  - > addr = NULLIF（@addr，'null'），
  - > pin = （@state，'null'），
  - > city = NULLIF（@city，'null'），
  - > state = > country = NULLIF（@country，'null'）; 
 Query OK，3 rows affected（0.00 sec）
 Records：3 Deleted：0 Skipped：0 Warnings：0 
 
 mysql> select * from calldetections; 
 + --------------------- + --------- + ------------- -  + ------------- + ---------- + ------ + ------ + ------ +  - ------ + --------- + ------ + ------ + 
 |日期|名称|类型|数字|持续时间| addr | pin |城市|状态|国家| lat | log | 
 + --------------------- + --------- + ------------- -  + ------------- + ---------- + ------ + ------ + ------ +  - ------ + --------- + ------ + ------ + 
 | 2013-09-18 13:53:45 |未知|呼出| 123456 | 0 | NULL | NULL | NULL | NULL | NULL | 0.0 | 0.0 | 
 | 2013-09-18 13:54:14 |未知|呼出| 1234567890 | 0 | NULL | NULL | NULL | NULL | NULL | 0.0 | 0.0 | 
 | 2013-09-18 13:54:37 |未知|呼出| 14772580369 | 1 | NULL | NULL | NULL | NULL | NULL | 0.0 | 0.0 | 
 + --------------------- + --------- + ------------- -  + ------------- + ---------- + ------ + ------ + ------ +  - ------ + --------- + ------ + ------ + 
集合中的3行（0.00秒）





最后在php中为 $ sql分配一个查询字符串变量应该像这样

  $ sql =LOAD DATA INFILEdetection.csv'
 INTO TABLE calldetections 
 FIELDS TERMINATED BY'，'
可选地由'\'''附加
由',,, \\r\\\\
'终止的行b $ b 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 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'）;


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

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