CODEWITHSUNDEEP
vbaexcelcsv
Dumitru Daniel
Dumitru Daniel

Reputation: 549

VBA, Import CSV split by ";" to sheet

I am trying to import a CSV file split by semicolon ";" into an excel object so I can use it later on.

Ideally i would like to use ADO, DAO or ADODB so I can also run SQL queries on the object, and get sum of specific fields, or total number of fields and so on.

So far i've gotten the code below, but it does not split the data by ";", so it all comes back as 1 field instead of multiple fields that can be handled.

Sub Import()
   Dim conn As New ADODB.Connection
   Dim rs As New ADODB.Recordset
   Dim f As ADODB.Field

   Dim csvName, csvPath

    csvPath = ActiveWorkbook.path
    csvName = "fileName.csv"

   conn.Open "DRIVER={Microsoft Text Driver (*.txt; *.csv)};DBQ=" & csvPath & ";"
   rs.Open "SELECT * FROM " & csvName, conn, adOpenStatic, adLockReadOnly, adCmdText

   Debug.Print rs.Fields

   While Not rs.EOF
      For Each f In rs.Fields
         Debug.Print f.Name & "=" & f.Value
      Next
   Wend
End Sub

Can anyone give me an idea how I can also split the data by ";" and query it using SQL query? Or a different object that I could load a CSV into and query certain columns.

Upvotes: 0

Views: 1021

Answers (2)

Dumitru Daniel
Dumitru Daniel

Reputation: 549

The only answer I found that was usable was to create an ini file in the current folder, and enter the delimiter in the ini file.

iniPath = activeworkbook.path & "\"
iniName = "schema.ini"
iniPathName = iniPath & iniName
If Not fso.FileExists(iniPathName) Then
    fso.CreateTextFile (iniPathName)
End if

Upvotes: 0

JohnyL
JohnyL

Reputation: 7142

Here's example:

Public Sub QueryTextFile()

    Dim rsData As ADODB.Recordset
    Dim sConnect As String
    Dim sSQL As String

    ' Create the connection string.
    sConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
               "Data Source=C:\Files\;" & _
               "Extended Properties=Text;"

    ' Create the SQL statement.
    sSQL = "SELECT * FROM Sales.csv;"
    Set rsData = New ADODB.Recordset
    rsData.Open sSQL, sConnect, adOpenForwardOnly, _
                adLockReadOnly, adCmdText

    ' Check to make sure we received data.
    If Not rsData.EOF Then
        ' Dump the returned data onto Sheet1.
        Sheet1.Range("A1").CopyFromRecordset rsData
    Else
        MsgBox "No records returned.", vbCritical
    End If

    ' Clean up our Recordset object.
    rsData.Close
    Set rsData = Nothing

End Sub

Upvotes: 2

Related Questions