Lotiony's Blog

    Public Shared Sub RenameFileExt(srcFileNM As String, ByRef tgtFileNM As String)
        Dim count As Integer = 1
        Dim FileNameOnly As String = System.IO.Path.GetFileNameWithoutExtension(tgtFileNM)
        Dim Extension As String = System.IO.Path.GetExtension(tgtFileNM)
        Dim path As String = System.IO.Path.GetDirectoryName(tgtFileNM)
        Dim newFullPath As String = tgtFileNM

        While File.Exists(newFullPath)
            count += 1
            Dim tmpFileNM As String = String.Format("{0} ({1})", FileNameOnly, count)
            newFullPath = System.IO.Path.Combine(path, tmpFileNM + Extension)
        End While

        Try
            File.Move(srcFileNM, newFullPath)
            tgtFileNM = newFullPath
        Catch ex As Exception
        End Try

    End Sub

public static void RenameFileExt(string srcFileNM, ref string tgtFileNM)
{
	int count = 1;
	string FileNameOnly = System.IO.Path.GetFileNameWithoutExtension(tgtFileNM);
	string Extension = System.IO.Path.GetExtension(tgtFileNM);
	string path = System.IO.Path.GetDirectoryName(tgtFileNM);
	string newFullPath = tgtFileNM;

	while (File.Exists(newFullPath)) {
		string tmpFileNM = string.Format("{0} ({1})", FileNameOnly, count++);
		newFullPath = System.IO.Path.Combine(path, tmpFileNM + Extension);
	}

	try {
		File.Move(srcFileNM, newFullPath);
		tgtFileNM = newFullPath;
	} catch (Exception ex) {
	}

}

인수로 주어지는 타겟파일명을 ByRef로 한 이유는, 함수 내부에서 타겟파일명이 변경될 수 있기 때문입니다.

RenameFileExt("C:\111.txt", "C:\111.txt") 라고 넣는다면, 소스와 타겟이 동일하므로

C:\111 (1).txt 라는 파일로 타겟파일명을 바꿔주게 되겠죠. 이 최종 파일명 정보를 리턴하는 것입니다.

File.Copy 메서드로 바꿔서 사용하면  윈도우 탐색기의 복사 기능과 비슷한 로직으로 사본을 생성하게 됩니다.

저는 Rename을 위해 File.Move메서드를 사용했습니다.

많이 활용하시길...

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

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

[.NET] 디렉토리의 파일 목록 조사 + 필터링. Linq와 람다식을 이용한 Directory.GetFiles 확장자 지정하기.

사용자에게 폴더를 선택하게끔 하고, 해당 폴더 안에 들어있는 파일을 리스트업 해줘야 할 때가 있다.

.NET에서는  System.IO.Directory.GetFiles 를 이용해서 디렉토리 내 파일 목록을 얻어올 수 있다.

사용방법은 잘들 아시겠지만 다음과 같다.

여기서 보듯이 GetFiles메서드에는 패턴검색 옵션을 지정할 수가 있게 되어있는데(searchpattern)

이 패턴 옵션이 뭔가 어정쩡하다.

예를들어, 폴더 내의 이미지파일을 모두 보여주고 싶을 때에는 파일확장명에 *.gif, *.tif, *.jpg, *.png, *.bmp 가 지정되어야 할 것이다.

하지만 익숙한 확장자 나열 기법을 적용해서

Directory.GetFiles(TargetFolderNM, "*.gif|*.jpg|*.png", SearchOption.TopDirectoryOnly) 처럼 사용하면 에러가 발생한다.

searchpattern에는 와일드카드를 사용할 수 있지만, 조건식을 나열할 수는 없는 반쪽짜리인 것이다..-_-

꼭 단일 확장자로만 지정을 해줘야 한단다.

그래서 그럼 이렇게 해보았다. Concat메서드로  리턴된 배열을 합쳐버리는 방법이다.

하지만 이 방법 역시 경우에 따라서 오류가 발생한다.

Concat 메서드는 연결할 첫 번째 시퀀스가 nothing일 경우 ArgumentNullException 에러를 발생시킨다.

즉, 첫번째줄에서 *.jpg파일이 하나도 발견되지 않았다면  *.tif 에서 반환된 파일목록 배열은 붙을곳을 찾지 못해 오류가 나는 것이다..

모든 폴더에 jpg파일이 최소한 한개씩은 있어준다는 보장이 없으니 이 역시 사용불가이다. 물론 이것도 조건분기를 처리해서 DataFileNM이 Nothing일 경우엔 그냥 할당하고  아닐경우에만 Concat을 하는 식으로 코딩할 수는 있겠으나 바람직한 코딩이 아니다.

사설이 길었는데, 이러한 연유로 아래의 방식을 찾았다.  별도의 함수를 만들지 않아도 되고 코드 길이도 짧다.

실로 베스트한 방식이 아닐수 없다. 후후후 -_ㅡv

여기엔 Linq와 람다식이 사용되었다.

[VB.net]

[C#.net]

일단  exts에 필터링 할 확장자를 배열로 지정해 놓고

Directory.GetFiles(TargetFolderNM, "*.*", SearchOption.TopDirectoryOnly) 로 폴더내 모든 파일을 가져온다(String배열)

여기서 .Where(Function(s) exts.Contains(Path.GetExtension(s), StringComparer.OrdinalIgnoreCase)) 으로  확장자를 필터링해서 .ToArray로 다시 변환해서 리턴하는 것이다.

그러면 DataFileNM 스트링배열 개체에  해당 파일명이 아름답게 담겨지게 된다!

[.NET] 시스템 환경변수 Path에 특정 경로 추가하기.

프로그램을 만들어 배포를 하게되면 경우에 따라 System 환경 변수의 Path를 건드려 주어야 할 때가 있다.

헌데 없어보이게  사용자에게 직접 환경변수를 추가하라고 할 수는 없는 노릇이고

제일 무난하고 편한 Install Factory로 해도, Install Shield 로 해도 방법이 없는것은 아니지만 해본결과 별루이다.

레지스트리를 직접 변경해주는 방식으로 하게 되는데, 이때 기존의 Path내용을 유지하면서 새로운것만 추가하기가 간단치 않다.

일단 '사용자 환경변수' 레지스트리의 위치는

 HKEY_CURRENT_USER\Environment\  이다.

사용자변수는 간단하다.

문제는 시스템 환경변수이다. 시스템환경변수는 최소 두군데 이상 동시에 존재한다.

일단 기본위치는

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\Environment

이곳이지만, 사용자계정에따라 ControlSet001, ControlSet002 ... 등등으로 나뉘어진다.

HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Control\Session Manager\Environment

어드메 있는것을 바꿔야 하는지까지는 잘 모르겠다. 이미 이렇게 널부러져 있는것을 확인한순간 다른방법을 고민했으니..

.NET에는 System.Environment 라는 훌륭한 클래스가 기본제공되질 않는가!

그래서 방법은 프로그램 실행시 시스템 환경변수값을 불러다가  내가 추가하고자 하는 경로가 이미 존재하면 제껴버리고,

없으면  맨앞에 경로를 하나 추가해서 다시 셋팅해주는 것이다.

그게 ControlSet001이 수정되야 하는지 002가 수정되야 하는지, 아니면 CurrentControlSet 에서 수정되야 하는지 알필요 따윈 없다.

어차피 이 프로그램이 System.Environment 가  찾아내준 위치의 시스템환경변수값만 조작해주면 DLL을 불러들이는데는 지장이 없으니까.

그래서 작성한 코드.

    Public Shared Sub SystemPathControl(AppPath As String)

        Dim _sysPath As String = System.Environment.GetEnvironmentVariable("Path", EnvironmentVariableTarget.Machine)
        Dim oldPath() As String = _sysPath.Split(";")
        Dim newPath As New StringBuilder

        If Not oldPath.Contains(AppPath) Then
            newPath.Append(AppPath + ";")
            For Each ePath As String In oldPath
                newPath.Append(ePath + ";")
            Next
            System.Environment.SetEnvironmentVariable("Path", newPath.ToString, EnvironmentVariableTarget.Machine)
        End If

    End Sub

사용된 클래스는 보시다시피..

System.Environment.GetEnvironmentVariable("Path", EnvironmentVariableTarget.Machine)

System.Environment.SetEnvironmentVariable("Path", newPath.ToString, EnvironmentVariableTarget.Machine)

이 되겠다.

Get으로 Machine(시스템) 환경변수 중 Path를 가져와서 매개변수로 들어온 AppPath값이 있는지 찾아보구

없으면 젤앞에 AppPath를 붙이고 나머지껄 줄줄이 이어붙여서 newPath를 만들어준 다음

Set으로 다시 Machine의 Path값을 바꿔주는 초간단 함수가 되겠다.

Get/Set EnvironmentVariable 메서드의 마지막 매개변수는 사용자/시스템 변수값에 대한 상수이다.

시스템변수는 EnvironmentVariableTarget.Machine

사용자변수는 EnvironmentVariableTarget.User

이제 이것을 프로그램 시작할때 도는 프로시져 한귀퉁이에서 호출해주면 되시겠다.

사실 이 행위를 플그램 실행때마다 매번 하는게 솔직히 비효율적일 수가 있다.

어디서 지나가다 주워본것 같은데, 닷넷은 배포후 최초 실행되었는지를 리턴해주는 함수가 있다고 봤다(다시 찾아보려니 찾을수가 없지만 ㅜㅜ)

이걸 조합한다면  프로그램 인스톨 후 최초 실행시에만 한번 돌려주도록 만들 수도 있을것이다.

최초실행여부를 확인해 주는 함수를 아시는분은 댓글로 제보좀 부탁드립니다 ㅋㅋ