CODEWITHSUNDEEP
csvvb6
Defektive
Defektive

Reputation: 1

Reading in .CSV file in VB6 and finding the max numerical value:

I'm having a terrible time trying to figure out how to bring in a .CSV file, read in each data which are integers, and simple find the maximum value of the file.

I have finished this opening the file in excel and doing the calculations in excel using VB6. However, I would like to not have to use any external programs.

Please can someone help explain how to open a .CSV file, and read a data entry into a variable.

Been at this for hours and it's driving me absolutely insane.

I can easily do the rest from there.

Thanks guys/gals...

Upvotes: 0

Views: 6288

Answers (3)

Bob77
Bob77

Reputation: 13267

The Jet Text IISAM can be a powerful tool for all kinds of processing of files containing tabular text. You can do simple queries, JOINs among files, embed VBA Expression Service expressions in the queries, transform an input file to another output format, and lots of other things.

By adding a schema.ini file you have finer control over delimiters, column naming, data type formats, and can process fixed-field text as well.

For simply finding the maximum of a column though:

'Requires a reference to ADO 2.5 or later.

Private Function GetMax( _
    ByVal FieldNum As Integer, _
    ByVal CSVFile As String, _
    Optional ByVal CSVFolder As Variant) As Variant
    'Find max value in field FieldNum of CSVfile, assumed
    'here to be a HDR=No CSV file so field names are F1,
    'F2, etc.

    Dim rsResult As ADODB.Recordset

    If IsMissing(CSVFolder) Then
        CSVFolder = App.Path
    ElseIf VarType(CSVFolder) <> vbString Then
        CSVFolder = App.Path
    End If

    Set rsResult = New ADODB.Recordset
    With rsResult
        .CursorLocation = adUseClient
        .Open "SELECT Max(F" & CStr(FieldNum) & ") FROM [" & CSVFile & "]", _
              "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" _
              & CSVFolder _
              & "';Extended Properties='Text;HDR=No'", _
              adOpenStatic, adLockReadOnly, adCmdText
        GetMax = .Fields(0).Value
        .Close
    End With
End Function

While this can look like overkill, consider it the "germ" of many other text table processing ideas.

This is as effective and useful in VBScript as in VB.

See Processing Text Databases for more information.

Upvotes: 2

Matt H
Matt H

Reputation: 6530

Dim F As Long, d As Double, dMax As Double
F = Freefile
Open "file.csv" For Input As F
While Not Eof(F)
    Input #F, d
    If d > dMax Then dMax = d
Wend
Close F
MsgBox "Max is " & dMax

That assumes only one column in the CSV. Use

Input #F, col1, col2, col3

etc... For multiple columns.

Upvotes: 3

JeffK
JeffK

Reputation: 3039

It looks like the root of your problem is reading the file a line at a time. I'd use the Scripting.FileSystemObject http://support.microsoft.com/kb/186118 to do that job. It's much easier to use than the built-in Input statement. Here is some code from that page:

Private Sub Command1_Click()
   'Declare variables.
    Dim fso As New FileSystemObject
    Dim ts As TextStream
    'Open file.'
    Set ts = fso.OpenTextFile(Environ("windir") & "\system.ini")
   'Loop while not at the end of the file.'
    Do While Not ts.AtEndOfStream
        Debug.Print ts.ReadLine
    Loop
    'Close the file.'
    ts.Close
End Sub

Once you've got a line of text, use Split to get an array of values, then inspect each one to see if it's numeric. If it is, include the value in your search for the maximum. See this question for some solutions to a similar question that may help out as well: Is there a VB6 function analagous to C's sscanf?

Upvotes: 1

Related Questions