MySQL 쿼리 결과 엑셀 저장 - MySQL kwoli gyeolgwa egsel jeojang

Question

MySQL에서 테이블의 값을 csv, json로 저장 하여 보관 또는 데이터를 편하게 조회하기 위해 필요할 때가 있습니다.

목차 Show

츄르사려고 코딩하는 코집사입니다.
1. [SQL] MySQL 테이블 엑셀로 내보내는 방법
IT 일기장

SQLGate나 MySQL Workbench 같은 툴에서 손 쉽게 Export 할 수 있지만 툴을 사용하지 못할 경우 아래와 같이 쿼리를 입력하면 됩니다.

쿼리로 Export

위에서 테스트는 바탕화면에 USERS 테이블을 조회하여 USERS.csv 라는 폴더를 생성하는 것입니다.

SELECT *

INTO OUTFILE '저장할 경로/저장할 이름.확장자'

FIELDS TERMINATED BY ','

ENCLOSED BY '"'

ESCAPED BY '\\'

LINES TERMINATED BY '\n'

FROM 저장할 테이블;

이렇게 추출된 csv 파일을 확인할 수 있습니다.

MySQL Workbench에서 Export

툴에서는 위 방법보다 쉽게 나온 결과 값을 툴에서 바로 여러 타입으로 저장할 수 있습니다.

Result Grid 영역에서 Export/Import 쪽 좌측 아이콘을 클릭하면 됩니다.

csv뿐만 아니라 json, sql, xml, txt 등 여러 타입으로 저장할 수 있습니다.

@notepad_jj2

츄르사려고 코딩하는 코집사입니다.

1. [SQL] MySQL 테이블 엑셀로 내보내는 방법

- 아래의 코드와 같이 쿼리문을 작성하면 된다.

SELECT *
INTO OUTFILE "저장할 경로/파일이름.확장자"
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n'
FROM 저장할 테이블;

- MySQL 테이블 엑셀로 내보내는 예제 코드

SELECT *
INTO OUTFILE "C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/b.csv"
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n'
FROM housedeal;

Mysql DB에서 통계 뽑을 일이 있어서 좀 무식한 쿼리를 돌렸는데요

너무 무식해서 그런지 phpmyadmin에서 내보내기 기능이 동작을 안합니다. ㅠㅠ

그래서 콘솔에서 직접 쿼리를 날리고 파일로 저장하는 방법으로 처리했네요.

파일로 저장안하고 그냥 터미널에 나오는 결과를 복사해서 쓰기에는 또 엑셀 처리가 어려워서 csv로 저장하였습니다.

파일로 바로 저장하는 방법은 쿼리문 뒤에 INTO OUTFILE 부분을 붙여 주시면 됩니다.

SELECT * FROM `Table Name` WHERE 조건문 into outfile '/usr/local/test.csv' fields terminated by ',' enclosed by "'"

뒷부분의 FIELDS TERMINATED BY 는 각각의 결과 필드들의 구분자를 콤마(,)로 할지 다른 특수문자등으로 처리할지 결정하는 겁니다.

예를들어 탭(TAB)을 각 필드간 구분자로 설정하려면 Terminated by '\t'로 써주시면 됩니다.

저는 데이타 중간중간에 콤마가 포함된 것들이 있어서 실제로는 탭으로 처리하였습니다.

enclosed by는 데이타 중에 줄바꿈등의 처리가 되어있는 경우 엑셀로 데이터로 가져다 붙이면 셀이 바뀌어 버리는 경우가 있는데요.

한 필드의 데이터로 처리할 수 있도록 각 필드 데이터의 양끝에 쌍따옴표(double quotation)으로 감싸주는 명령입니다.

엑셀이 정말 기능도 많고 업무처리할때는 유용한데 데이터가 많거나 한 경우는 너무 처리하는데 오래걸려서 제 맥북에다가 APM을 설치해서 DB에 데이타를 저장해놓고, 좀 복잡한 결과를 찾아야 하는 경우는 DB 쿼리 -> CSV 저장 -> 엑셀에서 마무리 하고 있는데 꽤 효과적입니다.

엑셀이 뻗어서 다시 돌려야 하는 경우도 없고요.

또 다른 장점은 DB에 있는 데이터를 엑셀의 데이터 연결 기능을 활용해서 DB의 자료를 바로 엑셀로 불러와서 처리도 가능하다는 거겠네요.

이렇게 DB와 엑셀을 같이 연결해서 사용하니 이전에 아예 엄두를 못내거나 하던 작업도 꽤 수월하게 처리할 수 있었습니다.

IT 일기장

여러가지 프로젝트를 해봤던 것 중에 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파일로저장한다.