Load and sort a CSV file by using ADO

VB Helper: HowTo: Load and sort a CSV file by using ADO

Load and sort a CSV file by using ADO

Description
This example shows how to load and sort a CSV file by using ADO in Visual Basic 6. It uses the Microsoft Text Driver to execute an an SQL SELECT statement that selects records from the file and sorts them.

Keywords:
CSV, sort, ADO, database, data

To select the data, the program opens an ADO connection using the Microsoft Text Driver. The connection string tells the driver what directory it will work from. The SQL SELECT statement can then treat CSV files as if they were tables.

The code selects records from the file and orders them. It displays the results in a ListBox.

Private Sub cmdLoad_Click()
Dim Cn1 As ADODB.Connection
Dim Rs1 As ADODB.Recordset
Dim iSQLStr As String
Dim field_num As Integer

    Set Cn1 = New ADODB.Connection
    Cn1.ConnectionString = _
        "Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
        "DefaultDir=" & txtDir.Text
    Cn1.Open

    lstResults.Visible = False
    DoEvents

    iSQLStr = "Select * FROM " & txtFile.Text & _
        " ORDER BY " & txtField.Text
    field_num = CInt(txtField.Text) - 1
    Set Rs1 = Cn1.Execute(iSQLStr)
    lstResults.Clear
    While Not Rs1.EOF
        If IsNull(Rs1.Fields(field_num).Value) Then
            lstResults.AddItem "<null>"
        Else
            lstResults.AddItem Rs1.Fields(field_num).Value
        End If
        Rs1.MoveNext
    Wend

    lstResults.Visible = True
End Sub