[MySQL] EXCEL to DB insert

💡 지역코드 관리

🧲 다운로드

https://www.code.go.kr/stdcode/regCodeL.do

법정동 코드 전체자료는 .txt로 다운로드가 된다. EXCEL 실행 후 복사 붙여넣기 한다.

많은 데이터 중 현재 존재하는 법정동코드 앞에서 5자리와 00구 까지만 저장할 예정이다.

🔑 데이터 가공

EXCEL 함수를 이용하여 데이터를 가공한다.

5자리를 가져오는 형태는 =LEFT(A2,5)을 사용해서 가져올 수 있고, 00구 형태로 가공하기 위해서는 =LEFT(B2, FIND(" ", B2, FIND(" ",B2)+1))을 사용해서 가져올 수 있다.

🪓 에러발생

이렇게 데이터를 가공한 경우 아래와 같이 에러가 나는 부분을 확인 할 수 있다.

🛠️ 해결방법

띄어쓰기 2번 기준으로하여 왼쪽 데이터를 출력한 것 이기 때문에 값이 제대로 출력되지 않는 지역에 대해서는 =B2 & " "을 사용하여 띄어쓰기를 인위적으로 추가해주어 해결하였다.

🪓 🪓 에러발생

이렇게 정리가 되는 줄 알았는데 아래와 같이 띄어쓰기가 하나도 없는 부분도 에러가 발생하였다.

🛠️ 🛠️ 해결방법

위에 설명했듯이 띄어쓰기 2번 기준으로 왼쪽 데이터를 출력한 것이니 =B2 & " " 두번 띄어쓰기로 해결하였다.

결과는 아래와 같이 나온다.

✂️ 데이터 중복 제거

새로운 시트를 열어 데이터를 붙여넣고 모두 드래그하여 중복 제거를한다.

💎 이제 원하는 데이터 형식으로 DB에 저장될 준비가 되었다.

🚪 테이블 생성

지역번호와 구 이름을 저장한 컬럼을 가진 테이블을 생성한다.

CREATE TABLE `AREA` (
  `AREA_CODE` char(5) NOT NULL,
  `AREA_NAME` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`AREA_CODE`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='지역 코드 관련 테이블';

🪄 INSERT 쿼리문 생성

EXCEL 데이터를 DB로 저장하기 위해서 각 행 마다 INSERT문을 작성한다.

엑셀에서는 아주 유용한 함수인 CONCATENATE 를 제공한다.

=CONCATENATE("INSERT INTO AREA VALUES('"&A1&"', '"&TRIM(B1)&"');")

여기서 중요한 것은 문자열이면 '' 을 고려하여 만들어야 한다.

🪄 EXCEL -> DB 저장

최종적으로 INSERT 문을 모두 복사하여 DB에 저장해주면 된다.

👀 데이터 확인

저장된 데이터를 확인할 수 있다.

---

마무리

엑셀로 DB에 데이터를 대량으로 저장할 수 있다고 알고있었지만 실제로 사용해보는 것은 처음이였다.

엑셀도 어느정도 공부해서 알아두면 많은 도움이 될 것이라고 생각이 들었다.

뿐만 아니라 마이그레이션이나 배치 같은 작업을 할 때 아주 유용하게 사용할 수 있을 것 같다.

---