[SQL] PHP - MySQL 데이터베이스로 가져 오기 CSV 파일을 사용하여 LOAD DATA INFILE

PHP - MySQL 데이터베이스로 가져 오기 CSV 파일을 사용하여 LOAD DATA INFILE

나는 그런 .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,,,

나는 데이터베이스에 데이터를 삽입하려면 다음 코드를 사용하고 있습니다

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

그러나 아무것도 삽입하지 않습니다; 실수는 어디에 있습니까?

해결법

1. ==============================

   1.당신은 에코 ($ SQL)를 할 줄 경우; 당신이 그것을 실행하기 전에 당신은 당신의 쿼리의 구문은 다음과 같은 이유에 대한 잘못된 볼 것 :

   당신은 에코 ($ SQL)를 할 줄 경우; 당신이 그것을 실행하기 전에 당신은 당신의 쿼리의 구문은 다음과 같은 이유에 대한 잘못된 볼 것 :

   그 존재는 syntacticly 올바른 문은 다음과 같을 수 말했다

   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)
   

   지금 당신은 당신이 그들을로드하는 동안 꽤 많은 필드를 변환 할 필요가 이럴 :

   그 존재는 명령문의 유용한 버전의 모양은 말했다

   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') 
   

   다음은 내 컴퓨터에 쿼리를 실행 한 결과입니다

   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)
   

   그리고 마지막으로 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') ";
   

2. ==============================

   2.

       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 및 RRP_nl 및 RRP_bl은 CSV가 아닌하지만 우리는 그 삽입 후 계산됩니다.

from https://stackoverflow.com/questions/18915104/php-import-csv-file-to-mysql-database-using-load-data-infile by cc-by-sa and MIT license