[엑셀 VBA] MySQL 02. Range -> 배열 -> INSERT

작성자
dra****
작성일
2021-09-16 12:52
조회
202

SQL에서 INSERT를 하려면 필드명과 값을 따로 모두 써 주어야 합니다.
아래는 INSERTUPDATE의 기본 문법입니다.

INSERT INTO 테이블명(필드명1, 필드명2, ...) VALUES (1, 값2, ..) 
 
UPDATE 테이블명 SET 필드명1=1, 필드명2=2, ...

그런데 필드의 수가 길면 보통 노가다가 아닙니다.
게다가 보통 Table의 id는 자동증감을 해 놓은 상태이기에 필드명을 안 쓰는 것도 불가능하고.... 젠장... 해보신 분들은 이 말이 무슨 말인지 이해하실 것입니다. 어떤 분들은 각 라인별로 INSERT문을 자동 채우기 형태로 해서 셀에 모두 채워 사용하시는 분도 계시더군요.

그렇다고 데이터가 많은 상황에서 각각의 셀들을 읽어서 필드명과 값을 Loop를 돌면서 하면 효율성이 떨어지고.. 그렇게 하는 방법도 있습니다.

여하튼 모든 코딩은 동적으로 해야 한다는 것이 정답입니다. Static으로 하면 유지 보수에 시간이 많이 들어값니다. 결정적으로 내가 코딩한 것도 시간이 지나면 모두 잊어 버린다는 것. 그래서 처음부터 유지 보수 할 생각하지 말고 코딩하는 것이 중요합니다.

ADODB.Recordset에 있는 Method 중에 .AddNew Fields, Values 라는 기능이 있습니다.
Fields, Values는 배열이며 여기에 필드명들과 값들을 배열로 전달하면 바로 INSERT가 됩니다. 업데이트도 마찬가지로 .Update Fields, Values 를 통해서 하면 바로 Update가 됩니다.

그럼 DB로 이식하지 않은 데이터들을 (ListObject)로 변환한 후 통채로 넘기고, 그 표의 row를 각각 배열로 넘기면 빠른 시간에 MySQL로 이식이 됩니다.

엑셀에서 웹 서버에 직접 올리는데, 100개의 데이터를 처리하는 데 2초 정도 걸리더군요. 그럼 대충 자신의 자료 양에 따라 걸리는 시간이 예측이 가능 할 것입니다. 아님 localhost에 올린후 웹서버로 이식하면 시간을 더 절약할 수 있을 지 모르겠습니다. 그 작업하는게 시간이 더 걸릴 듯.

굳이 표로 바꿀 필요없이 범위 잡아서 넘겨도 상관 없습니만, 앞으로 포스팅 할 내용들이 파워쿼리 관련된 내용이라 일부러 표로 설명하려 합니다. ListObject, QueryTable 등을 다룰 수 있어야 자동화의 개념을 편하게 이용할 수 있습니다.

서론이 길었습니다. 시작해 보지요.

1. range가 배열이라는 점을 먼저 살펴 보겠습니다. range는 기본적으로 2차원 배열(row, column)입니다.

이 점에 착안해서 아래와 같이 코딩 해보 면

Sub ArrayToRange()
   Dim arrH()
   Dim arrV()
 
   arrH = Array(1, 2, 3, 4, 5) 'row
   Range("A1:E1").Value = arrH
 
   arrV = Application.Transpose(arrH) 'column
   Range("A1:A5").Value = arrV
End Sub

다음과 같은 결과가 나옵니다.

그림에서 보는 바와 같이 배열을 만들고 Range에 대입하면 그 값이 들어 갑니다. 그럼 그 반대로 Range의 값들을 배열에 담을 수 있다는 말이겠지요. 아래와 같이 담으면 2차원 배열로 변수에 값이 담깁니다.

arrH = Range("A1:E5")

 

2. row에 있는 데이터들은 모두 횡적 배열이기에, 저는 이게 불편하더 군요. 그래서 Application.Transpose()함수를 이용해 종적 배열로 바꾸고, 2차원 배열 마저 1차원으로 바꾸고, id(Auto Increase) 부분을 포함 할지 말지 Option을 추가해서 1차원 배열로 변환하는 함수를 하나 만들었습니다.

' // rng: 종적 배열로 만들 범위
' // tpose: 횡적 배열을 종적 배열로 변환 <- 보통 row로 데이터가 있기 때문에 defaul로 True로 설정 해 놓음
' // includeIndex: 첫행이 id or index이면 포함 할 지 여부
' // 보통 첫행이 DB의 id 부분이라 default로 Flase값을 설정 해 놓음
 
Public Function RangeToArray(rng As Range, Optional tpose As Boolean = True, Optional includeIndex = False)
   Dim arr()
   Dim arrResult()
   Dim i As Long, index As Long, endIndex As Long
   arr = IIf(tpose, Application.transpose(rng.Value), rng.Value)
 
'   Debug.Print "rng LBound Value Columns: " & LBound(rng.Value, 1) : 헤더 포함 행의 개수
'   Debug.Print "rng UBound Value Columns: " & UBound(rng.Value, 2) : 칼럼의 개수
 
   If includeIndex = False Then
      ReDim arrResult(1 To UBound(arr, 1) - 1)
      index = 2
      endIndex = UBound(arr, 1) - 1
   Else
      ReDim arrResult(1 To UBound(arr, 1))
     index = 1
      endIndex = UBound(arr, 1)
   End If
 
   Dim tmp
   For i = 1 To endIndex
      tmp = VBA.Trim(arr(index, 1))
      If tmp = "" Then tmp = Null
      arrResult(i) = tmp
Continue:
      index = index + 1
   Next i
   RangeToArray = arrResult
End Function

 

3. 이제 1차원 배열로 변환된 row를 SQL문을 통해 INSERT 합니다.

3.1 먼저 표를 매개변수로 넘겨 줍니다. 그러면 표에 데이터가 얼마나 있던, 칼럼이 몇개이든 동적으로 처리할 수 있습니다.

그림에서 필드명이 있는 영역은 C9:L9 입니다. 여기에 "id"가 데이터 베이스 Primary Key이면서 자동 증감입니다.
따라서 이 부분은 DB에 INSERT를 해서는 안됩니다. 그래서 EID부터 INPUT_TIME까지만 가져와야 합니다.

마지막에 modified는 DB 필드가 아니고 나중에 업데이트할때 체크용 칼럼입니다. 표 범위로 잡혀 있지 않습니다.

표 범위는 id~INPUT_TIME 입니다.

Public Function Table_Insert_Update(tbName As String, TB As ListObject) As Boolean
   'Dim tbName As String:   tbName = "ys_tbca"
   Dim action As String, strSQL As String
   Dim cntRows As Long, cntCols As Long, i As Long
   Dim result As Boolean
   Dim id As Variant
   Dim ckUpdate
 
   'DB Fields 이름 담아 둠
   Dim arrFields() As Variant, arrValues() As Variant
   arrFields = RangeToArray(TB.Range.rows(1))
 
   '테이블 총 행/열의 갯수 구함
   cntRows = TB.DataBodyRange.rows.Count
   cntCols = TB.DataBodyRange.Columns.Count
 
   '테이블 루프를 돌면서 삽입 | 갱신을 수행함
   On Error GoTo Error
   Dim rs As New ADODB.Recordset
   rs.ActiveConnection = Conn
   rs.CursorType = adOpenDynamic
   rs.LockType = adLockOptimistic
   rs.Open tbName, , , , adCmdTable
 
   For i = 1 To cntRows
      id = TB.DataBodyRange.rows(i).Cells(1, 1).Value
      action = IIf(IsEmpty(id) Or id = "", "INSERT", "UPDATE")
      arrValues = RangeToArray(TB.DataBodyRange.rows(i))
      Debug.Print "Row No. : " & i
      If action = "INSERT" Then
         rs.AddNew arrFields, arrValues
         rs.Update
 
      ElseIf action = "UPDATE" Then
         ckUpdate = TB.DataBodyRange(i, cntCols).Offset(0, 1).Value
         If ckUpdate = False Or VBA.UCase(ckUpdate) = "FALSE" Then GoTo Continue
 
         Dim criteria As String
         criteria = "id = " & id
 
         rs.MoveLast
         rs.Find criteria, , adSearchBackward
         rs.Update arrFields, arrValues
      End If
Continue:
   Next i
 
   rs.Close
   Set rs = Nothing
 
   Table_Insert_Update = True
   Exit Function
 
Error:
   MsgBox Err.Description
   Table_Insert_Update = False   
End Function

→ tbName 은 DB에 있는 Table 이름입니다.

→ TB는 표(ListObject)개체를 전달하는 것입니다.

→ 표의 첫번째 행이 필드명이기 때문에 .rows(1)을 넘겨 배열로 받아 옵니다. RangeToArray()는 배열로 변환하는 함수

arrFields = RangeToArray(TB.Range.rows(1))

→ Recordset 개체의 속성 중

    rs.Open tbName, , , , adCmdTable

요렇게 생긴 부분이 있는데, tbName 자리에 보통 SQL문을 집어 넣는게 일반적인데, Table로 직접 작업할 때

이렇게 해주면 매우 편리합니다.

→ id 셀에 값이 있으면 UPDATE, 없으면 INSERT

id = TB.DataBodyRange.rows(i).Cells(1, 1).Value

→ 행의 값들을 배열로 변수에 담기

arrValues = RangeToArray(TB.DataBodyRange.rows(i))

→ modified 칼럼에 값이 FALSE이면 수정이 안된 것이므로 건너뛰고, TRUE이면 UPDATE 수행

ckUpdate = TB.DataBodyRange(i, cntCols).Offset(0, 1).Value

→ 오늘의 주인공인 DB에 INSERT하는 것입니다.

rs.AddNew arrFields, arrValues
rs.Update

INSERT 하는 경우라면 rs.Update를 루프가 다 돌고 나서 해 주면 되는데, 중간에 갱신 하는 부분이 있어 어쩔수 없이 바로 바로 갱신을 수행합니다. rs.Update를 해야 실제로 DB에 주입됩니다.

다음에는 파워쿼리로 조회하는 부분을 생성하고, 동적 조회가 가능하도록 하는 부분을 포스팅 해보려 합니다.

전체 5

  • 2021-09-17 15:26

    뭔가 SQL의 INSERT야 껌이지 하고 들어왔다가

    밑에 코드 보고 스크롤을 쭉 내렸네요 ㅎㅎㅎ;;; 나중에 정독해야겠습니다


    • 2021-09-17 20:14

      @엑셀은즐거워 님 엑셀의 범위를 넘어 또 다른 세상을 보았고, 보여 드리려 준비 중입니다. ㅎㅎㅎ


  • 2021-09-20 10:20

    어디서부터 배워야 저런걸 할 수 있나요


    • 2021-09-20 11:35

      @저 산너머에는 님 조그만한 제 사업체 반복 단순 입력에 짜증이 나서 자동화 하려고 하다 보니 저리 되었습니다.

      엑셀 함수로는 자동화가 안되니, 올해 1월~3월까지 엑셀 VBA만 죽어라 공부하고, 한 2개월 자동화 하면서 시행착오를 겪다 보니 이리 되었습니다. 그러다 오빠두 강좌를 보게 되었고, 감동과 자극을 동시에 받아, 예전 먹고 살기 힘들어 못했던 것을, 시간이 생긴 지금에서야 구현하고 있습니다.

      엑셀은 제가 안다고 착각했던 가장 어려운 분야 중 하나입니다. ㅠㅠ


  • 2021-11-14 15:15

    감사합니다..!


전체 48
번호 제목 작성자 작성일 추천 조회
23999
New [엑셀 VBA] Snippet - Public IP, Local IP, Mac Address 구하기 (4)
dra**** | 2021.11.25 | 추천 1 | 조회 32
dra**** 2021.11.25 1 32
23794
[엑셀 VBA] Snippet - 변수를 clipboard로 복사하기 (5)
dra**** | 2021.11.21 | 추천 2 | 조회 54
dra**** 2021.11.21 2 54
23337
HTML 라이브러리 사용하기 귀찮을 때, 사용하는 파싱함수 (getElementsByTag, getAttribute) (6)
트로피 오빠두엑셀 | 2021.11.09 | 추천 0 | 조회 70
트로피 오빠두엑셀 2021.11.09 0 70
23142
[잡설] 엑셀을 이용한 웹 자동화: SEO 이야기 (10)
dra**** | 2021.11.04 | 추천 0 | 조회 153
dra**** 2021.11.04 0 153
22802
[잡설] 웹 서버에서 내보내는 엑셀 파일 및 데이터 이야기 (6)
dra**** | 2021.10.25 | 추천 0 | 조회 111
dra**** 2021.10.25 0 111
22325
[엑셀 VBA] 파워쿼리 자동화 4. CRUD (3)
dra**** | 2021.10.12 | 추천 1 | 조회 149
dra**** 2021.10.12 1 149
22161
[엑셀 VBA] 파워쿼리 자동화 3. MySQL Class (8)
dra**** | 2021.10.07 | 추천 0 | 조회 170
dra**** 2021.10.07 0 170
21839
[엑셀 VBA] 파워쿼리 자동화 2. 자동화 실습 (8)
dra**** | 2021.09.28 | 추천 0 | 조회 224
dra**** 2021.09.28 0 224
21764
[엑셀 VBA] 개체 Reference - QueryTable Object 첨부파일 (2)
dra**** | 2021.09.26 | 추천 0 | 조회 95
dra**** 2021.09.26 0 95
21675
[엑셀 VBA] 파워쿼리 자동화 1. 자동화 순서 (8)
dra**** | 2021.09.23 | 추천 0 | 조회 393
dra**** 2021.09.23 0 393
21496
[엑셀 VBA] 개체 Reference - ADODB.Recordset : Database 필수 첨부파일 (4)
dra**** | 2021.09.16 | 추천 1 | 조회 190
dra**** 2021.09.16 1 190
21491
[잡설] IT가 흘러가고 있는 방향에 대한 단상 - 무엇을 공부해야 하는가? (21)
dra**** | 2021.09.16 | 추천 4 | 조회 218
dra**** 2021.09.16 4 218
21482
[엑셀 VBA] MySQL 02. Range -> 배열 -> INSERT (5)
dra**** | 2021.09.16 | 추천 0 | 조회 202
dra**** 2021.09.16 0 202
21412
[엑셀 VBA] MySQL 01. 워크시트 Custom Properties 활용하기 (4)
dra**** | 2021.09.14 | 추천 1 | 조회 112
dra**** 2021.09.14 1 112
20740
[엑셀 VBA] 개체 Reference - ListObject Object - [표]가지고 놀기 첨부파일 (12)
dra**** | 2021.08.29 | 추천 4 | 조회 218
dra**** 2021.08.29 4 218
20211
[엑셀 VBA] Windows API - winuser.h : 윈도우즈 프로그램의 핵심 첨부파일 (8)
dra**** | 2021.08.17 | 추천 3 | 조회 201
dra**** 2021.08.17 3 201
20194
[잡설] 엑셀이 웹으로 가야 하는 이유 (23)
dra**** | 2021.08.16 | 추천 12 | 조회 476
dra**** 2021.08.16 12 476
20153
[엑셀 VBA] 개체 Reference - FileSystem Object 첨부파일 (3)
dra**** | 2021.08.15 | 추천 1 | 조회 141
dra**** 2021.08.15 1 141
19717
[엑셀 VBA] 데이터 자동화 - 웹에서 제공하는 MySQL Sever를 이용한 자동화 개념 (15)
dra**** | 2021.08.05 | 추천 2 | 조회 343
dra**** 2021.08.05 2 343
19715
[엑셀 VBA] 개체 Reference - APPLICATION Object 첨부파일 (2)
dra**** | 2021.08.05 | 추천 0 | 조회 117
dra**** 2021.08.05 0 117