VB6: Sum elements of tab delimited text file

Greetings,

I have a tab-delimited text file that looks like this:

Species      Count
GWGU      3
CAGU      3
CAGU      250
GWGU      50
THGU      1
HERG      2
RNPH      10
RNPH      10
COMU      2

I would like to somehow produce the sums of the counts for each species, such that my output text file would look something like:

CAGU      253
COMU      2
GWGU      53
HERG      2
RNPH      20
THGU      1

The length of the file will vary as will the number of "Species".

I presume I would read the text file into an array, use the split function, and then loop through to sum the counts of each species element...but I can't seem to wrap my head around it.

Has anyone done this before and can provide some VB6 code?

Thanks,

Chris
sawmillsamAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
R_RajeshConnect With a Mentor Commented:
sorry forgot to add this. Goes at the begining (declaration section. outside any function)

Option Explicit
Type myType
Species As String
iCount As Variant
End Type

0
 
R_RajeshCommented:
Make sure taht Spedies and Count are seperated by tab.


ReDim myArr(0) As myType
Dim bFound As Boolean, i, TextLine
Open "C:\MyInitialfile.TXT" For Input As #1
Do While Not EOF(1)
Line Input #1, TextLine
For i = LBound(myArr) To UBound(myArr)
If myArr(i).Species = Trim(Split(TextLine, vbTab)(0)) Then
myArr(i).iCount = Val(myArr(i).iCount) + Val(Trim(Split(TextLine, vbTab)(1)))
bFound = True: Exit For
End If
Next i
If bFound = False Then
ReDim Preserve myArr(UBound(myArr) + 1) As myType
myArr(UBound(myArr)).Species = Trim(Split(TextLine, vbTab)(0))
myArr(UBound(myArr)).iCount = Trim(Split(TextLine, vbTab)(1))
End If
bFound = False
Loop
Close #1
Open "c:\MyFinalFile.TXT" For Output As #1
For i = LBound(myArr) To UBound(myArr)
Print #1, myArr(i).Species & vbTab & myArr(i).iCount
Next i
Close #1
0
 
sawmillsamAuthor Commented:
Thanks for your efforts

I am receiving a "Compile Error:  Invalid Qualifier" message for this line:

If myArr(i).Species = Trim(Split(TextLine, vbTab)(0)) Then

More specifically the myArr qualifier.

Any thoughts on how to rectify this?


0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
jmundsackCommented:
Here's a way to do this using ADO:

1. Create a new Standard EXE project
2. Remove Form1
3. Add a new Module
4. Add this code to the module:

Option Explicit

Public Sub Main()
    Dim oConn As Object
    Dim oRs As Object
    Set oConn = CreateObject("ADODB.Connection")
    oConn.Open _
        "Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
        "Dbq=c:\temp\;" & _
        "Extensions=asc,csv,tab,txt"
    Set oRs = CreateObject("ADODB.Recordset")
    oRs.Open "SELECT Species, SUM(Count) AS TotCount FROM species.txt GROUP BY Species", oConn, 3, 1, 1
    If Not (oRs.BOF And oRs.EOF) Then
        Do While Not oRs.EOF
            Debug.Print oRs.Fields(0).Value, oRs.Fields(1).Value
            oRs.MoveNext
        Loop
        oRs.Close
    End If
    oConn.Close
End Sub

5. Create a file in the same folder as Species.txt.  Name it Schema.ini, with the following contents:

[Species.txt]
Format=TabDelimited
ColNameHeader=True
MaxScanRows=0

6. Run the program.

HTH-Jon
0
 
jmundsackCommented:
About the above solution, NOTE:

1) you would have to change the c:\temp\ path in the connection string, to the location of your text file

2) you would have to change the species.txt in the SQL statement, to your text file name

If the object is to output this to another text file and you need help doing that, let me know.
0
 
sawmillsamAuthor Commented:
Thanks R_Rajesh.  Your solution is great.

jmundsack:  I think that R_Rajesh's solution better fit my needs...but I imagine your solution may help open my brain to a new way of addressing problems such as this.  Unfortunately, the old brain is not quite ready this week and I was able to implement R_Rajesh's solution first.  But I want to thank you as well for your efforts.

Chris
0
 
R_RajeshCommented:
Thanks for the Grade!

Rajesh
0
 
jmundsackCommented:
Yeah, SQL takes a lot of the complexity out of the code, especially where in your case it's a simple SUM..GROUP BY statement.  Had your species counts been in an actual database, everyone's first tendency would be to use SQL to get this result--not many people immediately think to use SQL for tab-delimited text files, but in many cases it can be a lot easier.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.