Excel iteration where data tab is also file name

1

Hope all is well.

I am trying to create a project that ingests multiple excel workbooks, grab a single tab, and export that tab into a new .CSV file.

Easymorph can grab a tab if it is the same name in each workbook, but in this instance the tab i want to grab is also the file name ( file.name.xlsx , excluding the .XSLX). I have a parameter with a file called ‘File_Name’ and i tried to pass this parameter to a new parameter called ‘Tab_Name’ = keepbefore(File_name, ‘.xlsx’). I have tried putting {} and [] brackets around the variable ‘File_Name’ with no success. Please help.

2

Welcome to the Community @Harvey_Charmichael,

When you say 'no success' does it mean that you see any error messages? What does your screen look like when you're trying to do this?

My understanding is that you're creating a calculated parameter Tab_Name that is calculated as keepbefore(File_name, ‘.xlsx’). Then you would want to use this parameter instead of a sheet name in the "Import Excel spreadsheet transformation". Here are a few things to check:

Check #1
Note that a calculated parameter is a special parameter type. Check that you have explicitly indicated in the Parameter Manager that this is a calculated parameter (see screenshot below).

image

Check #2
Check that in the expression the parameter name is wrapped in curly braces, i.e. {File_Name}

Check #3
In the "Import from Excel" transformation the sheet name is specified by parameter Tab_Name (see screenshot below).
image

See also the tutorial chapter on parameters (if you haven't done it yet): Tutorial:
Project parameters.

3

Thank you @dgudkov ,

It looks like I was not making Tab_Name a calculated parameter.

I have applied the solutions you recommended and I received a new error which says Worksheet or named range ‘…\full file path name’ not found. I think i now need to remove anything in front of the file name in the file path to just keep the file name. The screen shows a yellow arrow inside the import excel workbook in the table,

I remember seeing an example do this in one of the tutorials and will look into this, any recommendations would be very helpful.

5

Apparently, the calculated sheet name is incorrect as it includes full path. You can try using the filename() function, for instance:

keepbefore(filename([File_name], ‘.xlsx’)
1 Like