[ php ] csv 파일 테이블로 import 하기 ( LOAD DATA INFILE )

디렉토리에 들어있는 csv 파일들을 DB에 저장할 일이 생겼다.

파일 개수가 많아 php로 파일 리스트를 읽어 테이블들로 일괄 저장하려고 한다.

 

mysqlimport도 생각해봤는데 LOAD DATA INFILE 문 처리속도가 더 빠르다고 한다.

 

일단 DB에 접속하자.

 

$conn = mysqli_connect('localhost', 'userid', 'password','dbname');
if (mysqli_connect_errno($conn)) 
     echo "데이터베이스 연결 실패: " . mysqli_connect_error();
else
 echo "connection success";

 

csv파일을 import할 테이블도 생성하자.

기존 csv파일에 없는 idx필드를 생성해 primary key값으로 설정하고 자동 증가 옵션을 주었다.

 

$sql = "CREATE TABLE `테이블이름` (
     `idx` INT(11) NOT NULL AUTO_INCREMENT,
     PRIMARY KEY(idx),
     `날짜` INT(8) NOT NULL,
     `가격` INT(5) NOT NULL,
     ...

`시간` INT(4) NOT NULL 
 )";

 

if(mysqli_query($conn, $sql))

    echo "Table created successfully"; 

else
    echo "Error creating table:".mysqli_error($conn);
  

 

LOAD DATA INFILE 구문 규칙은 아래와 같다.

 

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [CHARACTER SET charset_name]
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]
    [IGNORE number LINES]
    [(col_name_or_user_var,...)]
    [SET col_name = expr,...]

^[각주:1]

 

기본적으로 csv 파일 이름과 테이블 이름만 입력해도 사용 가능하다.

이럴 경우 나머지 옵션들은 기본 옵션으로 실행되게 된다.

 

파일 이름은 경로와 함께 나타낼 수 있으며 상대 경로 절대 경로 다 사용 가능하다.

기본적으로 현재 데이터베이스가 있는 디렉토리가 기본 경로가 된다.

csv파일이 데이터베이스와 다른 경로에 있기 때문에 절대 경로로 나타낸다.

필드가 , 로 구분되어 있기 때문에 FIELDS TERMINATED BY ',' 옵션을 추가했다.

 

참고로 기본 필드 값은 아래와 같다.

 

FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'
LINES TERMINATED BY '\n' STARTING BY ''

 

 

응용프로그램에서 생성되는 많은 csv파일이 아래와 같은 포맷을 사용한다고 하니 참고하자.

 

LOAD DATA INFILE 'data.txt' INTO TABLE tbl_name
  FIELDS TERMINATED BY ',' ENCLOSED BY '"'
  LINES TERMINATED BY '\r\n'
  IGNORE 1 LINES;

 

 

csv파일의 첫 라인에는 필드 이름이 있었기 때문에 IGNORE 1 LINES"; 옵션도 추가했다.

IGNORE N LINES 옵션은 파일 N번째 라인부터 테이블로 저장하게 한다.

 

만약 테이블의 필드와 csv파일의 필드가 정확히 일치하면 그냥 아래와 같이 적어주면 된다.

 

$sql = "LOAD DATA INFILE '/var/lib/mysql-files/파일이름.csv'
     INTO TABLE `테이블이름`
     FIELDS TERMINATED BY ','
     IGNORE 1 LINES";
  

 

간혹 파일이 존재하는데 파일이 없다는 에러가 나올 수 있다. 

mysql> LOAD DATA INFILE '/home/changelist.csv' INTO TABLE changelist

    ->   FIELDS TERMINATED BY '\t'

    ->   IGNORE 1 LINES;

ERROR 29 (HY000): File '/home/changelist.csv' not found (Errcode: 13)

그럴 경우는 주로 권한의 문제로 파일이 있는 폴더나 파일 소유자를 mysql로 변경하면 해결 된다. 

chown mysql:mysql changelist.csv

 

그런데 나는 인덱스 필드를 추가했기 때문에 ( ) 옵션으로 필드들 이름을 하나하나 적어주기로 한다(...)

여기서 필드들 이름 순서는 파일에 나타나는 필드  순서와 동일해야 한다.

$sql = "LOAD DATA INFILE '/var/www/html/파일이름.csv'
     INTO TABLE `테이블이름`
     FIELDS TERMINATED BY ','
     IGNORE 1 LINES"

     (`날짜`,`가격`,... ,`시간`)";

 

if(mysqli_query($conn, $sql))
    echo "LOAD DATA INFILE successfully"; 
else
    echo "Error LOAD DATA INFILE:".mysqli_error($conn);

 

$conn->close();

 

필드 명까지 다 입력하고 php 파일을 실행하자 테이블이 생성되고 csv 파일이 테이블로 잘 변환된 것을 확인할 수 있었다 : )

 

만약 테이블에 null 값을 입력해야 한다면 (그리고 csv 파일에는 빈 입력값으로 들어있다면) 

빈 입력값을 \N으로 입력해야 한다. 

MySQL manual says:

When reading data with LOAD DATA INFILE, empty or missing columns are updated with ''. If you want a NULL value in a column, you should use \N in the data file. The literal word “NULL” may also be used under some circumstances.

So you need to replace the blanks with \N like this:

예시: 

1,2,3,4,5

1,2,3,\N,5

1,2,3

 

 

1. 출처:http://dev.mysql.com/doc/refman/5.1/en/load-data.html [본문으로]