Blog Detail

SQL SERVER: How to Read Excel file by TSQL

Many times developers asked that, they want to import data from Excel file. We can do this by many ways with SQL SERVER.
1. We can use SSIS package
2. Import/Export Wizard
3. T-SQL
Today, I am going to explain, How to import data from Excel file by TSQL.
To import Excel file by TSQL, we need to do following:
1. Put Excel file on server, means we need to put files on server, if we are accessing it from local.
2. Write following TSQL, to read data from excel file
SELECT Name, Email, Phone
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\SQLYoga.xls', [SQL$])

ExcelFile

NOTE: Here, Excel file is on "C:\" named "SQLYoga.xls", and I am reading sheet "SQL" from this excel file
If you want to insert excel data into table,
INSERT INTO [Info]
SELECT Name, Email, Phone 
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\test\SQLYoga.xls', [SQL$])
That's it.

About me

User

Tejas Shah

Microsoft Certified Professional Expert. I have 14+ years of experience in Design and Develop 100+ .Net applications using Asp.Net, C#, Asp.Net Core, VB.Net, SQL Server, MVC, AngularJS, JavaScript, Azure, HTML5 and CSS3.

Recent Post