Load no of sheets in an excel workbook
1. Please define two global variables one to store sheet name and second one to store total no of sheets. We will also have one more local variable for counter.
initialize $L_SHEET_COUNT = 1 in initializing_SCR.
2. Now drag While and add DF in which you have made excel sheet as source. Add two more script one before the DF and one after the DF.
As you can see condition in WHILE $L_SHEET_COUNT <= $G_Total_Sheet where $L_SHEET_COUNT = 1 and $G_Total_Sheet = 4 (because I have 4 sheets)
3. in Sheet_Name_SCR write below code
$G_SHEET_NAME = ‘sheet’||$L_SHEET_COUNT;
print(‘Loading ‘|| ‘Sheet’||$L_SHEET_COUNT);
4. increment $L_SHEET_COUNT = $L_SHEET_COUNT + 1 in increment_SCR.
5. Defining File Format.
Right Click on Excel Workbooks, select New and then create file format. Above screen shows how can you do it.
Make sure you have checked use first row values as column name and choosing worksheet option and passing parameter Global Variable which you have initialized in Sheet_Name_SCR.
Please do like if it helps.
Thanks,
Imran
Thanks, it solves my problem.
-Sultan
Nice example, although it appears your code assumes that each sheet name in the workbook is in the format "sheet1", "sheet2", etc which may not be the case in reality 😉
I have used a similar thing before, but where the number of workbooks in a folder was unknown, and the number of sheets in each workbook could vary. In the end I used a couple of nested loops after persisting a distinct list of all workbook names into a table.
The first loop determined which workbook name to reference (from the table) and the second nested loop iterated though each sheet (referenced by an incrementing number) with a try/catch which would handle when incrementing number was greater than the number of sheets.
The only issue I have with Data Services (or my knowledge!) is not being able to determine each sheet name at run time. Any ideas?
Use VBA to get the number and names of sheets in a workbook. Try Google, you'll find plenty of examples.
You can run the code from a DS script and assign the names to a global variable, or insert into a db table if you prefer.
Thanks for the reply. Never tried that solution, but as I currently have a non-windows DS implementation (Solaris) and have MS workarounds we've had to put in place already (DataDirect drivers, Excel adapter) I'm not sure if this will work. I'll take a look though!
No, I am afraid that won't work. I don't think VBA is possible on Solaris.
Anyway, you've got a (much simpler) working solution already, iterating thru sheet numbers instead of names.
Yes the iterating solution does work for getting the data out of the worksheets, but I would also like to find out the names of the worksheets! Changing OS is not an option 😉
And unfortunately, Excel itself cannot provide a solution either. The sheet() function returns the number if you pass the name, but the opposite (passing a number returning the name) doesn't exist.
Hi Barry, In your case you can use if condition.
If($L_Sheet_Count = 1)
Begin
$G_SHEET_NAME = <You first sheet name>;
End
If($L_Sheet_Count = 2)
Begin
$G_SHEET_NAME = <You second sheet name>;
End
.
.
So on till last sheet.
Mean if you have 5 sheet you then have 5 if.
This is little complicated but it is also a solution.
Thanks,
Imran
In my case the number of sheets was unknown as was the name of the sheets. Hard coding in a number of IF statements would be not be practical IMHO!
Here you can find name of the sheets and no of sheets too.
It sounds like reinventing the wheel.
As simply as active the Number checkbox under the Worksheet access method avoiding the concatenation of 'Sheet' and the Number of the counter. You just need the counter var 😉 The name of the sheet doesn't matters.
The sheet number must be stored as a varchar, for this to accept the variable.
If you pass the value as an int it will complain about not being able to find sheet number 0.