Query Text File Using SQL (VBA)

[nataliek92]

This is probably quite a broad question but any help whatsoever would be much appreciated!

I've been looking around online and believe this can be done... but I can't figure out how.

I would like VBA for Excel to run a code which uses microsoft SQL to query a text file. I'll give a simple example...

Say I have a text file called "MyTable" with some data in it, like this:

I.D.	Name	Age
1	Adam	24
2	Betty	45
3	Colin	78

<tbody>
</tbody>

I also have an SQL string which queries this table:

SELECT AVG(Age) FROM MyTable

Basically I would like a VBA code which does something like this:

Sub QueryTextFile()

    Dim cnn As TEXTFILE.Connection
    Dim str As String
    
    Set cnn = New TEXTFILE.Connection
    cnn.ConnectionString = "TEXT FILE CONNECTION STRING"
    cnn.Open
  
    Dim rs As TEXTFILE.Recordset
    Set rs = New TEXTFILE.Recordset
    
    str = "SELECT AVG(Age) FROM MyTable"
  
    With rs
            .ActiveConnection = cnn
            .Open str
            Sheet1.Range("A1").CopyFromRecordset rs
            .Close
    End With
  
    cnn.Close
    Set rs = Nothing
    Set cnn = Nothing

End Sub

This code should then place the average age (49) into cell A1 on Sheet1.

I know this is probably not as simple as I've made out but wanted to give a basic idea of what I'm looking for.

Any thoughts?

 

[Kyle123]

It's exactly as easy as that:

Sub QueryTextFile()

    Dim cnn As ADODB.Connection
    Dim str As String
    
    Set cnn = New ADODB.Connection
    cnn.Provider = "Microsoft.Jet.OLEDB.4.0"
    cnn.ConnectionString = "Data Source=" & "C:\My Data Folder\" & ";" & "Extended Properties=""text;HDR=Yes;FMT=Delimited;"""
    cnn.Open
  
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    
    str = "SELECT AVG(Age) FROM [MyTextFile.csv]"
  
    With rs
        .ActiveConnection = cnn
        .Open str
        Sheet1.Range("A1").CopyFromRecordset rs
        .Close
    End With
  
    cnn.Close

End Sub