Many times there is more than one solution to a problem - as Robert Gebeloff discovered in late November when faced with an unusual parsing dilemma. His post to the NICAR-L thread elicited about eight different solutions. Here's the problem: a state agency had grudgingly turned over some data in ASCII, delimited with semicolons, with each record ending in the person's name. The big problem, though, was that there wasn't a delimiter between records. Here's a sample: GRADY,SELMA ;8M MIGLIORE MANOR ;ELIZABETH;NJ;07206-64; NB00010500; Z;99;19340127;SELMA GRADY Gebeloff wanted to know if there was a way to construct a search/replace that would recognize the name reversal at the end of the record and insert some kind of delimiter. Here's a few of the suggestions, including the ultimate solution Gebeloff used:
Suggestion one:
Matthew Ericson, of The Philadelphia Inquirer, suggested using Perl to construct a search-and-replace expression that will put a line break after each record: perl -p-e's/((.+?),(.+?)\s*;+?)\3 \2\s*/\1\n/g;"datafile.txt More information about Perl is available www.perl.com
Suggestion two:
Paul Skolnick, of Thunder & Lightning News Service in Belleair Bluffs, Fla., suggested this solution: run the text file through a macro in a (newer version) word-processing program to parse the name fields. Since the first and last words in the records are the same, you would insert a carriage return-line feed after the second appearance and set that off as one distinct record. The macro recorder in Word 2000 is in the Tools menu. It has a Record function in it, but you would have to doctor the recorded macro some.
Suggestion three:
Offered by David Heath, of The Seattle Times, this solution preserves the original data, so commas before first and middle names are kept. This program takes bits of the file to work with at a time, which requires more code, but ultimately makes the program run faster.
Declare variables, including an array for the nine fields. Dim intReadFile, intWriteFile As Integer Dim strLine, strTempLine As String Dim strField(1 To 9) As String Dim X, ctr As Integer Dim lngCheckName, lngLengthOffields As Long
Open the text file and the output file intReadFile = FreeFile Open "d:\data\parse\newhha1.txt" For Input As #intReadFile intWriteFile = FreeFile Open "d:\data\parse\output.txt" For Output As #intWriteFile
Print the field names in the first line of the output file Print #intWriteFile, "Name" + "Address" + + "State" "," + "ZipCode" + "LicenseNo" "Code" + + "Expires" Start a loop to read the text file. In actuality, it will loop only once. Do While Not EOF(intReadFile) Stuff the whole file into a variable "strLine" Line Input #intReadFile, strLine
Start a second loop to read smaller chunks of the file This program is made more complicated by efforts to speed up the processing. It obviates the need to resave the whole file into memory after parsing each field. Do While Len(strLine) > 0 Initialize the counters 1 ctr = ctr + 1 lngLengthOffields = 0
Cut a chunk of 5000 bytes off the file and manipulate it. This makes the program run much faster. strTempLine = Mid(strLine, 1, 5000)
Feed the nine fields into an array, using semi-colons as the delimiter For X = 1 To 9
If PositionOfSemiColon(strTempLine) = 0 Then Exit Do strField(x) = Mid(strTempLine, 1, PositionOfSemiColon(strTempLine) - 1) strTempLine = TruncateLine(strTempLine) lngLengthOffields = lngLengthOffields + Len(strField(x)) + 1 Next X
Cut off the start of the file already fed into the array strLine = LTrim(Mid(strLine, lngLengthOffields + 1))
Measure the length of the extraneous data and cut it out of the next record If ctr = 1 Then lngCheckName = Len(strField(1)) If ctr > 1 Then strField(1) = LTrim(Mid(strField(1), lngCheckName)) lngCheckName = Len(strField(1)) End If
Write the data to a comma-delimited file Write #intWriteFile, LTrim(strField(1)), LTrim(strField(2)), strField(3), strField(4), strField(5), strField(6), strField(7), strField(8) Loop Loop
Close the input and output files Close Notify the user when the program is done retval = MsgBox("All Done", vbOKOnly, "Go Home") End End Sub
Create a function to locate semi-colons Private Function PositionOfSemiColon(String1 As String) PositionOfSemiColon = InStr(1, String1, ";", vbTextCompare) End Function
Create a function to cut data from the file once read Private Function TruncateLine(String1 As String) TruncateLine = Mid(String1, PositionOfSemiColon(String1)) End Function
Solution:
A Visual Basic script, drafted by Tim Henderson, of The Miami Herald, and modified slightly by Gebeloff, creates a text file and nine variables.
It then creates a loop that goes through the data looking for field delimiters. The contents of the first nine fields are assigned to the variables, and then it stacks the next nine records underneath, and so on until it runs out of data. The result is theoretically a tab-delimited text file that has line breaks after each record.
Gebeloff discovered, however, that his software didn't recognize tabs, so he changed the script to insert a comma between fields in the new table.
He also had to swap in commas for semicolons, which meant getting rid of extraneous commas in the original data. The script (run from Word's VB Editor):
Sub ParseDelimited() variables for the two text files we need Dim outfile As Integer Dim infile As Integer
variables for the fields we create Dim add As String, city As String, st As String, zip As String, licno As String, code As String, expires As String, dob As String, name As String
Open the file to receive the parsed fields outfile = FreeFile Open "c:\temp\hhc2.txt" For Output As outfile Open the original datafile infile = FreeFile Open "c:\temp\healthaides\hhc2.txt" For Input As infile
Loop through the file inputting the variables Do While Not EOF(infile) Input #infile, add, city, st, zip, licno, code, expires, dob, name Each time you get all 9 fields put them in the new file separated by commas Print #outfile, add & "," & city & "," & st & "," & zip & "," & licno & "," & code & "," & expires & "," & dob & "," & name Loop Clean up Close #infile Close #outfile End Sub
MaryJo Sylwester can be reached by e-mail at maryjo@nicar.org