How to import a csv file using @ as delimiter with VBA

Question

I am trying to load data from a csv file in Excel with VBA using ADODB.
I have a function to return a Connection object.

Private Function OpenConnection(dataSource As String) As ADODB.Connection
    Set OpenConnection = CreateObject("ADODB.Connection")

    With OpenConnection
        .ConnectionTimeout = 5
        .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dataSource & ";" & _
            "Extended Properties=""Text;HDR=YES;FMT=Delimited(,)"";Persist Security Info=False"

        Debug.Print "trying to connect: " & .ConnectionString
        .Open
    End With
End Function

And then I just print the data.

Public Sub Test_Import()
    Dim conn As ADODB.connection, records As ADODB.Recordset

    Set connection = OpenConnection(foldername)
    Set records = connection.Execute("Select * from data.txt")

    Debug.Print records.Fields(0)
End Sub

If I use commas it works fine but in the end I will have to use a file that is separated by '@' symbols and which I cannot convert to using ',' because of missing write permissions.
Copying and changing the file somewhere else is unfortunately also not an option.

Now I changed FMT=Delimited(,) to FMT=Delimited(@) in the function OpenConnection and instead of returning the first column value a1, the full line a1@b1@c1 is returned.

Is '@' not supported as a delimiting string? Or did I miss something?

Answer 1

Thanks to @SiddharthRout for the solution and the link to a thread at windows dev center.

The problem was that in the connection string one cannot set the delimiting string but only specify that the file is delimited using a specific character. FMT=Delimited(@) is treated just the same as FMT=Delimited or FMT=Delimited(,).

I had to create a file schema.ini in the folder that contains the csv-file (data.txt) where I had to enter:

[data.txt]
Format = Delimited(@)

The schema.ini file will be parsed and the delimiter is read correctly and everything works (as long as I don't change any file names).

I also found another source at msdn that explains how to set the delimiting string (using either the registry or the mentioned schema.ini) and also includes Tabstops and fixed length separated files.