[Mysql] Excel 데이터를 Table에 Insert 하기

데이터들을 만지다보면, 기존에 Excel로 관리 하던 것을 DB로 이관 해야 할 때 종종 있습니다.

다른 분들도 많이 소개 하였겠지만, 오늘은 제가 자주 사용 하는 방법으로 한번 포스팅을 해보려고 합니다.

 

먼저, 필요한 친구는 바로 Navicat이라는 친구입니다.

Mysql 에서 제공하는 Mysql Workbench가 있긴 하지만, 얘보다는 엑셀쪽 관련 작업은 Navicat쪽이 좀 더 쉽기 때문에 추천드립니다.

 

급하신 분들은 14일 무료버전으로 하셔도 일단 넣을 수 있기 때문에, 그쪽을 이용하시면 되고 아니면 이왕이면 정품..아니면 크랙...이라도..

 

버전은 12버전 기준으로 진행하였습니다.

 

Navicat | Download Navicat | Download Navicat Premium 14-day trial versions for Windows, macOS and Linux

먼저 엑셀로 관리하던 데이터를 DB에서 읽을 수 있도록 맞춰주는 작업이 필요합니다.

 

방법은 여러가지가 있지만 가장 추천드리는 방법은 첫번째 행에 컬럼 명을 적어주시고, 아래쪽에는 데이터를 입력해 주세요.

가장 첫번째 행에 컬럼명을 적고 아래에는 데이터를 적어주는게 좋다.

이런식으로 편집이 끝나고 나비캣만 깔려있다면 준비는 끝났습니다.

 

바로 테이블을 먼저 만들어 봅시다.

저는 이런식으로 코멘트 적는게 나중에 참 도움이 되더라구요..

이런식으로 일단 엑셀과 첫번째 행에 입력한 컬렴명과 동일하게 테이블을 만들어 주시는게 좋습니다.

꼭 그럴 필요가 있냐고 하신다면.. 굳이 필요는 없지만 작업하기가 조금 더 수월합니다.

 

테이블이 만들어 진 후, 테이블을 오른쪽 클릭하여 Import Wizard 선택

테이블을 다 만드셨으면 테이블을 오른쪽 클릭하여 Import Wizard 를 선택합니다.

그럼 아래와 같은 창이 뜨게 됩니다.

 

Excel 파일을 이용할 것이니, Excel file을 눌러주자

CSV, XML, JSON 파일 등등 전부 Mysql에 넣을 수 있도록 도와주는 창이 하나 뜹니다.

다른 포맷들도 상황에 따라서 자주 이용하기 때문에 한번 사용해 보시면 다음부터는 쉽게 작업이 가능할거에요.

 

Add File 버튼을 이용하여, 넣고자 하는 엑셀 파일을 추가합니다.

여러 파일을 넣을 수 있겠지만, 일단 샘플로 아까 만든 엑셀 파일을 선택합니다.

병원 리스트 이기 때문에 제목은 hospital이라고 했습니다.

 

데이터를 추가할 파일과, 시트를 선택합니다.

하나의 엑셀 파일에 여러 시트가 있을 수 있겠죠. 해당 작업을 진행합니다.

저는 그냥 Sheet1에 전부 데이터를 넣어주었으나, 실제로 엑셀로 관리하고 있다면 한 파일에 여러 시트가 존재 할 수 있겠죠?

 

따로 분리 작업을 진행하지 않아도 되며, 직접 시트 선택이 가능합니다. (단, 데이터 형태를 맞춰주는 작업은 진행해 주셔야 됩니다.)

 

옵션을 선택하는 창 입니다.

이 부분 때문에 첫번째 행에 컬럼명(필드명)을 적는 작업을 진행햇습니다.

FileNameRow를 1번째 행, Date Row를 2번째 행 이후로 기본 세팅이 되어있습니다.

 

만약 아래쪽에 필요없는 데이터가 있자면 마지막 행까지 지정해 줄 수 있습니다.

 

데이터 포맷도 지정해 줄 수 있는데 우리나라는 기본적으로 YMD 형태를 사용합니다.

 

나머지도 엑셀 파일에 맞에 편집해 주시면 될 것 같습니다.

엑셀 시트에 날짜 구분자가 /로 되어있으면 /, -로 되어있으면 -등 으로 변경해 주시면 됩니다.

시트와 타겟 테이블을 선택하는 창 입니다.

저는 첫번째 시트를 hospital 테이블에 넣을 것이기 때문에 이렇게 선택해 주었습니다.

 

필드를 매핑하는 작업입니다.

제가 엑셀쪽에는 si, 테이블에는 ci 라고 실수를 한 부분이 있었는데, 이런 부분들을 잡아 줍시다..

컬럼명이 동일하다면 자동으로 매핑 됩니다.

 

키값은 Primary Key에 체크해 주시면 되겠습니다. (보통, 자동으로 들어갑니다)

옵션입니다.

동일한 키값이 존재한다면 Update, 새로 데이터를 추가한다면 Append, 같이 진행하는 바익도 존재 합니다.

필요한 모드를 선택해 주면 되는데, 저희는 이번에는 데이터를 삽입만 진행 할 것이니 Append모드를 선택하고 Continue 버튼을 눌러 줍시다.

준비가 완료 되었습니다.

거의 끝났습니다. Start 버튼을 눌러주면 두구두구두구...

 

0.24초만에 357개의 행을 넣었습니다.

실제로 17만개 데이터를 작업하는데도 5분 이내에 작업된 기억이 있습니다.

다른 툴보다, Navicat이 작업 속도 측면에서도 매우 우수합니다.

 

입력된 데이터를 확인.

마지막으로, 제대로 데이터가 들어갔는지 확인해 주시면 됩니다.

만약 제대로 매핑되지 않은 데이터가 있다면 엑셀에서 해당 부분만 수정해 주신 후 같은 프로세스를 거쳐주시면 쉽게 가능합니다.

 

Mysql Workbench로도 가능은 하지만 속도가 굉장히 느리고, (17만 행의 경우에는 하루를 켜놔도 안 된 기억이 있습니다. 나비캣을 쓰니 바로 되더라구요...) 하는 방법도 훨씬 복잡복잡 합니다.

 

다들 즐거운 개발 되셨으면 좋겠습니다. 오늘 하루도 화이팅!