Text File Manipulation Help 1

[tmangin]

Hi,
I am in the process of writing code that does the following:

1. reads text file and counts the number of pipe characters (|) present. (Each pipe in this file represents the end of a record)
2. as the code reads the text, it compares ID #'s after each pipe to see if they have changed. If so, the code prints to output the new ID #.

What I am stuck on right now is that along with counting the TOTAL number of records (which I've already successfully coded), I also need to count the number of records that have the same ID #. In other words, have a separate counter that adds one until the ID # after a pipe changes. Here is the code I have now to count the pipes and print the different ID's:

Pipecnt = 0
For Counter = 1 To Len(FileString)

If Asc(Mid(FileString, Counter, Len(FileString))) = 124 Then
sContractName = Mid(FileString, Counter, 17)
ContractName = Mid(sContractName, 14, 2)
SiteName = Mid(sContractName, 16, 2)

If SiteName <> StrSiteName Then
Debug.Print ContractName & SiteName
Call PContractName
End If
StrSiteName = SiteName
Pipecnt = Pipecnt + 1
End If
Next

Any suggestions would be GREATLY appreciated! Thanks!

 

[paulbent]

How about:

DupCount = 0 'Duplicates counter
Pipecnt = 0
For Counter = 1 To Len(FileString)

If Asc(Mid(FileString, Counter, Len(FileString))) = 124 Then
sContractName = Mid(FileString, Counter, 17)
ContractName = Mid(sContractName, 14, 2)
SiteName = Mid(sContractName, 16, 2)

If SiteName <> StrSiteName Then
Debug.Print ContractName & SiteName
Call PContractName
Else
'Increment the dup count
DupCount = DupCount + 1
End If
StrSiteName = SiteName
Pipecnt = Pipecnt + 1
End If
Next


Paul Bent
Northwind IT Systems

http://www.northwindit.co.uk

 

[vbSun]

Just my thoughts, may be it will help you save some code.

1. If it is not a big file, manipulate it in the memory as a whole.
2. You can use fileContents= Input(LOF(fnum), fnum) to store the complete contents to the memory.
3. You can use Ubound(Split(filecontents,&quot;|&quot) + 1 to get the number of pipe chars.
4. Use the same logic to find the number of occurances of an ID.. Save the id and split the file contents using the ID and check the UBound.is that what you want?

Sunil

 

[tmangin]

paulbent,
I appreciate your help, but the DupCount is not working. It gives me zero for each ID.

Thanks!

 

[tmangin]

vbSun,

I think I follow your idea, but in order to use the same methodology to find the ID's, do I have to search for a specific ID? I don't know the ID's to search for...just know that they are n characters after the pipe char.

Thanks!

 

[paulbent]

I think I misunderstood. To print a count for each id, try:

Pipecnt = 0
For Counter = 1 To Len(FileString)

If Asc(Mid(FileString, Counter, Len(FileString))) = 124 Then
sContractName = Mid(FileString, Counter, 17)
ContractName = Mid(sContractName, 14, 2)
SiteName = Mid(sContractName, 16, 2)

If SiteName <> StrSiteName Then
If Counter > 1 Then
Debug.Print StrSiteName & _
&quot; occurred &quot; & Cstr(IDCount) & &quot; times&quot;
IDCount = 0
End If
Debug.Print ContractName & SiteName
Call PContractName
Else
'Increment the id count
IDCount = IDCount + 1
End If
StrSiteName = SiteName
Pipecnt = Pipecnt + 1
End If
Next


Paul Bent
Northwind IT Systems

http://www.northwindit.co.uk

 

[tmangin]

paulbent,
Thanks so much for the response. It works, however, all of the counts for each ID are one less than they should be. Do you have any idea why this is happening?

Thanks!

 

[vbSun]

Letus try this..

Dim strTmp() as string

strTmp = Split(strContentsofFile,&quot;|&quot
Dim Counter as Double
for i=lbound(strTmp) to ubound(strTmp)
Counter = 0
For j=lbound(strTmp) to ubound(strTmp)
If left(strTmp(i),myIDlength) = left(strTmp(i),myIDlength) Then
counter=counter+1
End If
Next
Debug.Print ID,Counter
Next

This is not optimised at all, cos it checks Counter times each id. You should also save the searched id to a temporary array and check whether it has been counted before. You can do that coding in the Debug.Print line.. and check it before the inner loop executes..

Best of Lucks
Sunil

 

[paulbent]

Ah yes, I see that I reset the counter to 0 and therefore didn't count the first new instance. Reset it to 1 instead.

If SiteName <> StrSiteName Then
If Counter > 1 Then
Debug.Print StrSiteName & _
&quot; occurred &quot; & Cstr(IDCount) & &quot; times&quot;
IDCount = 1
End If
Debug.Print ContractName & SiteName
Call PContractName
Else
'Increment the id count
IDCount = IDCount + 1
End If
StrSiteName = SiteName
Pipecnt = Pipecnt + 1
End If

Paul Bent
Northwind IT Systems

http://www.northwindit.co.uk

 

[tmangin]

paulbent and vbSun,
Thanks so much to both of you for your help. I think I am going to use paulbent's suggestion.

paulbent-
I've got one more question. Right now, I'm getting an extra &quot;occurred 0 times&quot; in the beginning of my output. I think I know why: The text file I'm reading does not begin with a pipe, so therefore, the code misses the first ID, which is located on the first line of the file. Do you have any suggestions as to how I can write code that will read that first ID, compare it to the second ID (which is after the first pipe char), and if they are the same, increment IDCount?

Thanks so much again for both of your help!