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

To report this post you need to login first.

12 Comments

You must be Logged on to comment or reply to a post.

  1. Barry Walters

    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?

    (0) 
    1. 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.

      (0) 
      1. Barry Walters

        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!

        (0) 
        1. 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.

          (0) 
          1. Barry Walters

            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 😉

            (0) 
            1. 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.

              (0) 
    2. Mohd Imran 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

      (0) 
      1. Barry Walters

        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!

        (0) 
  2. Néstor Martínez

    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

    (0) 
    1. 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.

      (0) 

Leave a Reply