Solved

# find maximum and minimum from text file

Posted on 2006-07-16
332 Views
I have a text file with 2 column and about 2000 rows of value. It looks like below

709.0 1220.0
391.0 1181.0
295.0 303.0
289.0 1303.0
2714.0 303.0
2745.0 1303.0
261.0 1254.0
And so on…

I want to write a program to find the max value for column 1 and minimun value for column 2. please help me to do it. thanks in advance.
0
Question by:rmtogether
[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
• 4
• 2
• 2
• +2

LVL 35

Accepted Solution

Raynard7 earned 150 total points
ID: 17119123
Dim fileName as string
Dim fileNo as integer

Dim maxCol1 as double
Dim minCol2 as double
Dim firstRow as boolean

Dim varString as string
Dim varArray as variant

fileName = "C:\temp\fileName.txt"
fileNo = freefile
open fileName for input as #fileNo

firstRow = false

Do while not eof(fileno)
line input #fileNo, varString
varArray = split(varString, " ")
if (firstRow = false) then
maxCol1 = varArray(0)
minCol2 = varArray(1)
firstRow = true
else
if (varArray(0) > maxCol1) then
maxCol1 = varArray(0)
end if

if (varArray(1) < minCol2) then
minCol2 = varArray(2)
end if
end if
loop

Debug.print "Maximum Value From Col1 is " & maxCol1
Debug.print "Minimum Value From Col2 is " & minCol2
0

LVL 35

Expert Comment

ID: 17119126
You can do this with file system objects - but this works just as well,

It opens the file, splits the results into an array (using " " as the delimiter)

The first loop it puts the initial values into the max and min values.

Each subsequent loop it compares the previous max and min values with the current row and changes if they are greater / less
0

Author Comment

ID: 17119248
thank you!!

but,
I have complie problem in the line minCol2 = varArray(2). the error message is "subscript out of range" could you please help me debug this problem
0

LVL 6

Assisted Solution

tone28 earned 50 total points
ID: 17119299
Change

minCol2 = varArray(2).

to

minCol2 = varArray(1)

Tone
0

Author Comment

ID: 17119399
thanks, it works

by the way, how can I modify it if I want to get both max and min values for both columns? like below

Maximum Value From Col1
Minimum Value From Col1

Maximum Value From Col2
Minimum Value From Col2
0

LVL 16

Assisted Solution

jimbobmcgee earned 100 total points
ID: 17120811
Based on the above code, try:

Dim fileName as string
Dim fileNo as integer

Dim maxCol1 as double
Dim minCol1 as double
Dim maxCol2 as double
Dim minCol2 as double
Dim firstRow as boolean

Dim varString as string
Dim varArray as variant

fileName = "C:\temp\fileName.txt"
fileNo = freefile
open fileName for input as #fileNo

firstRow = false

Do while not eof(fileno)
line input #fileNo, varString
varArray = split(varString, " ")
if (firstRow = false) then
maxCol1 = varArray(0)
minCol1 = varArray(0)
maxCol2 = varArray(1)
minCol2 = varArray(1)
firstRow = true
else
if (varArray(0) > maxCol1) then
maxCol1 = varArray(0)
end if
if (varArray(0) < minCol1) then
minCol1 = varArray(1)
end if
if (varArray(1) > maxCol2) then
maxCol2 = varArray(0)
end if
if (varArray(1) < minCol2) then
minCol2 = varArray(1)
end if
end if
loop

Debug.print "Maximum Value From Col1 is " & maxCol1
Debug.print "Minimum Value From Col1 is " & minCol1
Debug.print "Maximum Value From Col2 is " & maxCol2
Debug.print "Minimum Value From Col2 is " & minCol2

HTH

J.
0

LVL 45

Assisted Solution

aikimark earned 200 total points
ID: 17121329
I realize you have a solution, but consider the following:
1. you will be storing values in an array after you need them
2. you will be parsing a line needlessly
3. your line parsing requires extra datatype conversions (and variants are slow)
4. your variables' double datatypes are a bit of overkil, given the meager size of your data variables.

Consider the following alternative solutions:
A. Less-than-optimal, comparing every value twice, even if a match is found on the first comparison

Dim sngMin1 As Single
Dim sngMax1 As Single
Dim sngMin2 As Single
Dim sngMax2 As Single
Dim sngColumn1 As Single
Dim sngColumn2 As Single
Dim intFN As Integer

intFN = FreeFile
sngMax1 = -3.402823E+38
sngMax2 = -3.402823E+38
sngMin1 = 3.402823E+38
sngMin2 = 3.402823E+38

Open "e:\temp\minmax.txt" For Input As #intFN
Do Until EOF(intFN)

Input #intFN, sngColumn1, sngColumn2

If sngColumn1 > sngMax1 Then sngMax1 = sngColumn1
If sngColumn1 < sngMin1 Then sngMin1 = sngColumn1
If sngColumn2 > sngMax2 Then sngMax2 = sngColumn2
If sngColumn2 < sngMin2 Then sngMin2 = sngColumn2

Loop

Debug.Print "Min1: " & sngMin1, "Max1: " & sngMax1
Debug.Print "Min2: " & sngMin2, "Max2: " & sngMax2

Close #intFN

=================================
B. comparison and loop optimizations
Dim sngMin1 As Single
Dim sngMax1 As Single
Dim sngMin2 As Single
Dim sngMax2 As Single
Dim sngColumn1 As Single
Dim sngColumn2 As Single
Dim intFN As Integer

intFN = FreeFile

Open "e:\temp\minmax.txt" For Input As #intFN
Input #intFN, sngColumn1, sngColumn2
sngMax1 = sngColumn1  'initial values
sngMin1 = sngColumn1
sngMax2 = sngColumn2
sngMin2 = sngColumn2

Input #intFN, sngColumn1, sngColumn2

Do While True

Select Case sngColumn1
Case Is > sngMax1
sngMax1 = sngColumn1
Case Is < sngMin1
sngMin1 = sngColumn1
End Select
Select Case sngColumn2
Case Is > sngMax2
sngMax2 = sngColumn2
Case Is < sngMin2
sngMin2 = sngColumn2
End Select

If EOF(intFN) Then Exit Do

Input #intFN, sngColumn1, sngColumn2
Loop

Debug.Print "Min1: " & sngMin1, "Max1: " & sngMax1
Debug.Print "Min2: " & sngMin2, "Max2: " & sngMax2

Close #intFN

'Note: based on your knowledge of the data or performance profiling, you might reverse the order of these Case clauses, checking for a new MIN value prior to a new Max value
0

LVL 45

Expert Comment

ID: 17121347
You can also use SQL to accomplish this task, treating the text file like a table and using the ISAM drivers built into Jet/ADO.
0

Author Comment

ID: 17122036
hi, jimbobmcgee

thank you
your code works good, but I think you have little bug in the following. should make the following changes

If (varArray(0) < minCol1) Then
minCol1 = varArray(1)   --------------------->minCol1 = varArray(0)
End If
If (varArray(1) > maxCol2) Then
maxCol2 = varArray(0)   --------------------->maxCol2 = varArray(1)
End If
0

Author Comment

ID: 17122129
thanks guys,

thanks to all of your helps. I have got all I want.
I have another questions related to this topic. could you guys help me more about my question.

http://www.experts-exchange.com/Programming/Programming_Languages/Visual_Basic/Q_21921693.html

0

## Featured Post

Question has a verified solution.

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

### Suggested Solutions

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
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 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…
###### Suggested Courses
Course of the Month2 days, 23 hours left to enroll