Tech Forum

Code: Select all

BEGIN { FS = "\t"; fileIndex = 1 }
NR == 1 {
    for (i=3; i<=NF-1; i++)
        unitName[i-2] = $i

    filename = FILENAME
    nUnits = NF-3
}
$1 >= 1 && $1 <= 24 {
    if ( FILENAME != filename )
    {
        fileIndex++
        filename = FILENAME
    }
    stat = $1
    for (unit=3; unit<=NF; unit++)
        data[unit-2, stat, fileIndex] = $unit
    #print filename, $0 > "apple.txt"
}
END {
    for (unit=1; unit<=nUnits; unit++)
    {
        name = unitName[unit]
        filename = "unitStats/" tolower(name) ".txt"
        print "Members" > filename
        for (stat=1; stat<=24; stat++)
        {
            str = GetCategory(stat)
            if ( str != "none" )
                print str > filename

            printf( "%s", GetStat(stat) ) > filename
            for (k=1; k<=fileIndex; k++)
            {
                printf(",%d", data[unit, stat, k]) > filename
                print data[unit, stat, k] > "banana.txt"
            }
            printf("\n") > filename
        }
        close(filename)
    }
}

function GetStat(i) { 

    switch ( i ) {
        case 1:
            str = "Total members"
            break
        case 2:
            str = "Average Sacrament meeting attendance"
            break
        case 3:
            str = "Total families"
            break
        case 4:
            str = "Total families visited by home teachers"
            break
        case 5:
            str = "Total Melchizedek Priesthood holders"
            break
        case 6:
            str = "Total Melchizedek Priesthood holders attending priesthood meetings"
            break
        case 7:
            str = "Total prospective elders"
            break
        case 8:
            str = "Total prospective elders attending priesthood meetings"
            break
        case 9:
            str = "Total women"
            break
        case 10:
            str = "Total women attending Sunday Relief Society meetings"
            break
        case 11:
            str = "Total women contacted by visiting teachers"
            break
        case 12:
            str = "Total endowed adults"
            break
        case 13:
            str = "Total endowed adults with a temple recommend"
            break
        case 14:
            str = "Total Young Men"
            break
        case 15:
            str = "Total Young Men attending priesthood meetings"
            break
        case 16:
            str = "Total Young Women"
            break
        case 17:
            str = "Total Young Women attending Sunday Young Women meetings"
            break
        case 18:
            str = "Total children ages 3 (as of 1 January) through 11 years"
            break
        case 19:
            str = "Total children on line 18 attending Sunday Primary meetings"
            break
        case 20:
            str = "Total children ages 0 through 2 (as of 1 January) years"
            break
        case 21:
            str = "Total converts age 8 and older baptized and confirmed in the last 12 months"
            break
        case 22:
            str = "Converts on line 21 attending at least one sacrament meeting last month"
            break
        case 23:
            str = "Converts age 12 and older who have a Church responsibility or calling"
            break
        case 24:
            str = "Convert males age 12 and older who hold the Aaronic Priesthood"
            break
        }

    return str
}

function GetCategory(i) {
    
    switch ( i ) {
        case 3:
            str = "Families"
            break
        case 5:
            str = "Adults"
            break
        case 14:
            str = "Youth"
            break
        case 18:
            str = "Children"
            break
        case 21:
            str = "Converts"
            break
        default:
            str = "none"
    }

    return str
}

Code: Select all

Sub CombineTextFiles()
    Dim FilesToOpen
    Dim x As Integer
    Dim wkbAll As Workbook
    Dim wkbTemp As Workbook
    Dim sDelimiter As String

    On Error GoTo ErrHandler
    Application.ScreenUpdating = False

    sDelimiter = ","

    FilesToOpen = Application.GetOpenFilename _
      (FileFilter:="Text Files (*.txt), *.txt", _
      MultiSelect:=True, Title:="Text Files to Open")

    If TypeName(FilesToOpen) = "Boolean" Then
        MsgBox "No Files were selected"
        GoTo ExitHandler
    End If

    x = 1
    Set wkbTemp = Workbooks.Open(FileName:=FilesToOpen(x))
    wkbTemp.Sheets(1).Copy
    Set wkbAll = ActiveWorkbook
    wkbTemp.Close (False)
    wkbAll.Worksheets(x).Columns("A:A").TextToColumns _
      Destination:=Range("A1"), DataType:=xlDelimited, _
      TextQualifier:=xlDoubleQuote, _
      ConsecutiveDelimiter:=False, _
      Tab:=False, Semicolon:=False, _
      Comma:=False, Space:=False, _
      Other:=True, OtherChar:="|"
    x = x + 1

    While x <= UBound(FilesToOpen)
        Set wkbTemp = Workbooks.Open(FileName:=FilesToOpen(x))
        With wkbAll
            wkbTemp.Sheets(1).Move After:=.Sheets(.Sheets.Count)
            .Worksheets(x).Columns("A:A").TextToColumns _
              Destination:=Range("A1"), DataType:=xlDelimited, _
              TextQualifier:=xlDoubleQuote, _
              ConsecutiveDelimiter:=False, _
              Tab:=False, Semicolon:=False, _
              Comma:=False, Space:=False, _
              Other:=True, OtherChar:=sDelimiter
        End With
        x = x + 1
    Wend

ExitHandler:
    Application.ScreenUpdating = True
    Set wkbAll = Nothing
    Set wkbTemp = Nothing
    Exit Sub

ErrHandler:
    MsgBox Err.Description
    Resume ExitHandler
End Sub