Importing CSV file into SQL Server


Published 9/26/2007 11:19:14 PM  in  SQLServer

Lets say you have a CSV (comma separated file) and you need to import it into SQL Server. The quickest way to accomplish that is to use the OPENROWSET function.




SELECT *
INTO thetable
FROM
     OPENROWSET('MSDASQL',
        'Driver={Microsoft Text Driver (*.txt; *.csv)};
          DEFAULTDIR=D:\databases;Extensions=CSV;',
        'SELECT * FROM thefile.csv')

You might get an error saying that function isn't enabled in SQL 2005, but all you need to do then is open the SQL Server surface administration and activate it (under "functions").

Now, if you are facing a TAB separated file I use the following trick:

First create a schema.ini file in the same directory as the TAB delimited file

[Facilities.txt] ColNameHeader=True Format=TabDelimited

The use this call:

SELECT * into newtable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Text;Database=d:\databases\nya', 'SELECT * FROM Facilities.txt')

I.e we create a schema file telling the driver to use TabDelimited format for the file Facilities.txt