Import from Excel - Named Sheet

This article is to let you know how to import Excel data into SQL Server.

First of all download a set of components i.e OLE DB support that can be used to facilitate the transfer of data between Microsoft Office System files and non-Microsoft Office applications.

Please click here for the Microsoft download link.

The excel file has only one sheet and it is named as Sheet1 by default. Use the below script to import this data into SQL Server.

USE TestDB1

GO

CREATE TABLE SampleTable(IDCol INT, Col2 VARCHAR(10))

INSERT INTO SampleTable
SELECT * FROM
OPENROWSET('Microsoft.ACE.OLEDB.12.0',
                 'Excel 12.0; Database=D:\TestDataFolder\sample.xlsx',
                 'SELECT * FROM [Sheet1$]')
GO

SELECT * FROM SampleTable

If the excel file's tab has a specific name, i.e. named sheet then -

USE TestDB1
GO

CREATE TABLE SampleTable(IDCol INT, Col2 VARCHAR(10))

INSERT INTO SampleTable
SELECT * FROM
OPENROWSET('Microsoft.ACE.OLEDB.12.0',
                 'Excel 12.0; Database=D:\TestDataFolder\sample.xlsx',
                 'SELECT * FROM [Sample Data$]')
Go

SELECT * FROM SampleTable

Hope you find this article helpful.