Using an Excel File as a data source, have set up ODBC and created a data set, but Excel has multiple tabs.
-
Hello,
I have set up a data source in VS 2010 using an Excel (2010) Document, through an ODBC that I created. Now, when I set up the data set, I put in...
Select * from [tabname1$]
...I did this 3 times, so I have a data set for each tab.
And this works. But, what I'd like is to create a data set that consists of all the tabs in one. Why? Because I may need to use a mix of datasets in one table or graph. All the tabs are different so I do not think I can use vlookup.
The problem is if I try to add another tab like this...
select* from [tabname1$], [tabname2$],[tabname3$]
...it does not work. TBH, I did not think it would. Is it possible to combine all the tabs in to one data set? If yes, what is the syntax I must use?
Thank you.
kind regards,
D. -
I'm sure others more familiar with SSIS and Excel could help more.
But for an alternative, I normally use Python for such tasks because it allows you to load up Excel in a module called Pandas. You can then loop through the data easier. Being Pandas will auto-detect the data structure, it makes it a bit easier to dynamically load data sets. Then SSIS can be setup to execute the script.
The issue of course is that while it can dynamically load this, it's doing it based on assumptions. Those assumptions of the data types could be wrong. It could also be more than what you are asking for. This is why most will agree that you should design for something more static than trying to create a catch-all that may cause more trouble than what it's worth. If you can lock down a more static flow, then you wouldn't need to use Python or need to find a way to catch-all with SSIS. You can just define a flow per tab and be done with it.
-
I think the simplest thing is to select from each sheet into a temp table or one temp table for each sheet and do a union query
😎 -
You'll need to set up a Source for each tab in your Excel document. In each of those, "Select", the data from the relevant tab.
Depending on what your aim is, you can then use a Union All, or Merge Join Transformation. (Note that if you are using Merge Join, you need to ensure your data is sorted).
For example, the attached image shows one of my own tasks that uses a file with 6 tabs (names removed for anonymity). The top sources are all identical in columns and data types, so all feed straight into a Union All. The Data to Date is a tab which operates as a vlookup, which you can see if Merge Joined. The Data To Date is always sorted in the data, so the data is marked as sorted in that Source, however, I have to sort the data from the Union.
If you have some more specific questions, please ask.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply