Openrowset Command in SQL

Syntax :

OPENROWSET 
( { 'provider_name', { 'datasource';'user_id';'password' 
   | 'provider_string' } 
   , {   [ catalog. ] [ schema. ] object 
       | 'query' 
     } 
   | BULK 'data_file', 
       { FORMATFILE ='format_file_path' [ <bulk_options> ]
       | SINGLE_BLOB | SINGLE_CLOB | SINGLE_NCLOB }
} )<bulk_options> ::=
   [ , CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ] 
   [ , ERRORFILE ='file_name' ]
   [ , FIRSTROW = first_row ] 
   [ , LASTROW = last_row ] 
   [ , MAXERRORS = maximum_errors ] 
   [ , ROWS_PER_BATCH =rows_per_batch ] 

Import CSV File :

SELECT 
    'EmployeeInfo.csv' AS FileName
    , '.csv' AS FileType
    , BulkColumn
FROM OPENROWSET(BULK N'C:\EmployeeInfo.csv', SINGLE_CLOB) AS Document;

Output :

FileName         FileType BulkColumn
---------------- -------- ----------------------------------------------
EmployeeInfo.csv .csv     Eugene Malarky,Grove Street Smithtown,New York

Example Of SQL Database :

SELECT JSDTest.*
FROM OPENROWSET('SQLOLEDB','PC\SQLSERVER2008';'sa';'softweb',
   'SELECT CategoryName, ProductName
   FROM JSDTest.dbo.Product AS Product
        INNER JOIN JSDTest.dbo.Category AS Category ON Category.CategoryID = Product.CategoryID
   ORDER BY CategoryName, ProductName') AS JSDTest

Example of MDB :

SELECT a.*
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
   'c:\MSOffice\Access\Samples\northwind.mdb';'admin';'mypwd', Orders) 
   AS a

Example of Excel :

SELECT *
INTO db1.dbo.table1
FROM OPENROWSET('MSDASQL',
    'Driver={Microsoft Excel Driver (*.xls)};DBQ=c:\book1.xls',
    'SELECT * FROM [sheet1$]')