Solved

VB6:  Sum elements of tab delimited text file

Posted on 2006-10-19
8
633 Views
Last Modified: 2008-01-09
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
0
Comment
Question by:sawmillsam
  • 3
  • 3
  • 2
8 Comments
 
LVL 24

Expert Comment

by:R_Rajesh
Comment Utility
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
 

Author Comment

by:sawmillsam
Comment Utility
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
 
LVL 24

Accepted Solution

by:
R_Rajesh earned 250 total points
Comment Utility
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
 
LVL 13

Expert Comment

by:jmundsack
Comment Utility
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 13

Expert Comment

by:jmundsack
Comment Utility
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
 

Author Comment

by:sawmillsam
Comment Utility
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
 
LVL 24

Expert Comment

by:R_Rajesh
Comment Utility
Thanks for the Grade!

Rajesh
0
 
LVL 13

Expert Comment

by:jmundsack
Comment Utility
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

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now