Solved

VB6:  Sum elements of tab delimited text file

Posted on 2006-10-19
8
637 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
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

 
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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
using Access 8 85
VBA filters 2 73
VB6 common control 6 sp6 object library not registerd 3 49
VBA to find and replace multiline text from VBA modules 8 92
Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

696 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