Thursday, September 10, 2020

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.

No comments:

Post a Comment

Big Data & SQL

Hi Everybody, Please do visit my new blog that has much more information about Big Data and SQL. The site covers big data and almost all the...