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.

Excel_Sheet1

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

sheet1


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

sheetname

Hope you find this article helpful.

One comment

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 )

Connecting to %s