csv파일을 mysql에서 로드하기
1. csv파일의 컬럼 수에 맞춰 필드 생성해주기
create table 테이블명(
필드명 필드타입,사이즈,
...
( csv컬럼수에 맞게 미리 테이블을 만들어 놓는다 )
...
);이때 로드되는 데이터타입에 따라 문제가 생길 수 있으므로 알맞은 데이터타입 설정이 필요하다.
2. csv파일을 만들어 놓은 테이블에 로드하기
load data
infile 'csv파일이 위치한 경로/엑셀파일명.csv'
into table 테이블명
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
lines terminated by '\n'
IGNORE 1 ROWS;- fields terminated by : csv파일에서 각각의 필드(칼럼)을 구분하는 기준을 정한다. 즉, ' FIELDS TERMINATED BY ',' '라는 문장을 통해 쉼표를 기준으로 필드를 나눔을 알 수 있다.
- enclosed by : csv파일에서 각각의 필드(칼럼)가 무엇으로 감싸져 있는지 정한다. 즉, ' ENCLOSED BY '"' '라는 문장을 통해 각각의 필드가 "필드명" 이런식으로 감싸져 있음을 알 수 있다.
- lines terminated by : csv파일에서 라인을 구분하는 기준을 정한다. 즉, ' lines terminated by '\n' '라는 문장을 통해 개행문제 \n(엔터)를 기준으로 라인을 나눔을 알 수 있다.
- ignore : csv파일에서 1행은 필드명(칼럼)이다. 즉, 우리가 필요한 실질적인 데이터는 2행부터 이므로 1행은 지운다는 의미로 'ignore 1 rows'를 작성했다.
2.1 에러
이 부분에서 에러가 나는 경우가 있을 수 있다. 몇 가지 상황을 설명해본다.
1. 미리 만들어 놓은 테이블의 필드(컬럼) 수가 csv의 필드 수보다 적을 때
SQL Error [1262] [01000]: Row 1 was truncated; it contained more data than there were input columns'Row 1 was truncated;'이 부분은 그냥 ignore 1 rows가 실행되었다는 뜻이고, 뒤에 ' it contained more data than there were input columns ' 이 부분이 핵심내용이다. 미리 만들어 놓은 테이블의 필드 수가 부족해 csv의 데이터를 넣을 수 없다는 뜻이다.
- 미리 만들어 놓은 테이블의 필드(칼럼) 수가 csv의 필드 수보다 많을 때
SQL Error [1261] [01000]: Row 1 doesn't contain data for all columns' Row 1 doesn't contain data for all columns '라는 부분을 통해 테이블의 1행의 칼럼 수가 csv의 1행 칼럼 수에 비해 부족하다는 것을 알 수 있다.
- 미리 만들어 놓은 테이블의 필드(칼럼)의 타입이 맞지 않을 때
SQL Error [1366] [HY000]: Incorrect integer value: 'Midfielder' for column 'positions' at row 1위 내용은 미리 만들어 놓은 테이블 속 postions라는 필드(칼럼)에 1행 값으로 'Midfielder'라는 문자열이 들어왔는데, 미리 만들어 놓은 테이블 속 positions필드가 integer타입으로 선언되어 있어 문제가 생긴 모습이다.
- csv파일의 경로 설정 문제
SQL Error [1290] [HY000]: The MySQL server is running with the --secure-file-priv option so it cannot execute this statementmysql에서는 파일을 로드할 때 지정된 경로로만 로드할 수 있게 제한하는 설정이 존재한다.
SHOW VARIABLES LIKE 'secure_file%';다음 명령어를 통해 확인해본다. 그럼 보통 'C:\ProgramData\MySQL\MySQL Server 8.0\Uploads' 다음과 같은 경로로 경로 설정이 되어 있을 것이다.(다를 수도 있음) 이때 csv파일을 로드하는 방법은 두 가지일 것이다.
- 지정된 파일경로에 csv파일을 옮기고 로드한다.
- 지정된 파일경로 자체를 없에거나 변경한다
- 해당 설정은 'C:\ProgramData\MySQL\MySQL Server 8.0\Uploads'에 있는 my.ini 파일 속 ' # Secure File Priv.
secure-file-priv="C:/ProgramData/MySQL/MySQL Server 8.0/Uploads" ' 라는 부분에서 해당 경로를 삭제하고 빈 문자열로 남겨놓으면 된다. 또한 mysql서버를 재시작해주어야 해당 변경사항이 적용된다.
만약 아직도 같은 --secure-file-priv option 에러가 난다면 경로를 잘못 입력했을 가능성이 있다. 경로를 그대로 복사해오면 ' C:\ProgramData\MySQL\MySQL Server 8.0\Uploads ' 다음과 같이 ' \ ' 로 각 디렉토리가 구분되어 있겠지만, sql문에 작성할 때는 ' / ' 를 이용해야 한다. ( C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/원하는csv파일명.csv )
