Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2013-06-03
6
Medium Priority
?
5,949 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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 

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 1500 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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
A couple of weeks ago, my client requested me to implement a SSIS package that allows them to download their files from a FTP server and archives them. Microsoft SSIS is the powerful tool which allows us to proceed multiple files at same time even w…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Loops Section Overview

963 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