FAQ: Why cannot 64-bit MSDASQL access a .csv text file?
Summary
I have installed 64-bit MSDASQL on my Windows Server 2003, https://www.microsoft.com/downloads/details.aspx?FamilyID=000364db-5e8b-44a8-b9be-ca44d18b059b&displaylang=en, and configured a linked server on my 64-bit SQL Server 2008 instance to access a local .csv file, however the following query does not work:
select * from OpenRowset('MSDASQL',
'Driver={Microsoft Text Driver (*.txt; *.csv)}; DefaultDir=D:\Test;Extended properties=''Format=Delimited(,);''','select * from test.csv')
Symptom
The error message is as follows:
OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "(null)".
Resolution
The cause of this issue is that there is no 64-bit ODBC text driver installed on your 64-bit Windows server 2003 actually. The 64-bit MSDASQL just provides an OLEDB/ODBC 'bridge' that allows applications built on OLEDB and ADO (which uses OLEDB internally) to access data sources through ODBC drivers.
To resolve this issue, you need to first install a 64-bit ODBC text driver. Previously this is not possible, but now it has been included in "Microsoft Access Database Engine 2010 Redistributable", https://www.microsoft.com/downloads/details.aspx?familyid=C06B8369-60DD-4B64-A44B-84B371EDE16D&displaylang=en.
After you install this driver, you should see "Microsoft Access Text Driver (*.txt,*.csv)" with ACEODBC.DLL in your 64-bit ODBC Data Source Administrator. Then you can run your query again.
Comments
Anonymous
November 16, 2011
The comment has been removedAnonymous
May 20, 2012
Is the same solution applicable for SQL 2008 which is running with Windows 2008 ? Thanks in Advance...Anonymous
October 03, 2012
I had the same problem, but when I compared names of ODBC drive for text, I realised that it's name is {Microsoft Access Text Driver (*.txt, .csv)} and not {Microsoft Text Driver (.txt; .csv)} When I changed it, my sql command worked: select * from OpenRowset('MSDASQL', 'Driver={Microsoft Access Text Driver (.txt, *.csv)}; DefaultDir=c:temptest','select * from file.csv')Anonymous
January 15, 2013
can someboday help me to link my myob databse into SQL server 2008 64bit, suresh@abc.lkAnonymous
April 19, 2013
Thanks Petrpan, that solved it for me"Anonymous
May 08, 2014
The comment has been removed