Lotiony's Blog

.NET과 Access를 가지고 놀던 중 대량의 엑셀 데이터를 엑세스DB로 업로드 하는 괜찮은 방법을 찾아서 혹시 필요하신 분이 계실까 하여 포스팅합니다.

vb.net 기준이고 엑셀 문서를 datatable로 한방에 읽어오는 방법은,,

닷넷에서 기본 지원하는 Oledb를 이용하면 간단하므로 생략하겠습니다.

DataAdapter를 만들어 Fill을 해도 되고

ExcuteReader로 읽어와도 되고..  여튼 엑셀을 datatable로 만드는것은 간단합니다.

여기서 From Source를 Datatable로 한 것은, 비단 엑셀 뿐 아니라 xml이나 txt, 기타 sql서버 등 여러 가지 다른 소스를 대상으로 하여도 동일한 사용성을 갖게 하기 위해서 입니다.

엑셀파일만 대상으로 엑세스로 덤프하는것은 http://cafe.naver.com/xlsvba/1101  이 방법이 더 간단합니다.

여튼, .NET의 알짜배기인 Dataset, DataTable을  이용합니다.

처음에는 그냥 단순하게 아래의 방법으로 인서트를 했습니다.

'***** 데이터를 업로드한다. 
For i = 0 To OrgDataTable.Rows.Count - 1 
    Application.DoEvents() 

    qq.Clear() 
    qq.AppendLine(" INSERT INTO [Data]") 
    qq.AppendLine(" ([이름], [주민등록번호])") 
    qq.AppendLine(" VALUES") 
    qq.AppendLine(" (") 
    qq.AppendLine("     '" + OrgDataTable.Rows(i).Item(0).ToString.Trim + "'") 
    qq.AppendLine("     ,'" + OrgDataTable.Rows(i).Item(1).ToString.Trim + "'") 
    qq.AppendLine(" )") 

    Try 
        dbCon.aceRS.Close() 
    Catch ex As Exception 
    End Try 
    dbCon.aceRS.Open(qq.ToString, dbCon.aceDB, 1) 
    'System.Threading.Thread.Sleep(20) 

    loFunctions.UpdateProgress(ProgressBar1, lbl_Progress, (i + 1), (i + 1).ToString + " / " + OrgDataTable.Rows.Count.ToString) 
Next

지극히 평범한 방식이죠. 오리지널을 순서대로 돌면서 한줄한줄 인서트 하는 방법.

10만여개의 이름,주민등록번호 데이터를 넣는데도 10분이 훨씬 넘는 시간이 걸리더군요.

그래서 다른 방법을 구글링해서 아래 소스를 발견하고 적용해 보았습니다. DataAdapter를 사용하는 방법이죠.

http://www.codeproject.com/Articles/17028/Bulk-Record-Insert-for-Access

벌크 인서트에 관해서는 대부분 저 링크가 걸려있더군요.

많은 샘플소스들이 이렇게 DataAdapter를 활용해서 인텔리전스한 Update메서드를 사용하는걸 추천했습니다.

그런데 저대로는 잘 안되었습니다. 왜인가 이유를 살펴보니, DataAdapter의 Update메서드는

지정된 DataTable을 훌륭하게 동기화 해주기는 하지만 그 이전에 DataTable의 각각의 Rows객체의 RowState를 보고

Insert인지, Update인지, Delete인지를 판단해서 해당 커맨드를 실행하니까요.

지금 하려는 것은 원본데이터를 몽땅 Insert하는 것이므로 DataTable의 모든 Rows의 rowstate는 RowAdded 값을 가져야만 합니다.

그래서 아래와 같이 해봤습니다.

Dim oConn As OleDbConnection = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + AppDomain.CurrentDomain.BaseDirectory + My.Settings.DBFileNM + ";") 
Dim SQL As String = "SELECT * FROM Data WHERE 0=1" 
Dim insSQL As String = "INSERT INTO Data ([이름], [주민등록번호]) VALUES (@이름, @주민등록번호)" 
Dim oAdpt As OleDbDataAdapter = New OleDbDataAdapter(SQL, oConn) 

For Each dRow As DataRow In OrgDataTable.Rows 
    dRow.AcceptChanges() 
    dRow.SetAdded() 
Next 

oAdpt.InsertCommand = New OleDbCommand(insSQL, oConn) 
oAdpt.InsertCommand.Parameters.Add("@이름", OleDbType.Char, 255, "이름") 
oAdpt.InsertCommand.Parameters.Add("@주민등록번호", OleDbType.Char, 255, "주민등록번호") 
oAdpt.Update(OrgDataTable) 

DataTable의 모든 Rows를 순회하면서 임의로 RowState값을 RowAdded로 설정했습니다. 그래야 몽땅 Insert 적용되지요.

그리고 실행해보니 업로드는 잘 됩니다.

그러나 웬걸,, 시간이 아까보다 더 많이 걸리네요.

하도 오래 걸리기에 어느부분에서 시간이 걸리나 봤더니 oAdp.Update(OrgDataTable) 에서  하염없이.. 못넘어갑니다.

Progress로 진행률도 알아낼 수가 없어서 몇분 기다리다 답답해서 강종 해버리고 엑세스파일을 열어보니 고작 3만개 들어갔네요.

이딴 성능을 가지고 뭘 벌크로 인서트 하라고 --;;;;

방법이 이것말곤 없나 찾다가 드디어 궁극의 비기를 발견합니다.

http://stackoverflow.com/questions/7070011/writing-large-number-of-records-bulk-insert-to-access-in-net-c

정말 현답자이십니다. 6가지 사례를 들어  밀리세컨까지  시뮬레이션 결과를 정리해 주셨더군요.

메모리에 DAO Recordset객체를 만들어내는 방법이 가장 좋다합니다.

같은 일을 하는데 그 방법을 어떻게 하느냐에 따라 2.8초 vs 86초? 

어쨋든 해 보았습니다. 맨 아래에 친절하게도 클래스급으로 만들어진 소스가 있어서 그대로 따다가..

vb.net 코드로 컨버팅 하고.. 참조에 Microsoft Office Data Access Object 14.0 걸어주고.. (Dao3.6은 mdb까지밖에 안되므로)

Class uploadAccess 
    Public Sub BulkExportToAccess(dtOutData As DataTable, DBPath As [String], TableNm As [String]) 
        Dim dbEngine As New DAO.DBEngine() 
        Dim CheckFl As [Boolean] = False 

        Try 
            Dim db As DAO.Database = dbEngine.OpenDatabase(DBPath) 
            Dim AccesssRecordset As DAO.Recordset = db.OpenRecordset(TableNm) 
            Dim AccesssFields As DAO.Field() = New DAO.Field(dtOutData.Columns.Count - 1) {} 

            'Loop on each row of dtOutData 
            For rowCounter As Int32 = 0 To dtOutData.Rows.Count - 1 
                AccesssRecordset.AddNew() 
                'Loop on column 
                For colCounter As Int32 = 0 To dtOutData.Columns.Count - 1 
                    ' for the first time... setup the field name. 
                    If Not CheckFl Then 
                        AccesssFields(colCounter) = AccesssRecordset.Fields(dtOutData.Columns(colCounter).ColumnName) 
                    End If 
                    AccesssFields(colCounter).Value = dtOutData.Rows(rowCounter)(colCounter) 
                Next 

                AccesssRecordset.Update() 
                CheckFl = True 
            Next 

            AccesssRecordset.Close() 
            db.Close() 
        Finally 
            System.Runtime.InteropServices.Marshal.ReleaseComObject(dbEngine) 
            dbEngine = Nothing 
        End Try 
    End Sub 

End Class 

그리고 실행코드..

빨리빨리..빨리 해서 결과를 보고싶엉ㅇㅇ +_+ 대충 빨리..

Dim Acce As uploadAccess = New uploadAccess 
Acce.BulkExportToAccess(OrgDataTable, AppDomain.CurrentDomain.BaseDirectory + My.Settings.DBFileNM, "Data") 

매우매우 잘 됩니다. 10만5천건의 데이터가 눈깜짝할새에 쏵~ 들어가네요.

12만row 몇컬럼인진 모르겠으나 4초걸렸다고 했는데  제가 테스트한 10만5천row 2컬럼짜리는 0.5초만에 들어가네요.

전 개인적으로 어떤 DataTable을 다룰때 행,열 For문 돌려서 뭘 하는게 굉장히 맘에 안들었습니다.

SQL에서 레코드셋으로 받아온 결과를  엑셀에 뿌릴때에도..  컬럼, 로우 중첩for문 돌면서 처리하는거 절대 안썼고

쿼리문을 변경해서라도  copyfromrecordset 을 써서  한방에 뿌리는걸 선호했죠.

수십수백만개가 될지도 모르는걸  For문으로 처리해 버릇하면  후에 감당이 안될것 같아서요.

그래서 닷넷으로 와서도 copyfromrecordset이 지원되지 않는 닷넷의 ado 기본공급자들 안쓰고

따로 ado 6.0을 참조걸어서 쓸 정도였죠.

그런데 지금의 결과를 보니 for도 잘 쓰면 베스트가 될 수도 있구나 하는걸 깨달았습니다.

아마 이 방법은 db서버에 있는 데이터를 엑셀로 내려받기 해 줄 때에도 유용하게 쓸수 있을것 같네요.

어떤 데이터 원본이던지 DataTable로 만들어낼 수 있고,  DAO로 컨트롤할 수 있는 대상이면 적용 될 테니까요..

두서없이 제가 쓰던 코드를 고대로 갖다넣어버림으로써  샘플소스로 쓰기에는 부적절한 (lofunctions등 개별적으로 쓰는 클래스) 코드가 된 점 양해 부탁드립니다. 링크 원본을 가시면  다 있습니다^^;;;