如何使用SQL Server读取TXT或者CSV文件

如果你需要在SQL语句中读取外部的TXT或者CSV文件中的内容，那么可以使用以下的语句

SELECT * FROM OPENROWSET('MSDASQL','Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=C:\','SELECT * FROM file.csv')

SELECT * FROM OPENROWSET('MSDASQL','Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=C:\','SELECT * FROM file.txt')

如果出现运行错误，则尝试运行下列语句

sp_configure 'show advanced options', 1

RECONFIGURE

GO

sp_configure 'ad hoc distributed queries', 1

RECONFIGURE

GO

以上代码已经在32位的Win7和Sql Server 2008中测试。

如果你是64位（Bit）系统，那么可能会遇到以下的错误：Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "MSDASQL" for linked server "(null)" reported an error. The provider did not give any information about the error.

Msg 7303, Level 16, State 1, Line 1

Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "(null)".

那么说明你的64位系统中没有相应的驱动“Microsoft Text Driver”，查看路径为：Control Pane => Administrative Tools => Data Sources (ODBC) => Drivers

那么下载微软提供的64位软件：Microsoft Access Database Engine 2010 Redistributable： http://www.microsoft.com/en-us/download/details.aspx?id=13255

然后再将“Microsoft Text Driver”替换为“Microsoft Access Text Driver”即可。