VBA, Import CSV split by ";" to sheet

Question

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.

I think you will have all your answers here: https://stackoverflow.com/a/11267603/78522 — iDevlop, Dec 06 '17 at 09:49
@DumitruDaniel first, find the time to give credit to previous posts you had here. You have given 0 feedbacks so far. — Shai Rado, Dec 06 '17 at 09:53
16 questions and no accepted answers so I guess we can assume you will not make the effort to accept any answers this time either. — Nick.Mc, Dec 06 '17 at 09:55
Hi @Patrick Honorez, thank you, but i need to load the data in an object, not just copy it in my sheet. — Dumitru Daniel, Dec 06 '17 at 16:43
Hi Nick.McDermaid. I did not know where the accept answer checkbox was untill recently. The checkbox was not very intuitive, as i have an older flat screen monitor, and i could barely see it. Now I know, I will start checking them out. There is no need to down-vote this post, it has nothing to do with it. — Dumitru Daniel, Jan 04 '18 at 11:32

score 2 · Answer 1 · answered Dec 06 '17 at 10:04

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

Hi Johny, Thank you for response, but it's not splitting the data correctly. Please keep in mind that my CSV file is split by ";" — Dumitru Daniel, Dec 06 '17 at 16:35
Please, see [this](https://www.connectionstrings.com/textfile/) and [this](https://www.codeproject.com/Articles/26822/Read-Text-File-txt-csv-log-tab-fixed-length) for delimiter paramaters. — JohnyL, Dec 06 '17 at 19:08

score 0 · Answer 2 · answered Jan 04 '18 at 11:41

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

VBA, Import CSV split by ";" to sheet

2 Answers2