Skip to main content

How To Import Data From Excel To SQL Table Using Query

This article demonstrates how to import the data from a Microsoft Excel sheet to a SQL Server table in Microsoft SQL Server.

Let’s start step by step. Here I have one Excel sheet with employee details data with six columns as above and this detail I will use as an example and load into employee table.

import data from excel to sql server

 Open SQL Server management studio and run following SQL query.

import data from excel to sql server

DECLARE @Qry NVARCHAR(MAX)
DECLARE @FilePath NVARCHAR(255)='D:\Employee.xlsx'

EXEC sp_configure 'Show Advanced Options', 1;
RECONFIGURE;

EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;

SET @Qry='SELECT * FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'',
''Excel 12.0; HDR=yes; Database='+CONVERT(NVARCHAR(255),@FilePath)+''', [Sheet1$]);'

INSERT INTO Employees
EXEC (@Qry)

select * from Employees

You have to make sure "Microsoft Access Database Engine Redistributable" is install or not in your local machine. If it is not install in your machine above query will not work.

Download Microsoft Access Database Engine Redistributable from here

 


Labels:

Comments

Post a Comment

Popular posts from this blog

Angular 14 CRUD Operation with Web API .Net 6.0

How to Perform CRUD Operation Using Angular 14 In this article, we will learn the angular crud (create, read, update, delete) tutorial with ASP.NET Core 6 web API. We will use the SQL Server database and responsive user interface for our Web app, we will use the Bootstrap 5. Let's start step by step. Step 1 - Create Database and Web API First we need to create Employee database in SQL Server and web API to communicate with database. so you can use my previous article CRUD operations in web API using net 6.0 to create web API step by step. As you can see, after creating all the required API and database, our API creation part is completed. Now we have to do the angular part like installing angular CLI, creating angular 14 project, command for building and running angular application...etc. Step 2 - Install Angular CLI Now we have to install angular CLI into our system. If you have already installed angular CLI into your system then skip this step.  To install angular CLI ope...
Post a Comment
Read more

Angular 14 : 404 error during refresh page after deployment

In this article, We will learn how to solve 404 file or directory not found angular error in production.  Refresh browser angular 404 file or directory not found error You have built an Angular app and created a production build with ng build --prod You deploy it to a production server. Everything works fine until you refresh the page. The app throws The requested URL was not found on this server message (Status code 404 not found). It appears that angular routing not working on the production server when you refresh the page. The error appears on the following scenarios When you type the URL directly in the address bar. When you refresh the page The error appears on all the pages except the root page.   Reason for the requested URL was not found on this server error In a Multi-page web application, every time the application needs to display a page it has to send a request to the web server. You can do that by either typing the URL in the address bar, clicking on the Me...
Post a Comment
Read more

Send an Email via SMTP with MailKit Using .NET 6

How to Send an Email in .NET Core This tutorial show you how to send an email in .NET 6.0 using the MailKit email client library. Install MailKit via NuGet Visual Studio Package Manager Console: Install-Package MailKit How to Send an HTML Email in .NET 6.0 This code sends a simple HTML email using the Gmail SMTP service. There are instructions further below on how to use a few other popular SMTP providers - Gmail, Hotmail, Office 365. // create email message var email = new MimeMessage(); email.From.Add(MailboxAddress.Parse("from_address@example.com")); email.To.Add(MailboxAddress.Parse("to_address@example.com")); email.Subject = "Email Subject"; email.Body = new TextPart(TextFormat.Html) { Text = "<h1>Test HTML Message Body</h1>" }; // send email using var smtp = new SmtpClient(); smtp.Connect("smtp.gmail.com", 587, SecureSocketOptions.StartTls); smtp.Authenticate("[Username]", "[Password]"); smtp.Se...
Post a Comment
Read more