Need help extracting data....

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bruce - 11 Jan 2007 22:41 GMT

I am not sure which way would be best for this... worksheet functions of VBA

Here is the situation, I have a large text file that I need to break info
out of.. This is not comma, or easyily delimited files, so I need to figure
out how to do this (I have kludged something, but it takes so long and I get
error messages due to memory. I KNOW there has to be a cleaner way to handle
this.

The data comes from a combination of 2 or 3 rows, has a varible length name
at the beginning, a "code", then the next 19 items are varible length and
seperated by spaces (these are the only spaces in this portion of the data,
so space could be used to delimit in this section), then the rest of the
string is again varible length, open text). I have a routine that assembles
the data into one line. (I am not sure if it is best to extract the data,
and then rejoin them in to one row, or to join the 2-3 rows of raw data and
then parse. joining the lines seems to make the most sense...

Here are a few samples of the data:
BALL JESSE 1 00 62 14.69 77.97 64.41 10.00 8.71 19.80 15.00 210.58 2 31
2260.00 .00 .00 .00 0000000000000000000000 0122002440220380000000 LOT 10 BLK
27 BHAM REALTY COS ADD N O 4

BENSON ANNE L ROBINSON 1 00 108 26.13 138.68 114.57 10.00 14.90 26.10 15.00
345.38 2 35 4020.00 .00 .00 .00 0000000000000000000000
0122002030300030000000 S 50 FT OF LOTS 1 & 2 & 3 BLK 7 MAR

CHARLES REBECCA & SUTCLIF 2 05 79 5.46 42.00 5.88 36.44 10.91 87.55 163.31
351.55 2 14 840.00 .00 .00 .00 0000000000000000000000 0229113600041940000000
S 90 FT LOT 17 BLK 7 OLLINGER & STE

Would it be best to use a function or use programing?
I am thinking it would be best to seperate the name from the rest - (I have
a routine that seems to do this ok, it is based on he fact of the number
right after the name, and a corralating code that I manually paste in to a
seperate column (this code is based on what file the records come from)

Any ideas?

Thanks!
Bruce

Reply to this Message

NickHK - 12 Jan 2007 04:31 GMT

Bruce,
Depends what you mean by large.
If <~5MB, then you read the whole file into a variable
Dim FileNum As Long

FileNum = FreeFile
Open "C:\Multiline.txt" For Input As #FileNum
Dim AllText As String
AllText = Input(LOF(FileNum), #FileNum)
Close #FileNum

'Assuming there is blank line between between entries, Split the records
Dim AllRecords As Variant
AllRecords = Split(AllText, vbNewLine & vbNewLine)

'Open a file to save the fixed output
FileNum= FreeFile
Open "C:\Multiline-Out.txt" For OutPut As #FileNum

'So now each element of the allrecords array contains the required data.
Combine all into 1 row
Dim RecordCount As Long
RecordCount = UBound(AllRecords) + 1
Dim i As Long
For i = 0 To RecordCount - 1
AllRecords(i) = Replace(AllRecords(i), vbNewLine, "")
Debug.Print AllRecords(i)
Print #FileNum, AllRecords(i)
Next

Close #FileNum

Instead of the Debug, you would probably want a FixUp function to quote out
the first (and last ?) text entries, as you seem to have already.
Then you can open the file in Excel.

NickHK

> I am not sure which way would be best for this... worksheet functions of VBA
>
[quoted text clipped - 36 lines]
> Thanks!
> Bruce

Reply to this Message