Solved

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

Posted on 2013-06-03
6
4,842 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
kill process lock Sql server 9 63
SQL Server 2012 r2 - Varible Table 3 31
SSIS 2008  Datetime Converstion to Excel 3 20
Stored Proc - Rewrite 42 56
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?
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

840 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