[MySQL] 엑셀(구글 시트) 이용해서 쿼리 만들기

[MySQL] 엑셀(구글 시트) 이용해서 쿼리 만들기

2022. 4. 19. 18:14ㆍWEB Dev/MySQL

728x90

CSV 로 import 하면 데이터베이스에 데이터 연동하는 것은 어렵지 않은데 uft-8 인코딩 문제가 자꾸 걸려서 부득이하게 엑셀 파일을 직접 손 대서 쿼리를 만들게 되었다.

이런 일을 겪는 사람들이 많지는 않겠지만 혹시 코딩이 어려운 분들을 위해 구글 시트의 도움을 받아 쿼리문을 만드는 방법을 소개해 본다.

워크벤치로 하나하나 넣는 것보다 쿼리 만들어서 실행하는 것이 더 빠르기 때문에...

현재 데이터는 구글 시트에 연동되어 남아있는 상태고, 해당 파일을 로컬로 다운받아서 가공해보았는데

이모지가 깨져서 구글 시트에서 바로 쿼리를 작성해보자

우선 테이블은 아래와 같다.

각각의 데이터에는 null 값도 있고 공백 값도 있는데, 백업하는 시트에서는 null 값도 공백으로 받고 있어서 주의가 필요하다.

article_no 같은 경우는 int 형이라서 공백이 들어가면 0이 되어버린다.

사실 프론트에서는 큰 문제는 없지만 그래도 해당 내용들을 맞춰주기 위해 셀을 하나씩 추가해준다.

우선 article_no 의 값이 비어있지 않으면 그대로 추출하고, 비어있으면 null 을 반환하는 IF 함수 수식을 적어준다.

=IF(ISBLANK(B2), "null", B2)

이것을 모든 셀에 적용하면 article_no가 비어있으면 null을 넣어준다.

그리고 link_count라는 셀도 현재 null 값으로 유지시키고 있어 null을 넣어준다.

그리고 created_date 의 경우 YYYY-MM-DD hh:mm 형태의 텍스트가 필요하기 때문에 현재 셀 서식인 날짜를 텍스트로 변경해주어야 한다.

우선 서식을 이용해 YYYY-MM-DD hh:mm 형태를 맞춰준다.

열 전체를 선택한 다음 서식 - 숫자 - 맞춤 날짜 및 시간에 들어가 날짜 형식을 지정해준다.

*시간을 01시 02시 이렇게 0이 붙는 형태로 만들어야 하기 때문에 시간을 클릭해 0이 있는 시간으로 선택해 적용한다.

* : 콜론은 텍스트기 때문에 빼거나 붙일 수 있다!

그리고 다시 열 전체 선택한 후 서식 - 숫자 - 일반 텍스트 를 클릭하면 해당 셀에 있는 내용이 텍스트로 고정된다.

오피스 엑셀에서는 텍스트로 변환하면 소숫점이 나오는 고통을 겪는데 역시 구글 시트가 최고다.

그리고 우측 맨 끝 비어있는 셀을 선택해서 테이블 내의 내용을 쿼리로 만들 수 있도록 함수를 사용해준다.

=CONCATENATE("INSERT INTO 테이블 이름 (컬럼명1, 컬럼명2, 컬럼명3) VALUES ('",str형 셀번호,"', ",int형 셀번호,");")

위와 같이 입력하면 왼쪽에서 셀 번호 내의 정보를 찾아준다.

'",셀번호,"'

이렇게가 한 덩어리인데, ' ' 작은 따옴표가 텍스트형으로 변환해주는 따옴표기 때문에 int 형의 자료를 입력할때는 작은 따옴표를 지워주면 된다.

나의 경우는 이렇게 들어간다.

*article_no와 link_count가 int형이어야 할 때 (null 값 인식)

*B1을 이용해서 C1에 if 문으로 null 인지 아닌지를 체크하고 있기 때문에 B1을 건너뛰고 C1을 선택

=CONCATENATE("INSERT INTO 테이블명 (board_no, article_no, member_id, created_date, content, tags, link_url, link_title, link_count, agree, alarm_agree)\
VALUES ('",A1,"', ",C1,",'",D1,"','",E1,"','",F1,"','",G1,"','",H1,"','",I1,"',",J1,",'",K1,"','",L1,"');")