MySQL for Excel 기능

##. 환경

윈도우에 MySQL을 깔아서 사용중에 엑셀 데이터를 MySQL에 넣을 일이 생겼다.

찾아보니 MySQL 에서 "MySQL for Excel" 이라고 하는 확장기능을 제공하는 것이었다.

사실 이게 별도의 프로그램인가 싶어서 어느 어둠의 경로를 통해 받아야 하나 걱정을 했는데 MySQL에서 기본으로 제공하는 기능이었다. 대박~

근데 이 기능이 어디에 있는지 찾기가 너무 어려웠다는 건 함정.

그렇게 거의 이 기능을 포기하려고 하는 순간 Excel 2010 의 데이터 탭의 Database라는 그룹에 "MySQL for Excel" 버튼이 딱 보이더라는...

m-1

이 버튼을 선택하고 나면 아래 m-2 그림처럼 "MySQL for Excel" 기능이 오른쪽에 활성화가 된다.

m-2

근데 나만 그런진 몰라도 화면의 하단이 조금 잘린다. 아무리 늘려보려 해도 볼 수 가 없었지만 내가 하려는 기능에 문제는 없어서 걍 무시~

접근할 MySQL 정보를 새로 만들어도 되지만 난 로컬에 있는 MySQL을 쓸거라서 기본으로 보이는 커넥션을 선택했다.

그럼 아래 m-3 그림처럼 스키마 정보가 보이고 입력할 db를 선택하면(더블클릭!!) m-4 그림처럼 테이블들이 보인다.

m-4 그림에 보이는 테이블은 내가 미리 엑셀 시트별로 컬럼에 맞춰 생성해 놓은 테이블들이다.

              

m-3                                                                                  m-4

여기까지 오고 나면 MySQL에 데이터를 넣는 방법이 2가지가 있다. 이 2가지 기능은 시트에 있는 입력할 만큼의 데이터를 선택해야만 활성화가 된다. 현재 시트에 있는 모든 데이터를 넣고자 한다면 Ctrl+A 해주시면 되겠다.

1. Export Excel Data to New Table

이 녀석은 말 그대로 현재 시트와 동일한 테이블을 생성하면서 생성된 그 테이블에 엑셀 데이터를 집어 넣는 것이다.

하지만 불행히도 이 기능엔 컬럼의 Datatype에서 BLOB이나 TEXT를 선택할 수가 없었다. 내가 가지고 있는 데이터는 조금 큰 데이터가 있어 Varchar로 커버가 되지 않고 에러가 났다. 친절하게도 BLOB이나 TEXT를 쓰라고 에러메시지를 주긴 하지만 선택하는 옵션엔 없다는 거~ ㅡㅜ

m-5

m-6

그래서 난 이 기능은 포기했다. 하지만 나 처럼 큰 사이즈의 데이터가 없다면 매우 쉽게 엑셀 데이터를 MySQL에 옮길 수 있었다.

2. Append Excel Data to Table

이름에서 알 수 있듯이 이 기능은 생성되어 있는 테이블에 현재 엑셀 데이터를 추가 하는 기능이다.

위에서 내가 미리 테이블을 생성한 이유가 여기에 있다. 물론 생성은 되어 있지만 비어 있는 테이블이다.

테이블 생성하는 쿼리는 위에 m-6에 있는 에러메시지에 나온 쿼리를 활용하면 매우 쉽다. 나 같은 경우엔 "RegValue"의 Datatype만 Varchar에서 TEXT로 바꿔서 테이블을 생성해줬다. (에러메시지의 가이드대로~ ㅎ)

이제 엑셀에서 입력할 데이터 전체를 선택하고 데이터가 들어갈 테이블을 선택해주면 "Append Excel Data to Table" 기능이 활성화 된다. 바로 클릭!!

m-7

그러면 매핑지어 주는 화면이 아래 m-8 처럼 나온다. 테이블을 미리 엑셀과 동일하게 생성했기 때문에 거의 자동매핑으로 해결이 되지만 약간 맞지 않는 경우도 있는 듯 하다. 난 Datetime 맞지 않는다고 자동매핑이 되질 않았다.

m-8

여기까지 왔는데 포기할 순 없었다. 그래서 걍 "Column6"을 선택해서 아래 "RegDate" 위에 주황(?)색 칸으로 드래그를 했다.

그랬더니 녹색으로 바뀌면서 강제로 매핑이 지어졌다. ㅡㅡa

근데 사실은 위 "First Row Contains Column Names"를 선택했으면 깔끔하게 자동매핑으로 모두다 녹색이 되는 거였는데 몰랐다는 거~ ㅎ

즉, 첫 라인은 컬럼헤더로 실제 값이 아니므로 이를 체크해줬어야 했다.

아~ 정말 머나먼 길이었다.

드디어 드디어 테이블에 값이 들어가는 구나 싶었다.

근데 에러가 퉁!! 에러 메시지는 이렇다.

m-9

뭐 잘 모르겠으니 일단 구글링~

대충 내용은 "max_allowed_packet" 사이즈가 작아서 발생하는 에러니 늘려줘라는 것이다.

my.ini 설정 파일에서 "max_allowed_packet" 값을 늘려주면 해결 된다는 군. 오케~

근데 저 파일을 찾을 수가 없었다. 아놔~ ㅡㅡ

약 2시간의 구글링을 통해 이 파일이 MySQL이 설치된 곳이 아닌 다른 곳에 있다는 걸 알게 되었다. 아~ 윈도우 싫다.

C:\ProgramData\MySQL\MySQL Server 5.5\my.ini

C 드라이브 밑에 ProgramData라는 디렉토리가 왜 있는지 이제서야 알게 됨. ㅡㅜ

여튼 저 경로의 파일을 열어서 m-10 그림처럼 1줄을 추가해줬다. 그리고 나서 MySQL 서비스 재기동!!

다시 "Append Excel Data to Table" 기능을 이용한 엑셀 데이터를 MySQL로 이동을 시도했고 성공!!