MariaDB / DATA / CSV 파일 데이터 가져오는 방법

LOAD DATA INFILE

MariaDB의 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,...]

간단한 예제로 CSV 파일의 데이터를 가져오는 방법을 알아보겠습니다.

기본

• 다음과 같은 내용을 가진 ab.csv 파일을 root 폴더 아래에 만듭니다.(다른 폴더에 만들고 가져오기 코드에서 경로를 수정해도 됩니다.)

A001,B001
A002,B002
A003,B003

• MariaDB에서 csv_import라는 테이블을 만듭니다.

create table csv_import ( A varchar(100), B varchar(100) );

• 다음과 같이 명령하여 ab.csv의 데이터를 가져옵니다.
• terminated by ',' : 쉼표를 기준으로 열을 구분한다는 뜻입니다.
• lines terminated by '\n' : 줄바꿈을 기준으로 행을 구분한다는 뜻입니다.

load data local infile '/root/ab.csv' into table csv_import fields terminated by ',' lines terminated by '\n';

• 잘 가져왔는지 확인합니다.

select * from csv_import;
+------+------+
| A    | B    |
+------+------+
| A001 | B001 |
| A002 | B002 |
| A003 | B003 |
+------+------+

데이터에 쉼표가 있는 경우

• CSV는 comma-separated values의 약자입니다. 즉, 쉼표로 데이터를 구분합니다. 그런데 데이터 자체에 쉼표가 있으면 문제가 생깁니다.
• 예를 들어 CSV의 내용이 다음과 같은 경우...

A00,1,B001
A002,B002
A003,B003

• 경고가 만들어지고...

load data local infile '/root/ab.csv' into table csv_import fields terminated by ',' lines terminated by '\n';
Query OK, 3 rows affected, 1 warning (0.00 sec)
Records: 3 Deleted: 0 Skipped: 0 Warnings: 1

• 원하는 결과가 나오지 않습니다.

select * from csv_import;
+------+------+
| A    | B    |
+------+------+
| A00  | 1    |
| A002 | B002 |
| A003 | B003 |
+------+------+

방법 1

• 쉼표를 포함한 데이터를 큰 따옴표로 감쌉니다.

"A00,1",B001
A002,B002
A003,B003

• enclosed by '"'를 추가하여 데이터를 가져옵니다.

load data local infile '/root/ab.csv' into table csv_import fields terminated by ',' enclosed by '"' lines terminated by '\n';

방법 2

• 열을 구분하는 쉼표를 다른 문자로 변경하고...

A00,1|B001
A002|B002
A003|B003

• fields terminated by의 문자열을 변경하여 가져옵니다.

load data local infile '/root/ab.csv' into table csv_import fields terminated by '|' lines terminated by '\n';

일부 열만 추가하는 경우

• 테이블에 열은 여러 개 있는데, CSV 파일에 열이 하나라면...

A001
A002
A003

• 파일을 가져올 때 경고가 뜨고...

load data local infile '/root/ab.csv' into table csv_import fields terminated by ',' lines terminated by '\n';
Query OK, 3 rows affected, 3 warnings (0.00 sec)
Records: 3 Deleted: 0 Skipped: 0 Warnings: 3

• 첫번째 열에 데이터가 들어갑니다.

select * from csv_import;
+------+------+
| A    | B    |
+------+------+
| A001 | NULL |
| A002 | NULL |
| A003 | NULL |
+------+------+

• 만약 테이블의 B열에 데이터를 넣고 싶다면, 쉼표를 추가하거나...

,B001
,B002
,B003

select * from csv_import;
+------+------+
| A    | B    |
+------+------+
|      | B001 |
|      | B002 |
|      | B003 |
+------+------+

• 쉼표 없이 만든 후...

B001
B002
B003

• 데이터가 들어갈 열을 지정합니다.
• CSV 파일의 첫번째 열의 이름을 @csv1로 정한 후, B열에 넣으라는 뜻입니다.

load data local infile '/root/ab.csv' into table csv_import fields terminated by ',' lines terminated by '\n' ( @csv1 ) set B = @csv1;

select * from csv_import;
+------+------+
| A    | B    |
+------+------+
| NULL | B001 |
| NULL | B002 |
| NULL | B003 |
+------+------+