EXCEL VBA ADO copy CSV and separate by semicolon not comma

Question

What I am trying:

copy all data from a closed CSV (works)
paste it separated

initial position:

CSV data is delimited by semicolon ;
the code below copies everything, but only separates by comma , (pretty bad for europeans -.-')

Sub GetDatafromCSV()

    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim FileName As String
    
    Set cn = New ADODB.Connection
    
        cn.ConnectionString = _
        "Provider=Microsoft.ACE.OLEDB.16.0;" & _
        "Data Source=" & GetLocalPath(ThisWorkbook.Path) & "\;" & _
        "Extended Properties='text;HDR=YES;FMT=Delimited(';')'"
    
    cn.Open
    
    Set rs = New ADODB.Recordset
    
    rs.ActiveConnection = cn
    rs.Source = "SELECT * FROM [Test.csv]"
    rs.Open
    
    Tabelle2.Range("A21").CopyFromRecordset rs
    
    rs.Close
    cn.Close

End Sub

I have been googling for a few hours now but at this point i just get the same search results over and over again. What have i tried:

schema.ini file didnt work, its also not practical at all having to create a new ini file for every new CSV
MS Text Driver not working because I am using 64 bit?!
Jet 4.0 not working because i am using 64 bit?!
I tried "Extended Properties='text;HDR=YES;FMT=Delimited**(;)**'" -> error
trying to find the Delimiter "," in the registry and change to ";" but couldnt find it

My VBA knowledge is very little as you can read, i am basically just copying all the code together

Edit:

Thanks for the suggestion (CSV with comma or semicolon?) but my system operator already is ";" and it doesnt change a thing sadly.

Does this answer your question? [CSV with comma or semicolon?](https://stackoverflow.com/questions/10140999/csv-with-comma-or-semicolon) — Solar Mike, Apr 05 '23 at 12:29

score 0 · Answer 1 · answered Apr 05 '23 at 14:45

Try using TextToColumns after the import. This assumes there are no semi-colons in the data.

Option Explicit

Sub GetDatafromCSV()
    Dim cn As ADODB.Connection, r As Long
    Set cn = New ADODB.Connection
    With cn
        .ConnectionString = _
        "Provider=Microsoft.ACE.OLEDB.16.0;" & _
        "Data Source=" & ThisWorkbook.Path & ";" & _
        "Extended Properties='text;HDR=YES;FMT=Delimited'"
       .Open
    End With

    Const SQL = "SELECT * FROM [Test.csv]"
    With Sheet1
        .Range("A21").CopyFromRecordset cn.Execute(SQL)
        r = .Cells(.Rows.Count, "A").End(xlUp).Row
        Application.DisplayAlerts = False
        .Range("A21:A" & r).TextToColumns Destination:=.Range("A21"), _
               DataType:=xlDelimited, Semicolon:=True
         Application.DisplayAlerts = True
    End With
    cn.Close
End Sub

thanks a lot! but it cuts off after column J, there is data in K to W. it even copys all headers correctly. Any idea? Edit: I now understand what happened, the recordset still got split into Column A, B, C as efore because of the default delimiter ",".... so i guess i will try without FMT:Delimited — Pl0x_H3lp, Apr 05 '23 at 15:10

EXCEL VBA ADO copy CSV and separate by semicolon not comma

1 Answers1