Solved

VB6:  Sum elements of tab delimited text file

Posted on 2006-10-19
8
639 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
8 Comments
 
LVL 24

Expert Comment

by:R_Rajesh
ID: 17768248
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
ID: 17768928
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
ID: 17768975
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 13

Expert Comment

by:jmundsack
ID: 17769041
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
 
LVL 13

Expert Comment

by:jmundsack
ID: 17769390
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
ID: 17769613
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
ID: 17769641
Thanks for the Grade!

Rajesh
0
 
LVL 13

Expert Comment

by:jmundsack
ID: 17769886
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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…

688 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