[DB] excel 데이터를 insert문으로 변환하기

콩다영 2024. 6. 12.

728x90

[ Excel 파일에서 SQL 'INSERT'문을 작성하는 법 ]

먼저 엑셀 데이터를 적절한 형식으로 준비하고, 각 셀의 값을 기반으로 SQL 'INSERT'문을 생성해야 합니다.

간단한 예로 'students' 테이블에 대한 'INSERT' 문을 생성하는 방법을 설명하겠습니다.

엑셀에서 SQL 'INSERT' 문 생성

▼ 엑셀 파일의 첫 번째 행은 컬럼 이름이어야 합니다. 두 번째 행부터 데이터가 있어야 합니다.

▼ 엑셀에서 SQL 'INSERT' 문을 생성하는 수식을 사용합니다.

="INSERT INTO students (id, name, age, grade, student_id) VALUES (" & A2 & ", '" & B2 & "', " & C2 & ", '" & D2 & "', '" & E2 & "', , '" & TEXT(F2, "yyyy-mm-dd") & "');"

▼ 각 셀의 Type 에 맞춰서 수식을 입력하고, 아래로 복사하여 각 데이터 행에 대해 SQL 'INSERT'문을 생성합니다.

수식을 작성할 때 DB Tool에서 샘플 SQL문을 뽑아올 수 있습니다.

(필자는 DBeaver를 사용하였습니다.)

테이블 우측 클릭 - SQL 생성 - 원하는 SQL문 선택. (INSERT)

수식 설명

셀의 Type에 따라 다른 형태로 수식을 작성해 줘야 됩니다.

'A2' , 'B2', 'C2', ... 는 엑셀 시트의 셀 주소입니다.
'&' 연산자는 문자열을 연결하는 데 사용됩니다.
작은따옴표(`'`)는 문자열 값을 감싸기 위해 사용됩니다.

엑셀에서 'Date' 타입을 포함한 데이터를 SQL 'INSERT' 문으로 변환할 때는 날짜 형식을 적절히 처리해야 합니다.

일반적으로 SQL에서는 날짜 값을 'YYYY-MM-DD' 형식으로 사용하거나,

특정 데이터 베이스에서는 타임스탬프 형식('YYYY-MM-DD HH:MM:SS')으로 사용할 수 있습니다.

따라서 Date 타입은 '" & TEXT(E2, "yyyy-mm-dd") & "' 로 작성해주면 됩니다.

날짜 형식은 데이터베이스에서 요구하는 형식에 맞게 조정할 수 있습니다.

MySQL에서는 'YYYY-MM-DD' 형식을 사용하고,

Oracle에서는 'TO_DATE('YYYY-MM-DD', 'YYYY-MM-DD')' 와 같은 형식을 사용할 수 있습니다.

추가로,

필자는 id같이 숫자 앞에 0이 붙는 6자리 문자열을 그대로 유지할 때 추가적인 설정이 필요했습니다.

엑셀에서 숫자 앞에 0이 붙는 문자열을 그대로 유지하려면, 셀을 문자열 형식으로 설정해야 합니다. 그렇지 않으면 엑셀은 000012를 12로 인식합니다.

이 문제를 해결하려면, 셀 형식을 텍스트로 변경하고, SQL 'INSERT' 문을 생성할 때도 이를 적절히 처리해야 합니다.

Excel 셀 형식 설정

1. 엑셀에서 해당 셀들을 선택합니다. (필자는 해당 열을 전체 선택했습니다.)

2. 마우스 오른쪽 버튼을 클릭하고 "셀 서식"을 선택합니다.

3. "표시 형식" 탭에서 "텍스트"를 선택하고 "확인"을 클릭합니다.

[확인]을 누르면 해당 셀이 아래와 같이 텍스트로 설정이 되어 문자열을 그대로 유지할 수 있습니다.

위 설정대로 수식을 만들어서 txt파일에 복사하여 SQL문만 뽑아냅니다.

txt파일에 SQL insert문을 DB Tool에서 실행하여 대용량의 데이터를 빠르게 insert 할 수 있습니다 !! :D

이를 통해 엑셀 파일에서 데이터를 쉽게 SQL 'INSERT'문으로 변환할 수 있습니다.