How To Import Data From Excel To SQL Server Database

Ankur Mistry
Mar 25, 2016
4.7k
0
3
Blog

To import data from excel in to SQL Server Database follow the below steps.

Step 1: Right click on Database on which you want to import data, go to Tasks, then click Import Data.

Step 2: It will open SQL Server Import and Export Wizard. After that click on Next.

Step 3: From the next tab Choose a Data Source, here I have selected Microsoft Excel. You can import from many sources like Flat File, Microsoft Access, SQL Server etc.

Step 4: Select the Excel file from which you want to import data and now after selecting Data Source click on Next.

Here I have ticked "First row has column names" that means from my Excel first columns are my field name.

Step 5: Now click on Next, here it's give me error 'Microsoft.ACE.OLEDB.12.0’ provider is not registered on the local machine'

So let's fix the issue first, if you have 'Microsoft Office 12.0 Access Database Engine OLE DB Provider' installed on your system then it will not show the above error.

To fix this issue download (Microsoft Access Database Engine.

Install the patch and check in SQL Server by executing the following command.

EXECUTE MASTER.dbo.xp_enum_oledb_providers

You can check that now the 'Microsoft Office 12.0 Access Database Engine' is installed.

Step 6 : Next step, choose a Destination, here I have selected SQL Server Native client 11.0.

Now, enter your SQL Server Credentials like Server Name, User Name and Password.

Select the Database to which you want to import data, here I have selected 'EMP'. Now click on Next.

Step 7: Select Specify Table Copy or Query, I have selected 'Copy data from one or more tables or views'.

Click Next to execute.

Step 8: Select Source Tables, herI i have selected 'Enquiry From Summary' Table. You can also edit Mappings by clicking on 'Edit Mappings' and it edits your column mappings.