Solved

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

Posted on 2013-06-03
6
4,525 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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

759 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now