[MYSQL] DB 데이터를 엑셀 파일로 저장

여러가지 프로젝트를 해봤던 것 중에 8월 31일까지 우수과학도서에 대한 데이터를

엑셀파일로 보내달라는 요청건이 있었다. CSV를 이용해서 할 수 있는 방법이 있는데

 

... 잘 안보이지만 이 구문만 익혀두면 쉽다

INTO OUTFILE '/var/lib/mysql-files/myplist.csv' -- csv 파일을 출력하고자 하는 경로
CHARACTER SET utf8 -- 언어 타입 설정
FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"' -- '|'로 구분하고 구분지운 문자열 타입들을 ""로 감싼다
ESCAPED BY '\\' -- '\\'일 경우 끝
LINES TERMINATED BY '\n' -- 줄바꿈은 "\n"으로 표시

SELECT 다음에 나오는 ':::' 추가, 그리고 조회하고자 하는 구문 뒤에 붙여주면 된다

데이터와 겹치지않는 구분자를 선택해서 큰따옴표("")로 감싸서 엑셀 파일 틀에 맞게 정리할거다

 

저 구문이 실행이 안되는 경우가 난 2가지가 있었다.

1. The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

2. Access denied for user 'root'@'localhost' (using password : YES)

 

# 1번 해결책

SHOW VARIABLES LIKE "secure_file_priv"; 
SELECT @@GLOBAL.secure_file_priv;

 

둘 중 하나 실행하고 변수 상태를 확인해보면 경로가 존재한다. 해당 경로에만 import가 가능하다는 의미다.

그래서 INTO OUTFILE에 설정돼있는 경로로 저장을 하던지, 설정된 경로를 삭제하던지 둘 중 하나 선택하면 된다.

 

# 2번 해결책 

GRANT FILE ON *.* TO 'root'@'localhost';

권한 거부됐다는 소리니까 현재 권한 확인하고 저거 실행 한 다음 아까 권한으로 원복해주면 된다.

(root 계정에 모든 권한 부여라 원복은 필수다)

 

해결됐으면 저 쿼리를 실행한다. 해당 경로에 파일이 출력되는데, 우선 다운받아준다.

파일을 열면 한글이 엄청 깨지는데 CSV 파일 받을때 ANSI 언어로 출력해야 된다.
메모장으로 연결해서 ANSI로 저장해준다. 경고문 뜨는데 무시하고 "확인" 눌러주면 된다.

여기까지해서 문제없이 출력되면 상관없는데, html 데이터들이 포함돼있는 경우면 구분자를 넣어줘야된다.
흔하게 포함돼있는 구분자는 피하기위해 나는 SQL문에서 ":::"를 추가했다.
html 데이터들이 포함돼있어서 데이터가 제대로 안나오는 경우, 에디터 프로그램이 필요하다.

 

에디터 프로그램 다운받았다면 csv 파일에서 메모장으로 연결해서 이 내용들 모두 복사해서 다른이름으로 txt파일로 저장한다.

 

저장한 txt파일을 EditPlus로 열어서 바꾸기 (Ctrl + H) 버튼을 눌러주고 순서대로 시행해준다.

1. 개행 문자는 모두 제거해준다. ( \n -> 공백 )
2. 내가 넣었던 구분자와 "|"를 개행 문자로 바꿔준다 (":::"\| -> \n ) 

그러면 컬럼의 구분자는 모두 "|"로 구분되게 설정된다.

 

이제 txt파일을 csv파일로 저장하고 엑셀을 열어준다.

엑셀에서 데이터 > 텍스트/CSV 클릭해서 아까 저장했던 CSV 파일을 열어준다.

다음과 같은 화면이 뜨는데 "로드" 눌러주면 컬럼별로 데이터가 정상적으로 출력되는걸 볼수있다.

그리고 컬럼 이름 맞춰주면 끝