Import external data into SQL server: from excel file

Three programtical ways to load data into SQL server:
  • OPENROWSET: It is an alternative to accessing tables in a linked server and is a one-time, ad hoc method of connecting and accessing remote data using OLE DB. The OPENROWSET function can be referenced in the FROM clause of a query as though it is a table name. The OPENROWSET function can also be referenced as the target table of an INSERT, UPDATE, or DELETE statement, subject to the capabilities of the OLE DB provider. Although the query may return multiple result sets, OPENROWSET returns only the first one. it does not accept variables for its arguments
  • OPENDATASOURCE:The OPENDATASOURCE function can be used in the same Transact-SQL syntax locations as a linked server name. Thus, OPENDATASOURCE can be used as the first part of a four-part name that refers to a table or view name in a SELECT, INSERT, UPDATE, or DELETE statement, or to a remote stored procedure in an EXECUTE statement. When executing remote stored procedures, OPENDATASOURCE should refer to another SQL Server. OPENDATASOURCE does not accept variables for its arguments
  • Linked server: Once defined, it can be used in repeated distributed queries, or execute stored procedure in the linked server. 
The following gives examples for common external data source such as MS excel files, MS access, text file, SQL server etc.
 
1. Excel file
1.1 Use OPENROWSET
SELECT * FROM OPENROWSET(
‘Microsoft.Jet.OLEDB.4.0’,
‘Excel 8.0;DATABASE=C:\ExcelTest.xls’,
    ‘Select * from [sheet1$]’)
Since OPENROWSET does not support variable in parameters, if the file name is dynamically determined in run time, use dynamic query like this:
DECLARE @FileName nvarchar(255)
SET @FileName=N’C:\ExcelTest.xls’
DECLARE @SQL nvarchar(2000)
SET @SQL=’SELECT * FROM OPENROWSET(
”Microsoft.Jet.OLEDB.4.0”,
”Excel 8.0;DATABASE=’+@FileName+”’,
    ”Select * from [sheet1$]”)’
PRINT @SQL
EXEC(@SQL)
 
 
1.2 Use linked server:
EXEC sp_addlinkedserver ‘ExcelSource’,
   ‘Jet 4.0’,
   ‘Microsoft.Jet.OLEDB.4.0’,
   ‘C:\ExcelTest.xls’,
   NULL,
   ‘Excel 8.0’
GO
SELECT * FROM ExcelSource…[sheet1$]
SELECT * FROM OPENQUERY(ExcelSource, ‘SELECT * FROM [sheet1$]’)
 
sp_addlinkedserver supports variables in the parameter datasource, e.g.:
DECLARE @FileName nvarchar(255)
SET @FileName=’C:\ExcelTest.xls’
EXEC sp_addlinkedserver ‘ExcelTest’,
   ‘Jet 4.0’,
   ‘Microsoft.Jet.OLEDB.4.0’,
   @FileName,
   NULL,
   ‘Excel 8.0’
GO
SELECT * FROM ExcelTest…[sheet1$]
 
 
1.3 Use OPENDATASOURCE:
SELECT * FROM OpenDataSource( ‘Microsoft.Jet.OLEDB.4.0’,
  ‘Data Source="C:\ExcelTest.xls";
    User ID=Admin;Password=;Extended properties=Excel 8.0′)…[sheet1$]
 

For dynamic file names:

DECLARE @FileName nvarchar(255)
DECLARE @SQL nvarchar(2000)
SET @FileName=’C:\ExcelTest.xls’
SET @SQL=N’
SELECT * FROM OpenDataSource( ”Microsoft.Jet.OLEDB.4.0”,
  ”Data Source="‘+@FileName+’";
    User ID=Admin;Password=;Extended properties=Excel 8.0”)…[sheet1$]’
PRINT @SQL
EXEC(@SQL)

 

Advertisements
This entry was posted in SQL server. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s