CodeItBetter Programming Another VB Programming Blog

'Database - How to Load and sort a CSV file by using ADO
'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