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.