My hope in posting this here is that some Visual Basic genius will take this script and figure out how to automatically make graphs for each unit so you can see trending information.
matthew
awk script:
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