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등 개별적으로 쓰는 클래스) 코드가 된 점 양해 부탁드립니다. 링크 원본을 가시면  다 있습니다^^;;;

몇일 전 Round함수가 잘못 작동하고 있다는 글을 포스팅하였다.

하지만  설마하니 Round같은 기초 함수를 설계하는데 버그를 낼리가 없을거라는 생각에 조금 더 조사를 해 보았더니 역시 이것은 의도된 것이었음을 알게 되었다.

그리고 그것이 보다 정확한 결과를 위해서 라는 것도..

이 글을 보니 단숨에 이해가 되었다.

요지는 이렇다.

정수 1개를 기준으로  0.1 ~ 0.9 까지 소수 9개가 반올림 대상 숫자가 된다.

0.1~0.4까지 4개의 소수는 내림 처리를 하게 되고

0.5~0.9까지 5개의 소수는 올림 처리를 하게 된다.

이렇게 되면 버려지는 숫자는 4개, 올려지는 숫자는 5개이므로 정수1개당  1/9만큼 불공평하게 나누어진다는 것이다.

살짝 표로 보자면..

1.0~4.0까지 숫자 40개 중에서 변하지 않는 숫자는 4개이고

버려지는 숫자는 16개, 올려지는 숫자는 20개이다. 올려지는 숫자가 더 많다..

그럼 위 링크의 'Banker's Rounding'의 규칙을 적용해 본다면

1.0~4.0까지 숫자 40개 중에서 변하지 않는 숫자는 똑같이 4개이고

버려지는 숫자는 18개,  올려지는 숫자도 18개이다.

실제로 여러 값의 반올림을 평균한다던지 하는 집계쪽에는 이 방법이  더 근사치와 가까워 질 것이 확실해 보인다.

하지만 개인의 성적을 처리할 경우에는 맞지 않는다. 97.5점을 받아도 98점이고, 98.5점을 받아도 98점이 되버리니..

여튼 이제껏 생각 못하고 그냥 마구잡이로 Round를 써왔는데 이번을 계기로 알게 되었으니 앞으로는 상황을 고려해 사용하도록 해야겠다.

Access를 이용해 데이터 처리를 하던 도중 의도치 않는 결과가 나온다는 보고가 있어서 찾아보았다.

흔히 반올림 처리를 위해 사용하는 Round 함수가 특정 규칙을 가지고 오작동을 하고 있었다.

0.5 => 1

1.5 => 2

2.5 => 2

3.5 => 4

4.5 => 4

5.5 => 6

6.5 => 6

....

이렇게 격수로 하나씩 건너뛰면서 반올림, 내림, 반올림, 내림 처리를 하고 있는 것이었다.

증거자료..

 아래와 같이 실수(Single)타입의 값1~값6 필드를 가진 테이블을 만들어서 값들을 채워넣고..

아래의 쿼리를 돌려서 나온 결과..

SELECT
SUM(값1) AS 원값
,SUM(값2) AS [원값빼기05]
,SUM(값3) AS [원값빼기1]
,SUM(값4) AS [원값빼기15]
,SUM(값5) AS [원값빼기25]
,SUM(값6) AS [원값빼기35]
FROM 테이블1

UNION ALL

SELECT
ROUND(SUM(값1),0)
,ROUND(SUM(값2),0)
,ROUND(SUM(값3),0)
,ROUND(SUM(값4),0)
,ROUND(SUM(값5),0)
,ROUND(SUM(값6),0)
FROM 테이블1;

결과를 보면 17.5는 정상적으로 18로 반올림 처리가 되었으나  12.5의 반올림은 13이 되지 않고 12가 되었다.

역시 7.5는 반올림되어 8이 되었으나 2.5는 내림이 되어 2가 되어버렸다.

추측하기에는 실수(Single)값의 부동소숫점 연산 처리때문에 그런게 아닐까 싶지만..

별생각없이 반올림 처리하려고 Round를 자주 쓰게 되는데  이러면 곤란하다..

특히 이건 SAT 채점의 Omit처리를 하다 발견된 건데,, 점수의 계산이 틀어지면 끝장이지 않은가.

해서 해결방안은. Round함수를 뜯어고칠수는 없으니,,

Round(SUM(값1)+0.1 , 0) 으로  일괄적으로 0.1을 더해주었다. 정수반올림을 하는데에는 0.1 더해주는거면 충분함..

Microsoft ActiveX Data Objects 6.0 Library (adodb)와 ODBC 를 이용해서 각 DB에 Connection하는 클래스

Public Class DBConn
    Public myDB As New ADODB.Connection
    Public myRS As New ADODB.Recordset
    Public qq As StringBuilder

    Public Function DBOpen() As Boolean
        Dim Opened As Boolean
        Try
            myDB.Open("Provider=SQLOLEDB.1;Persist Security Info=False;Data Source=111.111.111.111,1111;Initial Catalog=DBNAME;User ID=DBNAME;Password=DBPASS;")

            If myDB.State Then
                Opened = True
            Else
                Opened = False
            End If
        Catch ex As Exception
            Opened = False
        End Try
        myRS.CursorType = ADODB.CursorTypeEnum.adOpenStatic
        myRS.CursorLocation = ADODB.CursorLocationEnum.adUseClient
        myRS.LockType = ADODB.LockTypeEnum.adLockOptimistic

        DBOpen = Opened
    End Function


    Public Sub DBClose()
        Try
            myDB.Close()
            loFunctions.releaseObject(myDB)
        Catch ex As Exception
        End Try
    End Sub

End Class




Public Class xlDBConn
    Public xlDB As New ADODB.Connection
    Public xlRS As New ADODB.Recordset
    Public qq As StringBuilder

    Public Function xlDBOpen(xlFileNM As String) As Boolean
        Dim Opened As Boolean

        Try
            With xlDB
                .ConnectionString = "Dsn=Excel Files;dbq=" + xlFileNM + ";driverid=1046;fil=excel 12.0;maxbuffersize=2048;pagetimeout=5"
                .Open()
            End With

            If xlDB.State Then
                Opened = True
            Else
                Opened = False
            End If
        Catch ex As Exception
            Opened = False
        End Try

        xlDBOpen = Opened
    End Function


    Public Sub xlDBClose()
        Try
            xlDB.Close()
            loFunctions.releaseObject(xlDB)
        Catch ex As Exception
        End Try
    End Sub

End Class



Public Class aceDBConn
    Public aceDB As New ADODB.Connection
    Public aceRS As New ADODB.Recordset
    Public qq As StringBuilder

    Public Function aceDBOpen(aceFileNM As String) As Boolean
        Dim Opened As Boolean

        Try
            With aceDB
                .ConnectionString = "Dsn=MS Access Database;dbq=" + aceFileNM.Replace("\\", "\") + ";driverid=25;fil=MS Access;maxbuffersize=2048;pagetimeout=5;uid=admin"
                .Open()
            End With

            If aceDB.State Then
                Opened = True
            Else
                Opened = False
            End If
        Catch ex As Exception
            Opened = False
        End Try

        aceDBOpen = Opened
    End Function


    Public Sub aceDBClose()
        Try
            aceDB.Close()
            loFunctions.releaseObject(aceDB)
        Catch ex As Exception
        End Try
    End Sub


End Class

ADODB나 OLEDB로 ACCESS DB(mdb, accdb)를 사용할 때.

일련번호 형식의 SEQ라던지 Identity 필드값을 초기화 하는 방법.

참고로 MS-SQL 에서는 이렇게 처리한다.

이걸 엑세스(access 2007 에서만 해봤음)  에서 구현하려면~

무슨.... 엑세스에 가서 데이터베이스 압축/복구를 하라느니 복잡하고 귀찮은 설명이 있는데

외부프로그램에서나 엑세스 내부에서나  위 쿼리를 한번 돌려주면 끝남. 단, 그전에 테이블의 모든 데이터를 삭제하고 해야함.

또한 PK,FK 등  관계가 걸려있는 경우에는 안 될수도 있음..