Importing CSV / Excel Files

-- CSV

EXEC sp_configure

GO

-- [Ad Hoc Distributed Queries] run_value should be 1

SELECT * FROM OPENROWSET('MSDASQL',

'Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=F:\data\export\csv\;',

'SELECT * FROM Top10.csv')

GO

            

--Using a different provider

SELECT *

FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Text;Database=F:\data\export\csv\;HDR=YES',

'SELECT * FROM Top10.csv')

GO

-- EXCEL:

SELECT *

INTO #SpreadSheet1

FROM

OPENROWSET('Microsoft.Jet.OLEDB.4.0'

,'Excel 8.0;Database=D:\data\excel\generalledger.xls','SELECT * FROM [Sheet1$]')

GO

             

SELECT *

INTO #SpreadSheet2

FROM OPENROWSET('MSDASQL',

    'Driver={Microsoft Excel Driver (*.xls)};DBQ=D:\data\excel\generalledger.xls',

    'SELECT * FROM [Sheet1$]')

GO 

SELECT * FROM #SpreadSheet1

GO

SELECT * FROM #SpreadSheet2

GO