텍스트 파일 Importing & Exporting

Step 1: 텍스트 파일 불러오기(Importing)

요즘은 상황이 좋아졌는데 몇 년 전만 하더라도 질문하신 분과 같은 작업, 즉 회사의 Server에서 텍스트 파일(또는 SAM 파일)을 다운받은 다음, 이것을 Excel 파일로 Converting 시켜서 각종 분석을 하는 일을 무진장 많이 했었습니다. 일단, 다운받은 강좌파일과 예제 텍스트 파일을 반드시 같은 폴더에 압축해제를 하신 다음 아래의 버튼을 눌러 보세요.

예제 텍스트 파일(Textfile.txt)은 콤마로 구분된 CSV 형태이며, 아래는 그 앞부분입니다(6개 필드까지만 표시).

코드를 보도록 합니다.

Sub ImportTxtFile1()
    Dim sht As Worksheet
    Dim rngImport As Range
    Dim strData As String
    Dim Msg As String
    Dim r As Long
    Set sht = Worksheets.Add
    Set rngImport = sht.Range("a1")
    '//새로운 시트를 하나 삽입하고 A1 셀을 rngImport라는 Range 오브젝트 변수에 담습니다.

    Open ThisWorkbook.Path & Application.PathSeparator & "Textfile.txt" For Input As #1
    '//Open이라는 새로운 Statement가 나왔습니다(Open 메서드가 아닙니다. 착오없으시기
    '//바랍니다). 이 Open Statement는 (텍스트) 파일을 Importing 하거나 Exporting 하기 전에
    '//(쉽게 말해서 읽거나 쓰기 전에) 반드시 써 주어야 합니다.
    '//Application.PathSeparator는 역슬래시(\)를 의미합니다.
    '//폴더를 입력할 때, C:\Windows\Temp\ 등과 같이 폴더명 뒤에 \를 적어주어야 하는데
    '//이것을 얻기 위한 것입니다. 따라서 이 문장을,

    '//Open ThisWorkbook.Path & "\" & "Textfile.txt" For Input As #1 또는
    '//Open ThisWorkbook.Path & "\Textfile.txt" For Input As #1
    '//이렇게 적어 주어도 됩니다.

    '//그리고 파일을 읽어올 때에는 Input, 내보낼 때에는 OutPut을 각각 기재합니다.
    '//맨 마지막 부분의 #1은 불러올 파일의 filenumber로 이것은 1부터 511까지 가능합니다.

    Do While Not EOF(1)
        Line Input #1, strData
        '//파일을 읽어들일 때(Importing)에는 Input, Input#, Line Input# 이렇게 세 가지의
        '//Statement 중 하나가 사용됩니다. 원문을 그대로 인용할테니 참고하세요.

        rngImport.Offset(r, 0)=strData
        r = r + 1
    Loop
    Close #1
    '//음이 있으면 양이 있고 Black Hole이 있으면 White Hole이 있듯이, 파일을 Open했으면
    '//닫아 주어야지요. 만약 Close하지 않은 상태에서 다음번에 파일을 Open하려고 하면
    '//Error가 발생할 것입니다.

    Msg = "자료를 다 읽어들였습니다." & vbCr & vbCr
    Msg = Msg & "총 " & r - 1 & "개의 레코드가 생성되었습니다"
    MsgBox Msg,,"작업 종료//Exceller"
End Sub

코드 안에서 언급한 세 가지 Statement의 설명은 다음과 같습니다(도움말 원문을 인용하고 뜻을 덧붙였습니다).

이렇게 하니까 시트가 하나 생기면서 텍스트 파일의 내용이 워크시트 상에 차례대로 옮겨지기 하는데… 문제가 좀 있군요. 모든 데이터가 한 개의 셀 안에 들어가지요? 텍스트 파일이 한 라인에 한 개의 정보만을 담고 있다면 이 코드를 사용해도 되겠지만 예제 파일과 같이 여러 정보를 가진 파일이라면 이상한 형태의 파일이 생성됩니다.

그렇지만 시트를 잘 살펴보세요. 각 항목(즉 필드)이 콤마(,)로 구분되어 있지요? 그렇다면 이것은 "데이터-텍스트 나누기" 메뉴를 사용해서 손쉽게 원하는 형태의 파일로 전환(Converting)해 줄 수 있습니다. 이것만으로도 대단한 발전이라 할 수 있지 않겠습니까? ^^

이걸로는 만족을 못 하신다구요? 당연히(!) 그런 생각이 드셔야 합니다. 이런 걸로 프로그램을 만들었다간 욕 얻어먹기 십상이지요. ^^ 이렇게 바꾸면 어떨까요?

Step 2: 필드를 나누어 불러오기

이제 여러분이 원하시는 형태대로 텍스트 파일이 Excel 파일로 읽혀지지요?

Sub ImportTxtFile2()
    Dim sht As Worksheet
    Dim rngImport As Range
    Dim strData As String
    Dim strTemp As String
    Dim strText As String
    Dim Msg As String
    Dim r As Long
    Dim c As Integer
    Dim i As Integer
    Application.DisplayAlerts = False

    For Each sht In Sheets
        If sht.Name = "Importing2" Then
            MsgBox "같은 이름의 시트가 이미 있으나 삭제하겠습니다", , "시트 삭제//Exceller"
            sht.Delete
        End If
        '//이 부분은 예전 강좌에서 많이 설명을 드렸는데, 워크북 내에 같은 이름의 시트가 있으면
        '//시트를 삭제하는 부분입니다.
    Next sht

    Worksheets.Add.Name = "Importing2"
    Set rngImport = ActiveSheet.Range("a1")
    Open ThisWorkbook.Path & Application.PathSeparator & "Textfile.txt" For Input As #1

    Do While Not EOF(1)
        Line Input #1, strData

        For i = 1 To Len(strData)
            strText = Mid(strData, i, 1)
            '//텍스트 파일의 첫번째 라인을 읽어온 다음, Mid 함수를 사용해서 한 글자씩 strText
           '//변수에 저장합니다.

            If strText <> "," And strText <> Chr(34) Then strTemp = strTemp & strText
            If strText = "," Or i = Len(strData) Then
                rngImport.Offset(r, c) = strTemp
                c = c + 1
                strTemp = ""
            End If
            '//strText에 저장된 값이 콤마이거나 strData의 길이가 I와 같으면 시트 상의 지정된 셀에
            '//뿌려 줍니다. Textfile.txt 파일을 살펴보면, 각 필드는 콤마로 구분이 되어 있습니다.
            '//이런 형태의 텍스트 파일을 CSV(Comma Separated Value) 파일이라고 하는데,
            '//필드가 콤마로 구분되어 있으니까 콤마를 발견하면 그 시점에서 셀에 값을 Display
            '//하고 strTemp 변수를 초기화시킵니다.

            '//만약 strText에 저장된 값이 Chr(34), 즉 "가 아니라면 문자열을 계속 더합니다.
            '//왜 이 과정이 필요한지는 아시겠지요? 잘 생각해 보세요. ^^

        Next i
        c = 0
        r = r + 1
    Loop
    Close #1

    Msg = "자료를 다 읽어들였습니다." & vbCr & vbCr
    Msg = Msg & "총 " & r - 1 & "개의 레코드가 생성되었습니다"
    MsgBox Msg, , "작업 완료//Exceller"
End Sub

Step 3: 텍스트 파일로 내보내기(Exporting)

텍스트 파일 불러오기(Importing)는 대충 정리가 된 것 같고, 이번에는 워크시트의 내용을 텍스트 파일로 내보내는(Exporting) 방법에 대해 알아보겠습니다. 아래의 버튼을 누르신 다음, 생성된 텍스트 파일을 열어 보세요.

Sub ExportCurRegion()
    Dim rngOut As Range
    Dim lngFirstRow As Long, lngLastRow As Long
    Dim intFirstCol As Integer, intLastCol As Integer
    '//변수를 선언할 때, 위와 같이 여러 개의 변수를 한 라인에서 할 수 있습니다.
    '//그런데 만약 이것을 Dim lngFirstRow, lngLastRow As Long이라고 할 경우 각 변수의
    '//타입은 어떻게 지정될까요?
    '//뒤에 선언한 lngLastRow는 Long 타입으로 정의되나 lngFirstRow는 Long 타입이 아닌
    '//Variant형으로 되어 버립니다. 헷갈리지 마시기 바랍니다. 그리고 …As Long 부분을
    '//생략하고 그냥 Dim varData 이렇게만 적어주어도 Variant 형으로 자동선언됩니다.

    Dim r As Long
    Dim c As Integer
    Dim varData As Variant
    Dim Msg As String
    Dim sht As Worksheet
    On Error GoTo ET
    Application.Goto Sheets("Importing2").Range("a1"), True
    Set rngOut = ActiveCell.CurrentRegion
    '//ActiveCell.CurrentRegion이라는 것은 현재 선택되어 있는 셀의 상/하/좌/우 방향에
    '//연속적으로 이어져 있는 셀을 말합니다.
    rngOut.Replace what:=Chr(10), replacement:=""

    lngFirstRow = rngOut.Rows(1).Row
    lngLastRow = lngFirstRow + rngOut.Rows.Count - 1
    intFirstCol = rngOut.Columns(1).Column
    intLastCol = intFirstCol + rngOut.Columns.Count - 1
    Open ThisWorkbook.Path & Application.PathSeparator & "TextOut.txt" For Output As #1
    '//lngFirstRow, lngLastRow, intFirsCol, intLatCol 등 네 개의 변수에 ActiveCell.CurrentRegion의
    '//행과 열의 수를 담습니다.
    '//파일을 내보낼 때에도 역시 Open Statement를 사용했는데 이번에는 결과물을 문자 그대로
    '//내보내기 위해 Output을 사용하였습니다.

    For r = lngFirstRow To lngLastRow
        For c = intFirstCol To intLastCol
            varData = Cells(r, c).Value
            If IsNumeric(varData) Then varData = Val(varData)
            If c <> intLastCol Then
                Write #1, varData;
            Else
                Write #1, varData
            End If
            '//중첩 For~Next 순환문을 돌면서 각 셀의 값을 텍스트 파일로 내보냅니다.
            '//여기서 한 가지 주의하셔야 할 점은, Write Statement를 사용할 때, 해당 셀이 영역
            '//내의 마지막 열이 아니면 뒤에 세미콜론(;)을 붙여준다는 사실입니다. 그래야 하나의
            '//행(레코드)으로 인식을 하는 것입니다.

            '//텍스트 파일을 불러들일 때에는 Input, Input #, Line Input # 등의 Statement를
            '//사용했었습니다.
            '//Text 파일 형태로 내보낼 때에는 Write # 또는 Print # Statement를 사용합니다.

            '//요약하자면, Write # 문장은 콤마와 인용부호로 구분되는 텍스트 파일을 만들 때,
            '//Print # 문장은 탭으로 구분되는 텍스트 파일을 만들 때 각각 사용됩니다.

            '//대체로 Write # Statement에 의해 작성된 파일은 Input # Statement로 읽어오고,
            '//Print # 문장에 의해 작성된 파일은 Input이나 Line Input # Statement로 읽습니다.

        Next c
    Next r
    Close #1

    Msg = "Exporting 작업이 완료되었습니다" & vbCr & vbCr
    Msg = Msg & "이 파일과 같은 폴더 내에 있는" & vbCr
    Msg = Msg & "TextOut.txt 파일을 살펴보세요"
    MsgBox Msg, vbInformation, "작업 종료//Exceller"
ET:
End Sub

마무리

정신없이 떠들다 보니 분량이 좀 많아졌습니다. 특히 텍스트 형태의 파일과 Excel 파일을 넘나들며 분석작업을 많이 하시는 분들은 잘 소화시키시기 바랍니다.

Exceller에게 책을 만들자고 제의를 해 오신 분이 계시는군요. 가끔 서점에 갈 때마다 느끼는 것이지만 수십, 수백 종의 (Excel/VBA) 서적이 난무하고 있지만 사례 위주로 사용자의 관점에서 기술된 것은 매우 드물다는 생각을 하게 됩니다. 저마다 현란한 포장과 광고로 고객들을 부르고 있으니 어떤 것을 봐야하나 현기증을 느끼는 것은 어찌보면 당연할 것입니다. 내용을 보면 다 거기서 거기 같은데…

사실 우리 나라의 출판업계는 문제가 많다고 생각합니다. 누가 "이거 좋다"고 하면, 서점의 가판대에는 온통 그와 관련된 서적 일색입니다. 또 외국의 어느 전문가가 "이것도 좋다"고 하면 또 거기에 모든 초점이 맞춰집니다. 이래서는 안되지요.

다시 한번 말씀드리지만 Exceller가 알려드리는 Tip과 강좌는 비록 공짜이긴 하지만 어디에서도 찾아보기 힘든, 철저히 실무와 사례위주로 진행되는 것이므로 다른데 신경쓰지 마시고 열심히 하시기 바랍니다.

다음 시간에…

정리 — 텍스트 파일 입출력 핵심

자주 묻는 질문 (FAQ)

마치며

텍스트 파일과 엑셀 파일을 넘나드는 작업은 Open 문과 Write, Line Input만 익히면 자동화할 수 있습니다.