Skip to Content
Author's profile photo Mohd Imran

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.

/wp-content/uploads/2015/10/variables_815268.png

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.

/wp-content/uploads/2015/10/while_815269.jpg

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.

/wp-content/uploads/2015/10/file_format_815280.jpg

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

Assigned Tags

      12 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      Thanks, it solves my problem.

      -Sultan

      Author's profile photo Former Member
      Former Member

      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?

      Author's profile photo Dirk Venken
      Dirk Venken

      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.

      Author's profile photo Former Member
      Former Member

      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!

      Author's profile photo Dirk Venken
      Dirk Venken

      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.

      Author's profile photo Former Member
      Former Member

      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 😉

      Author's profile photo Dirk Venken
      Dirk Venken

      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.

      Author's profile photo Mohd Imran
      Mohd Imran
      Blog Post Author

      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

      Author's profile photo Former Member
      Former Member

      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!

      Author's profile photo Mohd Imran
      Mohd Imran
      Blog Post Author

      Here you can find name of the sheets and no of sheets too.

      /wp-content/uploads/2015/10/sheet_816050.jpg

      Author's profile photo Former Member
      Former Member

      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.

      /wp-content/uploads/2015/10/2015_10_22_15h36_07_815685.png

      Author's profile photo Joshua Blythe
      Joshua Blythe

      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.