Solved

How to import data form excel with multiple worksheets to a single sql server table?

Posted on 2013-06-03
6
4,765 Views
Last Modified: 2016-02-11
Hi,

I have an excel 2010 with multiple tabs(around 10 tabs) in it with each tab containing the data.

I am using Microsoft SQL server Management Studio 2008 R2.

I would like to import the data from the excel from all tabs into my single table in the database.
This is one time activity.

I could use import wizard and load only one tab or one worksheet in the excel one time.

I am forced to connect again to wizard to load the second tab (worksheet) in the excel.

When I map tow Sources(two worksheets in the same excel) and Destination as same table for both the sheets it is giving
error as " The sam destination table name is used more than once. All destination table names must be unique"

I have many worksheets in a single excel.

So Can you please let me know if the is there any way to load all in one table so that I can avoid every time opening the wizard.


Thanks.
0
Comment
Question by:GouthamAnand
6 Comments
 
LVL 9

Expert Comment

by:mimran18
ID: 39218270
Here is the solution
http://raresql.com/2013/02/22/step-by-step-data-migration-from-excel-2007-and-above-to-sql-server/

In your case you just need to change sheet name each time.

For example :

SELECT * FROM OPENROWSET ('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=E:\test.xlsx;HDR=YES;IMEX=1', 'SELECT * FROM [sheet1$]');


SELECT * FROM OPENROWSET ('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=E:\test.xlsx;HDR=YES;IMEX=1', 'SELECT * FROM [sheet2$]');

SELECT * FROM OPENROWSET ('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=E:\test.xlsx;HDR=YES;IMEX=1', 'SELECT * FROM [sheet3$]');
0
 
LVL 12

Expert Comment

by:Saurabh Bhadauria
ID: 39218283
Well if you can SSIS them below link will be  very useful ...  

http://stackoverflow.com/questions/7411741/how-to-loop-through-excel-files-and-load-them-into-a-database-using-ssis-package


Thanks,
saurabh
0
 
LVL 11

Expert Comment

by:SThaya
ID: 39219075
Hi,

  you can  use SSIS to perform your task

http://www.codeproject.com/Tips/395541/How-to-load-data-from-multiple-Excel-sheets-to-any
http://beyondrelational.com/modules/24/syndicated/398/Posts/18163/ssis-how-to-loop-through-multiple-excel-sheets-and-load-them-into-a-sql-table.aspx


http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/97d02c0b-89d3-4284-abca-f70e37767369

For Excel Connection string :

http://www.connectionstrings.com/excel-2007

For Excel 2007 and above set the below properties

> Connection Manager list please select
"Microsoft Office 12.0 Access  Database Engine OLEDB Provider

and  in  "Enxtended prperties set ."Excel 12.0"
0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 

Author Comment

by:GouthamAnand
ID: 39221079
Hi All,

I do not know how to use and what is SSIS also...So is there any way other than this SSIS?

When I use like below code

Insert into SQLServerTable Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
    'Excel 8.0;Database=D:\testing.xls;HDR=YES',
    'SELECT * FROM [SheetName$]')

am getting The OLE DB provider "Microsoft.Jet.OLEDB.4.) has not been registered..

am not able to open
http://raresql.com/2013/02/22/step-by-step-data-migration-from-excel-2007-and-above-to-sql-server/ 
given by mimran18 in the first answer.

Thanks.
0
 
LVL 9

Accepted Solution

by:
mimran18 earned 500 total points
ID: 39221094
you need to download this utility and install it.

http://www.microsoft.com/en-us/download/details.aspx?id=13255
0
 
LVL 12

Expert Comment

by:Saurabh Bhadauria
ID: 39221438
In this case workaround can be...

Get the all sheet name in a new sheet within that  workbook

Then read that new sheet through  query store the name of all sheets in a table
Then create dynamic queries for each sheet using table values and import the data  


You can get list of all sheets in a new sheet using VBA (macros)..  check below link
 
http://www.ehow.com/how_8503026_list-worksheets-excel-using-vba.html

Thanks,
Saurabh
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Here's a requirements document template for an integration project (also known as Extract-Transform-Load or ETL) based on my development experience as an SQL Server Information Services (SSIS) developer over the years.
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question