[엑셀vba] AccessDB 테이블에 엑셀 파일로 한 번에 대량 데이터 입력하기

지금까지 엑셀vba를 이용해서 액세스 DB에 데이터를 저장하는 방식은 SQL 쿼리를 이용했었다. 데이터가 소량일 때는 큰 문제가 되지 않았는데, 수 백개 이상이 되면서 처리 속도가 상당히 느려지는 바람에 작업에 큰 무리가 있음을 알게되었다. 그래서 한 방에 뙇! 넣을 수 있는 방법이 없을까 구글링을 하면서 찾은 해결책을 기록하고자 한다. 

---

1. 해결방안

 액세스에서 엑셀파일을 불러오는 모듈을 만들고 엑셀vba에서 액세스의 모듈을 실행시킨다.

 

???: 않이;;; 그럴꺼면 그냥 첨부터 액세스vba로 만들면 되는거 아니냐;; 라고 생각할 수 있다. 근데 나 도박사 엑셀의 신이 될 남자기 때문에 엑셀을 사용한다.

---

2. 액세스vba 모듈 추가

액세스vba에 들어갈 소스는 구글링하다가 다음 블로그에서 찾아냈다. 그냥 바로 써도 되는 아주 훌륭한 코드였다.

 

https://blog.daum.net/jinsilban/963

access vba 엑셀파일 가져오기

Public Function 엑셀파일추가(Filename As String, HasFieldNames As Boolean, tableName As String) As Boolean

    Dim errCount As Integer
    On Error GoTo err_handler
    DoCmd.DeleteObject acTable, tableName
    
    If (Right(Filename, 3) = "xls") Then
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, tableName, Filename, HasFieldNames
        Exit Function
    ElseIf (Right(Filename, 4) = "xlsx") Then
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, tableName, Filename, HasFieldNames
        Exit Function
    End If
    If (Right(Filename, 3) = "csv") Then
        DoCmd.TransferText acLinkDelim, , tableName, Filename, True
    End If

Exit_Thing:

    If Not IsNull(DLookup("Name", "MSysObjects", "Name='" & tableName & "'")) Then
       DoCmd.DeleteObject acTable, tableName
    End If

    Exit Function

err_handler:

    If (Err.Number = 3086 Or Err.Number = 3274 Or Err.Number = 3073) And errCount < 3 Then
        errCount = errCount + 1
    ElseIf Err.Number = 3127 Then
        MsgBox "The fields in all the tabs are the same. Please make sure that each sheet has the exact column names if you wish to import mulitple", vbCritical, "MultiSheets not identical"
        엑셀파일추가 = False
        GoTo Exit_Thing
    Else
        MsgBox Err.Number & "-" & Err.Description
        엑셀파일추가 = False
        GoTo Exit_Thing
        Resume
    End If
    Resume

End Function

 

액세스 vb편집기를 열고 위 코드를 붙여넣기만하면 액세스 모듈 준비 끝!

아, 이 코드를 쓸 때 주의할 점이 있다. 그것은 바로 불러온 엑셀파일의 내용을 넣어둘 빈 테이블이 필요하다!

왜냐하면 기존에 존재하는 테이블을 이용할 경우 모든 내용이 삭제되고 불러온 엑셀파일의 내용으로 변경되기 때문이다. 아래 그림처럼 ID 필드만 있는 빈 테이블을 만들면된다. 

 

빈 테이블 생성

이 모듈은 파라미터 3가지를 받는다.

-Filename : 파일경로 + 파일이름 + 확장자까지 포함한 경로(필수)

-HasFieldNames : 첫 행이 필드인지 구분하기 위한 파라미터, True, False 둘 중에 택(필수)

-tablename: 엑셀파일 내용이 들어갈 테이블 이름(필수)

 

액세스에서 준비할 부분이 여기서 끝이다.

다음으로 엑셀vba에서 액세스 모듈을 실행하는 법을 살펴보자.

 

 

※ 참고사항 ※

필자가 이 코드를 써보면서 오류가 나타난 경우를 보면 다음과 같다.

1. 파일경로 잘못됨.
2. 존재하지 않는 테이블
3. 그 외 (아직 발견하지 못함)

+ 액세스에서도 바로 실행가능한 코드이므로 굳이 엑셀vba까지 갈 필요는 없음.

---

3. 엑셀vba 모듈 추가

엑셀vba 편집기에서는 액세스에 있는 모듈을 실행하는 코드를 넣어주면 된다.

액세스 모듈을 작동시키려면 엑셀vba에서 참조를 추가해줘야한다. 

추가할 항목: Microsoft Access 16.0 Object Library (액세스 버전에 따라 숫자가 다를 수 있음)

 

추가할 참조

Option Explicit

Sub TEST()
    Access.OpenCurrentDatabase "accdb 파일 경로"
    Access.Run "엑셀파일추가", "엑셀 파일 경로", True, "데이터 넣을 테이블명"
    Access.CloseCurrentDatabase
End Sub

참조를 추가하고 위 코드를 이용하면 엑셀 파일을 한 번에 액세스 테이블에 입력 할 수 있다. 경로에는 파일의 확장자까지 모두 입력해줘 정상적으로 작동하므로 주의하자.

ex) "c:\test\data.accdb"

ex) "c:\test\data.xlsx" , "c:\test\data.xls", "c:\test\data.csv"

 

다음으로 하나의 예시 파일을 생성하여 시간이 얼마나 단축되는지 확인 해보겠다.

---

4. 코드 테스트

data.xlsx 파일

data.xlsx 파일을 1행은 필드명을 넣어주고 동일한 데이터를 10001번행까지 입력해두었다. 

한 번에 엑셀파일로 입력할 때와 INSERT INTO 쿼리를 이용해 한 행씩 입력할 경우 시간 차이가 얼마나 나는지 확인해보자.

※ 실행 속도는 컴퓨터 사양, 환경에 따라 다를 수 있음 ※

 

---

-데이터 한 번에 넣을 때 

60,000개 - 20초

20,000행 * 데이터 3개 = 60,000개

 

 

 

 

 

 

15,000개 - 8초

 

 

 

 

 

---

-INSERT INTO 쿼리를 이용한 방법

15,000개의 데이터 넣는데 51초.... 그만 알아보자...

 

 

 

 

---

대량 로우데이터를 입력할 땐 확실히 엑셀파일을 이용하는게 압도적 확실히 어마무시하게 빠르다는 것을 알 수 있다. 귿

마지막으로 200만개에 가까운 데이터를 넣을땐 얼마나 걸리는지 테스트해보겠다.

 

?????

48만행 * 필드 4개 = 1,920,000개 데이터를 넣어봤는데 6초밖에 안걸렸다.. 뭐지? ;; 

오히려 좋아, 아주 좋아

귿