Delimiter for csv and text file using ADO
 

Hello,

What I found so far did not answer my question.
On 2 pc's with "similar" regional setting (we tried to get), a query return
only one column or all
depending if the delimiter is a comma (,) or semi-colon(;)
Any experience with this ?

format of csf file
head1;head2
sssss;gggggg
sdfaf;asdfdfdf
.....

Sub test()
Dim strConnection, conn, rs, strSQL

strConnection = "Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
"Dbq=" & ThisWorkbook.Path &
";Extensions=asc,csv,tab,txt;Persist Security Info=False"
Set conn = CreateObject("ADODB.Connection")
conn.Open strConnection
Set rs = CreateObject("ADODB.recordset")
strSQL = "SELECT * FROM myFile.csv"
rs.Open strSQL, conn

'Debug.Print rs.Fields(0).Name & " " & rs.Fields(1).Name
While Not rs.EOF
Debug.Print rs.Fields("nom").Value
Debug.Print rs.Fields(1).Value
rs.MoveNext
Wend
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing
End Sub

Regards
JY Tfelt

Delimiter for csv and text file using ADO
 

After searching (a long time),
The default delimiter , as specified in the regional settings panel is kept.
A schema.ini file as to be created in the same directory where the text/csv
file is located.
Regards
JY

"Jean-Yves" wrote in message
...
Hello,

What I found so far did not answer my question.
On 2 pc's with "similar" regional setting (we tried to get), a query
return only one column or all
depending if the delimiter is a comma (,) or semi-colon(;)
Any experience with this ?

format of csf file
head1;head2
sssss;gggggg
sdfaf;asdfdfdf
....

Sub test()
Dim strConnection, conn, rs, strSQL

strConnection = "Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
"Dbq=" & ThisWorkbook.Path &
";Extensions=asc,csv,tab,txt;Persist Security Info=False"
Set conn = CreateObject("ADODB.Connection")
conn.Open strConnection
Set rs = CreateObject("ADODB.recordset")
strSQL = "SELECT * FROM myFile.csv"
rs.Open strSQL, conn

'Debug.Print rs.Fields(0).Name & " " & rs.Fields(1).Name
While Not rs.EOF
Debug.Print rs.Fields("nom").Value
Debug.Print rs.Fields(1).Value
rs.MoveNext
Wend
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing
End Sub

Regards
JY Tfelt