VB6:  Sum elements of tab delimited text file

sawmillsam used Ask the Experts™

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?


Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

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
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


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?

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

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\;" & _
    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
    End If
End Sub

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


6. Run the program.

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.


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.


Thanks for the Grade!

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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial