[VBA] 엑셀 매크로를 활용해 MySQL 테이블 만들기-2

https://data-so-hard.tistory.com/109

[VBA] 엑셀 매크로를 활용해 MySQL 테이블 만들기-1

 

1편에 이어 2편에서는 본격적으로 엑셀 VBA를 활용해 테이블을 만들어보도록하겠습니다. Excel에서 개발 도구 탭이 보이지 않는다면, 파일 -> 옵션 -> 리본 사용자 지정에서 개발 도구를 체크하여 활성화합니다. 이제 VBA를 작성할 수 있는데 개발 도구 탭을 클릭하고 VISUAL BASIC 아이콘을 선택하거나 Alt + F11를 누르면 VBA 작성창이 생성됩니다. 여기서 삽입 > 모듈을 누르면 코드를 작성할 수 있습니다. 이제 본격적으로 코드를 작성해보겠습니다. 

 

우선 엑셀 파일을 만들어 아래와 같이 만들 테이블의 정보에 대해 입력했습니다. 

테이블 명	test_1
id	varchar(5)
name	varchar(5)
address	varchar(5)
phone	varchar(20)

 

만들 테이블의 이름과 컬럼, 컬럼의 데이터타입에 대해 정보를 입력했습니다. 이제 이를 기반으로 코드를 작성해보겠습니다. Alt + F11를 누르고 vba 창을 열어 코드를 작성해보겠습니다. 

Sub CreateDynamicMySQLTable()
    ' 변수 선언
    Dim conn As Object
    Dim connStr As String
    Dim sql As String
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim tableName As String
    Dim i As Long
    
    ' 현재 시트 설정 
    Set ws = ThisWorkbook.Sheets("Sheet1") ' 시트 이름을 실제 시트 이름으로 변경
    
    ' 테이블 이름 가져오기 (A1 셀에 있다고 가정)
    tableName = ws.Range("B1").Value
    
    ' SQL 쿼리 시작 부분 작성
    sql = "CREATE TABLE " & tableName & " ("
    
    ' 마지막 행 찾기
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    
    ' 컬럼 정보 추가 (A2:Bn에 컬럼 정보가 있다고 가정)
    For i = 2 To lastRow
        sql = sql & ws.Cells(i, 1).Value & " " & ws.Cells(i, 2).Value
        If i < lastRow Then
            sql = sql & ","
        End If
    Next i
    
    ' SQL 쿼리 끝부분
    sql = sql & ");"
    
    ' DSN을 이용한 연결 문자열 설정
    connStr = "DSN=mysql_connection;"
    
    ' MySQL에 연결
    Set conn = CreateObject("ADODB.Connection")
    conn.Open connStr
    
    ' SQL 쿼리 실행
    conn.Execute sql
    
    ' 연결 종료
    conn.Close
    Set conn = Nothing
    
    MsgBox "Table '" & tableName & "' has been created successfully!", vbInformation
End Sub

 

코드 중에 난해한 부분이 있다면 컬럼 정보를 추가하는 반본문 부분일텐데요.  루프는 각 컬럼의 이름과 데이터 타입을 sql 문자열에 추가하여 CREATE TABLE SQL 쿼리를 구성합니다. 구간들을  분할해 살펴보겠습니다.

 

1. For i = 2 To lastRow

• i는 루프의 인덱스 변수로, 2부터 시작하여 lastRow까지 증가합니다.
• 2로 시작하는 이유는 1행은 테이블 이름이 있고, 실제 컬럼 정보는 2행부터 있기 때문입니다.
• lastRow는 데이터가 있는 마지막 행을 가리킵니다.

2. sql = sql & ws.Cells(i, 1).Value & " " & ws.Cells(i, 2).Value

• ws.Cells(i, 1).Value는 시트의 A열에서 i번째 행의 값을 가져옵니다. 즉, 현재 행의 컬럼 이름입니다.
• ws.Cells(i, 2).Value는 시트의 B열에서 i번째 행의 값을 가져옵니다. 즉, 현재 행의 데이터 타입입니다.
• sql = sql & ws.Cells(i, 1).Value & " " & ws.Cells(i, 2).Value는 sql 문자열에 "컬럼 이름 데이터 타입"의 형식으로 문자열을 추가합니다.
  • ws.Cells(2, 1).Value는 "id"가 되고,
  • ws.Cells(2, 2).Value는 "VARCHAR(5)"가 되어,
  • sql에 "id VARCHAR(5)"가 추가됩니다.
• 예를 들어, i = 2일 때:

3. If i < lastRow Then

• 이 조건문은 현재 컬럼이 마지막 컬럼인지 확인합니다.
• 마지막 컬럼이 아닌 경우에만 쉼표(,)를 추가합니다.

4. sql = sql & ","

• 마지막 컬럼이 아니면, sql 문자열에 쉼표(,)를 추가합니다.
• 쉼표는 SQL 구문에서 여러 컬럼을 구분하는 데 사용됩니다.

5. Next i

• i를 증가시켜 다음 행으로 이동하고, 루프를 반복합니다.

실행과정의 예시는 아래와 같습니다. 

 

 

• 첫 번째 루프 (i = 2):
  • sql = "CREATE TABLE test_1 (id VARCHAR(5)"
• 쉼표 추가 (이후 컬럼이 있기 때문):
  • sql = "CREATE TABLE test_1 (id VARCHAR(5),"
• 두 번째 루프 (i = 3):
  • sql = "CREATE TABLE test_1 (id VARCHAR(5), name VARCHAR(5)"
• 쉼표 추가:
  • sql = "CREATE TABLE test_1 (id VARCHAR(5), name VARCHAR(5),"
• 세 번째 루프 (i = 4):
  • sql = "CREATE TABLE test_1 (id VARCHAR(5), name VARCHAR(5), address VARCHAR(5)"
• 쉼표 추가:
  • sql = "CREATE TABLE test_1 (id VARCHAR(5), name VARCHAR(5), address VARCHAR(5),"
• 네 번째 루프 (i = 5):
  • sql = "CREATE TABLE test_1 (id VARCHAR(5), name VARCHAR(5), address VARCHAR(5), phone VARCHAR(20)"

루프가 끝나면 최종적으로 아래와 같은 쿼리가 완성됩니다. 

CREATE TABLE test_1 (id VARCHAR(5), name VARCHAR(5), address VARCHAR(5), phone VARCHAR(20));

 

 

이 구문이 MySQL에 전달되어 test_1이라는 이름의 테이블이 생성됩니다. 테이블에는 id, name, address, phone이라는 네 개의 컬럼이 있으며, 각각의 데이터 타입은 VARCHAR(5), VARCHAR(5), VARCHAR(5), VARCHAR(20)입니다.

 

이렇게 루프를 통해 동적으로 SQL 구문을 생성함으로써, 엑셀 시트에 입력된 어떤 테이블 구조에도 맞는 SQL 쿼리를 자동으로 생성할 수 있게 됩니다.

 

해당 코드는 F5를 누르면 실행되고, MySQL에 테이블이 잘 구축됐는지 확인해보겠습니다. 

 

확인결과, 테이블이 생성된 것을 확인했습니다.