Solved

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

Posted on 2013-06-03
6
4,948 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

My client sends data in an Excel file to me to load them into Staging database. The file contains many sheets that they have same structure. In this article, I would like to share the simple way to load data of multiple sheets by using SSIS.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

740 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