자료변환 예제

Step 1: 오늘의 질문

오늘 소개해 드릴 내용은 오피스 튜터 사이트에 어느 분이 질문하신 것입니다.

현재 상태는 아래와 같이 항목 이름과 값이 세로로 한 줄씩 번갈아 입력되어 있는 텍스트 파일입니다.

원하는 상태는 다음과 같습니다.

Step 2: 실행해 보기

일단 아래 버튼을 눌러 예제를 실행시켜보고 오시기 바랍니다.

오늘 강좌 파일은 VB0084.xls와 MyList.txt 이렇게 두 개의 파일로 이루어져 있습니다. 압축을 푸실 때 반드시 같은 폴더에 푸시기 바랍니다. 안그러면 에러가 납니다.

Step 3: DataFormatTransfer 프로시저

일단 오늘 강좌를 제대로 이해하시기 위해서는 텍스트 파일 Importing에 대한 기본지식이 있어야 합니다. VB0052 강좌에서 자세하게 설명을 드렸으니 기억이 감감한 분들은 다시 한번 읽어보고 오시기 바랍니다. 세상 만사가 반복의 연속이지요? 역사도 반복되는데(History repeats itself) 무엇인들 반복이 안될려구… ^^ 코드를 볼까요?

Sub DataFormatTransfer()
    Dim strData As String
    Dim rngButton As Range
    Dim rngImport As Range
    Dim Msg As String
    Dim r As Long
    Dim c As Integer
    Dim i As Long
    Application.DisplayAlerts = False
    On Error GoTo ET
    '//필요한 등장인물 및 배역(변수)을 선언해 줍니다.
    '//만약 프로그램을 실행하는 도중 에러가 발생할 경우를 대비하여 안전장치(에러 트래핑)를
    '//마련해 둡니다.

    Worksheets.Add after:=Sheets("PREFACE")
    Set rngImport = ActiveSheet.Range("A1")
    ActiveWindow.DisplayGridlines = False
    '//새로운 워크시트를 한장 삽입하고 A1셀을 rngImport라고 지정해 둡니다.
    '//DisplayGridlines를 False로 지정하면 시트의 셀 구분선을 보이지 않도록 설정합니다.

    Range("A1:F1") = Array("이름", "나이", "직업", "회원번호", "주소", "전화번호")
    Open ThisWorkbook.Path & Application.PathSeparator & "MyList.txt" For Input As #1
    '//Open이라는 Statement에 대해서는 이미 VB0052 강좌에서 보셨지요? 안 보셨어요?
    '//이것은 (텍스트) 파일을 읽거나 쓰기 전에 반드시 써 주어야 합니다.
    '//그 다음에 Application.PathSeparator는 \와 같은 것이고, 그 뒤에 있는 Input은
    '//파일을 읽어올 때(즉 Importing 할 때), Output은 파일을 Exporting 즉 내보낼 때
    '//적어 줍니다.
    '//맨 마지막에 있는 #1은 읽어들일 파일의 filenumber로 1부터 511까지 가능합니다.

    Do While Not EOF(1)
        Line Input #1, strData
        '//파일을 읽어들일 때에는 Input, Input #, Line Input #, 이렇게 세 가지 Statement 중에서
        '//한 가지가 사용된다고 설명을 드렸지요?(아직 VB0052를 안 보신 분은 참고하세요)

        i = i + 1
        If i Mod 3 = 2 Then
        '//여기서부터는 약간 생각이 필요한 부분입니다.
        '//원본 데이터(텍스트 파일)을 잘 살펴보시면 어떤 규칙을 발견하실 수 있을 것입니다.
        '//어떤 규칙이 있습니까? 아무 규칙이 없다구요? 있습니다! 다시 한번 잘 살펴 보세요.

        '//그렇습니다!! 두 줄씩 내용이 있고 공백이 하나, 또 두 줄에 내용에 공백라인이 하나…
        '//이렇게 계속 반복이 되지요? 그리고 또 한 가지... 이름, 나이, 직업, 회원번호, 주소,
        '//전화번호 등과 같이 여섯 개의 필드가 계속 반복이 됩니다.

        '//따라서 이 두 가지 조건을 If 문을 사용해서 구분해 줍니다. 이렇게 말로 한 것을
        '//컴퓨터(EXCEL)가 알아먹을 수 있도록 프로그래밍을 하면 아래와 같이 됩니다.

            rngImport.Offset(r + 1, c) = strData
            c = c + 1
            If c = 6 Then
                c = 0
                r = r + 1
            End If

        End If
    Loop
    Close #1

    '//아래 부분은 완성된 자료에 대해 장식을 하는 부분입니다.
    Range("A2").Select
    Selection.AutoFormat Format:=xlRangeAutoFormatList2, Number:=True, Font:= _
        True, Alignment:=True, Border:=True, Pattern:=True, Width:=True
    Columns.AutoFit

    '//사용자의 편의를 위해 버튼을 하나 그린 다음, 클릭을 하게 되면 원래의 위치로
    '//돌아가도록 하기 위한 부분입니다.
    Rows("1:2").Insert Shift:=xlDown
    Set rngButton = ActiveSheet.Range("F2")
    With rngButton
        ActiveSheet.Buttons.Add(.Left, .Top, .Width, .Height).Select
    End With
    With Selection
        .Caption = "<<돌아가기"
        .Font.Size = 10
        .Font.Name = "Arial"
        .OnAction = "GoBack"
    End With
    rngButton.Select
    Msg = "자료를 다 읽어들였습니다" & vbCr & vbCr
    Msg = Msg & "총 " & r & "개의 레코드가 생성되었습니다"
    MsgBox Msg, , "작업 종료//Exceller"
    Application.DisplayAlerts = True
    Exit Sub
ET:
    '//만약 프로그램을 실행하는 도중 에러가 발생하면 이 부분이 실행됩니다.

    Msg = "파일이 존재하지 않거나 다른 경로에 있습니다." & vbCr
    Msg = Msg & "          확인하시기 바랍니다"
    MsgBox Msg, , "오류 발생//Exceller"
    Application.DisplayAlerts = True
End Sub

Sub GoBack()
    Sheets("PREFACE").Select
End Sub

코드는 좀 길어보입니다만 이미 설명을 드렸던 부분이고 또한 decoration을 위한 부분이 일부 포함되어 있으므로 실제로 복잡한 것은 아닙니다.

마무리

오늘은 여기까지…

정리 — 자료 변환 핵심

자주 묻는 질문 (FAQ)

마치며

데이터가 반복되는 규칙만 찾으면 어떤 형태의 텍스트 자료도 표로 변환할 수 있습니다.