Urgent..........How to import Excel having Multiple Sheet

Question

Post reply

Urgent..........How to import Excel having Multiple Sheet

Anoop Saxena

Mr or Mrs. 500

Points: 547
More actions
December 8, 2008 at 7:15 am

#128335

Hi
I wants to import Excel having Multiple Sheet using Excel Source in SSIS package in to a single table in single execution. how it is possible pls any one help me
Thanks
Anoop

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Alan. T. Hall of Fame Points: 3221 More actions · Answer 1

Use SSIS.

The excel data source allows you to connect to the workbook.

The Excel data source in the data flow task will allow you to specify which worksheet you wish to take your data from.

It work quite well as long as you sheet are well formatted.

Alan

Alan. T. Hall of Fame Points: 3221 More actions · Answer 2

Alan (12/8/2008)
Use SSIS.
The excel data source allows you to connect to the workbook.
The Excel data source in the data flow task will allow you to specify which worksheet you wish to take your data from.

Sorry typed too fast the brain

1. The Excel connection manager allow you to connect to the workbook.

2. The Excel data source in the data flow task will allow you to specify which worksheet you wish to take your data from.

Alan

Lowell SSC Guru Points: 323518 More actions · Answer 3

not sure with SSIS, as there's wizards to help you, but from a pure TSQL point of view,

with multiple sheets, you'll need to add the excel as a linked server.

once it's linked, you can refer to each sheet as a table.

to do it in a single operation, it'd be something like

select * from excelalias...Sheet1$ UNION

select * from excelalias...Sheet2$

etc

here's an example of adding a linked server:

EXEC sp_addlinkedserver AK,

'Jet 4.0',

'Microsoft.Jet.OLEDB.4.0',

'C:\EXCELALIAS_sumfy07.xls',

NULL,

'Excel 5.0;'

GO

--see the availalble tables int he spreadsheet

EXEC sp_tables_ex @table_server = 'EXCELALIAS'

--EXEC sp_dropserver EXCELALIAS

You can then select from the Excel table using SQL code as is needed do an insert into a sql table.

--sheet names end in a dollar sign..so if you name the sheet "Invoice", you access it as "Invoice$"

SELECT * FROM EXCELALIAS...SUMFY07$

SELECT * FROM EXCELALIAS...Sheet1$

SELECT * FROM OPENQUERY(EXCELALIAS, 'SELECT * FROM [Sheet1$]')

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

RR-881870 SSC Enthusiast Points: 137 More actions · Answer 4

Possible via the use of couple of "ForEachLopp" container(Ofcourse one can skin a cat in more then one way).

PreReq: Soon after the package gets create make sure the package level property "DelayValidation" is set to True. This is key to avoid any un-necessary errors being diaplyed due to invalid connections. Please remember most of the connections defined derieve their connectionstrings during execution.

1. define a .Net for oleDB provider connection (under connection manager)

2. The outer loop container reads the individual XLS file (use For EachFile Enumerator)

3. Define a package level variable to read the XLS file name (under variable mappings for the first container).

4. The connection defined in step 1 above, needs to have its "connectionstring" dynamically configured to append the value from variable in step 3 (this variable would hold the name of XLS file being processed currently), Index = 0

4. Now add a 2nd ForEachloop Container inside the 1st one. Use "Foreach ADO.NET Schema Rowset Enumerator", and at the same time point its connection to the one defined in step 1, and also change the schema property to "Tables"

5. Define another variable to save the name(s) of the individual workbooks for the XLS being processed.

6. Under variable mapping for the 2nd ForEach Loop, add new mapping and Assign this variable (Index = 2)

7. Once you know which workbook is under process, you could either write some custom .Net code (script task) or dataFlow control.

I hope its clear. Happy coding 🙂

dulanjan SSC Rookie Points: 45 More actions · Answer 5

would you happen to have any help on writing this script?

im clueless about vb .net

and really need to pass the sheetnames into my dataflow task

Pedro Perfeito SSC Veteran Points: 289 More actions · Answer 6

I made an example for those people that still need:

http://biresort.net/blogs/pedrocgd/archive/2009/08/06/stepbystep-ssis-extract-data-from-multiple-excel-worksheets.aspx

I hope this help.

regards,

Pedro

http://www.pedrocgd.blogspot.com

bhsim Old Hand Points: 325 More actions · Answer 7

Hi Pedro,

You links really helpful, my enviroment is SQL 2008, how should i change the VB.net code to suite VS2008? I get this error message for the CurrentTable define in the code : Value of type '1-dimensional array of String' cannot be converted to 'String'.

Can you show me step by step for SQL 2008 in VS 2008?

Thanks.