OPENROWSET

SQL Server

OPENROWSET(‘provider_name’, ‘datasource’, { ‘username’ | ‘password’ } | ‘provider_string’, { [ catalog. ] schema. } object)

• ‘provider_name’: The name of the OLE DB provider that is used to access the remote data source.
• ‘datasource’: A string that identifies the data source. This could be a file name, URL, or other data specific to the data provider.
• ‘username’ | ‘password’: The credentials required to access the data source. Some providers may not require these parameters or may use different terms for them.
• ‘provider_string’: An optional parameter that can be used to provide additional provider-specific connection settings.
• [ catalog. ] schema. object: These parameters represent the three-part name of the object that you want to access in the data source. The catalog is the name of the database, the schema is the name of the database schema, and the object is the name of the database object, such as a table or view.

Example

SELECT a.*
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
                'Excel 8.0;Database=C:\temp\YourExcelFile.xls;',
                'SELECT * FROM [Sheet1$]')
      AS a;

Output

Column1 | Column2 | Column3
--------|---------|--------
Data1   | Data2   | Data3
Data4   | Data5   | Data6

Explanation

The OPENROWSET function is used to retrieve data from a Microsoft Excel spreadsheet. In this case, it is accessing the Excel spreadsheet located at C:\temp\YourExcelFile.xls and selecting all records from Sheet1. The output shows a portion of the retrieved data.