Import csv with ODBC

User avatar
agibsonsw
SilverLounger
Posts: 2403
Joined: 05 Feb 2010, 22:21
Location: London ENGLAND

Import csv with ODBC

Post by agibsonsw »

Hello. (Excel 2003)
I'm trying to import a csv file using the ODBC text driver. I got this code (snippet shown) from the internet:
Set cn = New ADODB.Connection
cn.Open "Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
"Dbq=" & strFolder & ";" & _
"Extensions=asc,csv,tab,txt;"
I can't open the connection - Microsoft [ODBC Text Driver] (unknown) is not a valid path.

I think it's wrong because the text driver is not an ADO feature, but part of ODBC.
Can someone point me in the right direction? Thanks, Andy.
"I'm here to save your life. But if I'm going to do that, I'll need total uninanonynymity." Me Myself & Irene.

User avatar
HansV
Administrator
Posts: 78370
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Import csv with ODBC

Post by HansV »

Try the OLE DB driver:

cn.Open ConnectionString:="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
strFolder & ";Extended Properties=""text;HDR=Yes;FMT=Delimited"";"
Best wishes,
Hans

User avatar
agibsonsw
SilverLounger
Posts: 2403
Joined: 05 Feb 2010, 22:21
Location: London ENGLAND

Re: Import csv with ODBC

Post by agibsonsw »

Thank you. I've got both versions to work - I had the wrong folder, as I'm using a different computer. Dur!

I apologise; sometimes I struggle for ages, post a question, and then find the answer..

I assume there is no reason, or benefit, to using ODBC for this? Ta, Andy.
"I'm here to save your life. But if I'm going to do that, I'll need total uninanonynymity." Me Myself & Irene.

User avatar
HansV
Administrator
Posts: 78370
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Import csv with ODBC

Post by HansV »

You can use OLE DB or ODBC - whichever works for you...
Best wishes,
Hans