Load data from CSV/flat file to SQL Server

 

SQL Server supports multiple methods to load data from CSV/flat file in to a table.

=> Bulk insert
=> BCP
=> Import/Export Wizard
=> Using SSIS Package
=> OpenDatasource
=> OpenRowset

All of these above approaches has own advantages/disadvantages and selecting appropriate approach depends on business requirement.

OpenRowset is easy to implement and more customizable approach. To use OpenRowset, MSDASQL -the OLE DB provider for ODBC should be installed in database server. MSDASQL driver can be downloaded from below link,
http://www.microsoft.com/en-us/download/details.aspx?id=13255

After installation of driver using above link or Ms-Office MSDASQL will be available on providers list  as shown below,

Sample Query:-

SELECT *
FROM OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};
DefaultDir=D:\SSIS\Data;', -- CSV file residing folder localtion
'SELECT TOP 100 * FROM file_name.csv') -- CSV file name

Another approach to read text file,

select * from openrowset(BULK 'd:\<filename>.txt',single_clob)as test

SINGLE_BLOB   - To Fetch Binary data from File (Example :- Image File)

SINGLE_CLOB   - To Fetch Character data from file (Example :- Text File)  

SINGLE_NCLOB  - To Fetch Unicode Character data from file (Example :- Text File)

Error messages received while using OpenRowset:-

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)".

Solution:-

Possible reason for above error could be incorrect syntax or driver not installed. Download and install driver from http://www.microsoft.com/en-us/download/details.aspx?id=13255.

OLE DB provider "msdasql" for linked server "(null)" returned message "[Microsoft][ODBC Text Driver]General Warning Unable to open registry key 'Temporary (volatile) Jet DSN for process 0xeec Thread 0x844 DBC 0x57df6fb4 Text'.".
OLE DB provider "msdasql" for linked server "(null)" returned message "[Microsoft][ODBC Text Driver] Disk or network error.".

Solution:-

Possible reason for above error could be user configured to run SQL Server database service doesn’t have permission on HKEY_LOCAL_MACHINE\SOFTWARE\ODBC registry key. Provide access as show below,

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 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "MSDASQL" for linked server "(null)".

Solution:-

Possible reason for above error could be corrupt file.

OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Text Driver] The Microsoft Jet database engine could not find the object ‘’.  Make sure the object exists and that you spell its name and the path name correctly.".
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider "MSDASQL" for linked server "(null)".

Solution:-

Possible reason for above error could be file not available on specified path.