0

I use ADODB.Connection and ADODB.Recordset to get data from csv files. The problem I am facing is that the delimiter seems not to work in case of semicolon (or other than comma). I am working with a semicolon as a delimiter. This is my code:

Public Function getDataFromFile(path As String, filename As String) As ADODB.Recordset

    Dim cN As ADODB.Connection
    Dim RS As ADODB.Recordset

    Set cN = New ADODB.Connection
    Set RS = New ADODB.Recordset

    cN.Open ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & path & ";" & _
             "Extended Properties=""text;HDR=NO;FMT=Delimited(;);IMEX=1;""")

    RS.ActiveConnection = cN
    RS.Source = "select * from " & filename
    Set getDataFromUrl = RS

End Function

When I replace all semicolons to commas in the csv file everything works fine (same code, even with "FMT=Delimited(;)"). But it doesn't work with semicolon as delimiter.

clex
  • 465
  • 2
  • 7
  • 19

1 Answers1

1

As far as I know, you have to use a file "Schema.ini" that needs to exists in the same folder as the file. And, to make it worse, it needs to have the filename in it. I made two example files, one semicolon-separated, one with Tab:

[textfileWithSemicolon.csv]
ColNameHeader=True
Format=Delimited(;)

[textfileWithTAB.csv]
ColNameHeader=True
Format=TabDelimited

As your routine gets the path and file as parameter, maybe the easiest way for you is to copy the file to a fixed folder with a fixed name and put the Schema.ini file there.

An alternative would be to create the Schema.ini on the fly, but that require that you have write permission on the passed folder (and that noone else is doing the same on that folder in the same moment...)

For more information about the Schema.ini, look at Microsoft Docs

(There seems to be also a way to change the default delimiter in the registry, but I don't think that this a good solution)

FunThomas
  • 23,043
  • 3
  • 18
  • 34
  • Wow, great. It works. Thanks. But how can I add headers? I tried it as described in the Docs with adding ColNameHeader=True but still no headers appear. – clex Aug 21 '18 at 15:13
  • **(1)** Change HDR=YES in your Connection String. **(2)** Where do you want to add headers? Do you dump the Recordset data into a sheet with `Range.CopyFromRecordset` - than this may help: https://stackoverflow.com/a/24283834/7599798 – FunThomas Aug 21 '18 at 15:28