How to import a csv file using @ as delimiter with VBA
adodb, csv, excel, vba
Solution
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.
Problem
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?