How to Import Excel in SQL for doing ETL Operation

Today I got request from client, they want to import Excel file into SQL Server by using procedure, because they want to perform incremental loading on daily basis and ETL on it.

When I goggle out, didn’t find any appropriate blog, videos or any document which are relevant to it. So I thought to write about it.

For import the file we have to create the server which is dedicate to the particular file, or we can use SQL query also for getting the data.

By creating the Server :

Step 1: You have to install OLA DB and have to configure it

Link for installation: https://www.microsoft.com/en-us/download/details.aspx?id=13255

Step 2: You have to configure the dedicated server inside the sql server which will call the file

       Step 2.1: Go to Object Explore

       Step 2.2: Go to sever object

                                         

       Step 2.3: Click on the link server

                     

Step 2.4: Select the new linked server, fill the details

·         In the left pane, select the General page, and then follow these steps:

·         In the first text box, type any name for the linked server.

·         Select the Other data source option.

·         In the Provider list, click Microsoft Jet 4.0 OLE DB Provider.

·         In the Product name box, type Excel for the name of the OLE DB data source.

·         In the Data source box, type the full path and file name of the Excel file.

·         In the Provider string box, type Excel 8.0 for an Excel 2002, Excel 2000, or Excel 97 workbook.

·         Click OK to create the new linked server.

Note : In SQL Server Management Studio, you cannot expand the new linked server name to view the list of objects that the server contains.

For Getting the data : SELECT name,age FROM [OLE DB 17]...[Sheet1$]

Here OLE DB 17 is the server name and Sheet1$ is the name of the excel sheet we want to import

By Using the SQL Query:

Step 1: Then we have to activate the sp_CONFIGURE it is stored procedure in it we have to pass the argument 'show advanced options',1 for activating the advance option to call the ad-hoc distribution query

Step 2: Again we have to call the sp_CONFIGURE and this time we will pass the argument 'Ad Hoc Distributed Queries', 1 for activating the query passing.

Step 3:  Then run the script: SELECT * INTO [dbo].[tmpTable1] FROM OPENROWSET ('Microsoft.ACE.OLEDB.12.0', 'Excel 8.0;Database=C:\Users \test.xlsx;','SELECT * FROM [Sheet1$]')

Then we can use: select * from dbo.tmptable1 to get data

Using procedure and temporary table we can perform various operation, it is very use for BI and Analytics process

Welcome for the feedback and Thanks for viewing.

Kindly suggest more topics

👍👍👍👱👱👱🌝🌝🌝