Retain sheet name when using a batch macro input

[Kearnd967]

I want to use a batch macro to bring in multiple sheets from the same excel file.  They are all structured the same but have different sheet names.  I would like to retain the sheet name in the joined data.

I am really struggling with this one, any help would be greatly appreciated.

David.

[DataNath]

Hey @Kearnd967, in the Input Data tool that sits inside your macro, if you go to the 5th option (Output File Name as Field) and select 'Full Path' - this will output the entire filepath into the data when the macro has finished running, including the sheet name at the end. From here, you can either keep it as is or parse it out, depending on the format you want.

For parsing the sheet name, you can just stick a RegEx tool on in Parse mode, select the [FileName] field and use something like the following as the expression:

`([^$]+)

Hope this helps!

[Kearnd967]

I think I have done that already.  See attached.  Not sure if its because I am trying to import from a specific row?

[DataNath]

@Kearnd967 the image you attached shows the configuration of your Action tool. I mean the configuration of your actual Input Data tool - if you just change that 5th option to output the Full Path then you should see that come through into the data when you run the macro.

[Kearnd967]

Sorry, I dragged in the wrong pic.  This is the input tool and action tool side by side.  This shows Option 5 already selected. Perhaps I am doing something wrong on the main workflow 

[OllieClarke]

@Kearnd967 

Have you set option 5 as @DataNath said?

Also if I may amend @DataNath's RegEx, I would go with:

.+\|`?(.+)

Hope that helps,

Ollie

[OllieClarke]

@Kearnd967 if you run the macro itself (not in the wrapper workflow), does it output the filepath?

[Kearnd967]

Yes it does.  It works fine in the macros.  But when I add the macros to the workflow, it names everything with the original sheet name from the macro.

[DataNath]

Hey @Kearnd967 I notice the name of your macro is still starred i.e. unsaved. Since ticking Option 5, have you re-saved it? That option is all you need to see the full FilePath so should be working otherwise.

[OllieClarke]

@Kearnd967 can you show the workflow and the data going into the batch macro please?