[개발 공부] 엑셀 데이터 데이터 베이스에 이관하기

이번 포스팅에서는 엑셀로 관리하던 데이터를, 엑셀로만 관리하기 너무 어려운 상황에 이르러서 관리 플랫폼을 웹 애플리케이션으로 개발하는 초기 단계에서, 엑셀의 데이터를 데이터베이스 상으로 이관하는 SQL문을 작성하는 방법에 대해 설명하려고 합니다.

 

보통 엑셀로 관리하던 데이터는 몇백 개에서 몇천 개가 될 수 있습니다.

조금 더 많은 양의 데이터를 이관하는 작업에는 알맞지 않은 방법일 수 있으나, 1000개 정도의 데이터를 옮겨야 했던 저는 좋은 방법이었습니다.

 

※ 해당 포스팅에서 사용되는 데이터와 테이블은 포스팅을 위한 데이터를 직접 만든 것으로 실제 데이터가 아닙니다 ※

 

엑셀로 회원정보를 관리하고 있었고, 엑셀에 회원 정보가 있습니다.

회원 정보에는 아이디, 비밀번호, 주소, 전화번호가 있습니다.

해당 데이터를 담을 데이터베이스에는 USER 테이블이 있으며 칼럼에는 USER_ID, ACCOUNT, PASSWORD, ADDR, PH_NUMBER가 있습니다.

USER_ID: 테이블의 PK로, Auto Increment 옵션이 체크되어있고, 새로운 유저가 추가될 때마다 1씩 증가해 고유 아이디를 부여합니다.

ACCOUNT: 로그인할때 사용하는 아이디입니다.

PASSWORD: 로그인할때 사용하는 비밀번호입니다.

ADDR: 주소입니다.

PH_NUMBER: 전화번호입니다.

 

USER_ID를 제외하고 나머지 칼럼들은 엑셀에 데이터가 있는 칼럼 들입니다.

데이터를 이관하면서 넣어주면 USER_ID의 Not Null과 Auto Increment 속성으로 인해 1부터 1씩 증가하면서 자동으로 채워질 것입니다.

 

그럼 이제 엑셀에서 데이터를 이관하기 위해 SQL문을 만들어 주겠습니다.

그전에 알아야 할 사전 지식이 있는데, 대량의 데이터를 SQL문으로 삽입하려면 아래와 같이 작성해주면 됩니다.

 

INSERT INTO 테이블 이름 ( 칼럼 1, 칼럼 2, 칼럼 3)

VALUES

('데이터 1-1', '데이터 1-2', '데이터 1-3'),

('데이터 2-1', '데이터 2-2', '데이터 2-3'),

... 중략...

('데이터 50-1', '데이터 50-2', '데이터 50-3');

 

간단합니다.

 

그럼 엑셀에서 어떻게 만들어줄까요?

엑셀에서 데이터 오른쪽에 빈칸을 사용해서 다음과 같은 문장을 넣어주면 됩니다.

엑셀의 concantenate함수를 사용해도 되지만, 저는 ="원하는 문장" 식을 사용한 게 더 간단하고 직관적이어서 사용했습니다.

(사실 엑셀에 그렇게 익숙하지가 않았습니다)

 

="('"&A2&"', '"&B2&"', '"&C2&"', '"&D2&"'), "

 

위 문장을 입력하면 다음과 같은 문자열이 됩니다.

 

('A2', 'B2', 'C2', D2'),

 

만약 데이터가 INT형, 숫자이면 작은따옴표(' ')는 생략해도 됩니다.

제가 넣어줄 데이터인 아이디, 비밀번호, 주소, 전화번호 모두 문자열 형태로 되어있기 때문에 작은따옴표(' ')를 모두 사용해줍니다.

제일 마지막에 온점(,)은 아까 봤던 대량의 데이터 INSERT문에서 봤듯이 데이터 뒤에 반점(,)이, 마지막 줄에는 땀 표(;)가 있어야 합니다.

 

문장을 작성했으면, 엑셀에서 해당 칸의 제일 오른쪽 밑에 모서리를 클릭하고, 밑으로 쭉 드래그하면, 해당 형식과 똑같이 문장이 생성됩니다. 이렇게 생성이 됩니다.

그럼 이제 SQL 만들기 쪽에 있는 모든 데이터를 복사해 텍스트 에디터로 가져옵니다.

그리고 제일 앞에 INSERT 문의 나머지 부분을 작성해 줍니다.

마지막에 있는 온점(,)은 땀 표(;) 바꿔줘야 합니다!!

그럼 이제 생성한 SQL문을 데이터베이스의 쿼리 실행문에 복사해서 실행을 해줍니다.

저는 DBeaver툴을 사용했습니다.

그럼 아래 사진과 같이 데이터가 일괄적으로 이관된 것을 확인할 수 있습니다.

 

작은따옴표가 SQL문을 망쳐요!!

사실 이 글을 쓰기 전에 다양한 글을 봤는데, 대부분 비슷한 해결방안을 제공하거나, 좀 더 복잡한 방법을 소개해주곤 했습니다.

제가 이관해야하는 데이터는 와인과 관련된 데이터였는데, 와인이다 보니 제품 명에 작은따옴표(')가 포함되는 경우가 굉장히 많았습니다.

ex) Chaetue De'LeBlanc

 

저 작은 따옴표 하나가 SQL문을 망쳤습니다.

왜냐하면 와인 이름은 문자열이고, 문자열을 감싸는 작은따옴표가 있는데 이름에 포한된 작은따옴표가 인식되어 SQL문이 망가졌습니다.

 

...

('Chaetue De'LeBlanc', ' ...' , '...'),

...

 

제가 노란색으로 표시한 부분을 하나의 데이터로 인식해서 SQL문이 망가지는 문제가 있었습니다.

이를 해결하기 위해 고민을 해보다가 다음과 같은 해결책을 고안해 냈습니다.

1. 작은 따옴표(')를 포함한 와인 이름 데이터는 작은따옴표(' ')로 감싸지 말고 큰따옴표(" ")로 감싸자!

이 해결방법을 적용하기 위해선 일종의 노가다 작업(?)이 필요했습니다.

위에 똑같이 생성했던 SQL문을 생성하고 SQL문을 실행하면 작은따옴표가 포함된 와인 정보를 입력하는 줄마다 에러가 떠서 직접 바꿔줄 수 있었습니다.

('Chaetue De'LeBlanc', ' ...' , '...'),      이 부분을 이렇게 수정하는 거죠    ("Chaetue De'LeBlanc", ' ...' , '...'),  

이렇게 해결하긴 했지만, 만약 데이터가 수만 개 있어서 직접 하지 못하는 경우엔 어떻게 할까요?

이 작업을 하면서 실수도 많이 했었어서 별로 효율적이지 못하다는 생각을 했습니다.

2. 작은따옴표(')를 사전에 제거하자!

엑셀에서 SQL문을 만들기전에, 엑셀에서 작은따옴표(')를 검색하면 와인 이름에 포함된 작은따옴표들만 검색될 것입니다.

이때 해당 작은따옴표들을 모두 두 개의 작은따옴표로 replace 해줍니다.

 

Chaetue De'LeBlanc 이랬던 와인 이름이 Chaetue De''LeBlanc 이렇게 되는것입니다.

 

이 상태에서 SQL문을 만들면 SQL문이 망가지지 않습니다.

('Chaetue De''LeBlanc', '...' , '...'),

이 SQL문은 정상적으로 작은따옴표가 인식되어 데이터가 정상적으로 잘 이관되었습니다.

 

이렇게 직접 개발도 하기 전에 데이터를 이관하는 과정에서 많은 시간을 투자하면서 하나 배워갑니다!