extracting sheet names from excel sheets with different schemas

[lumjingbki]

Hi I have a excel file with different sheets and with different column names I have created a batch macro to extract the sheet details from the file and I am able to append all the data into a single file but I also want the "Sheet names" so that with the help of different sheet names I can segregate the data once the data is extracted as the sheets have different columns. can you please help. 

[Yoshiro_Fujimori]

Hi @lumjingbki ,

In the Dynamic Input tool in the Batch Macro, when you specify "Input Source Data Template",

you can configure to "Output File Name as Field" as "Full Path", so that the Full path and Sheet name are output.

Then in the main Workflow,

you can parse the string to File name and Sheet name with RegEx such as:

SheetName = REGEX_Replace([FileName], ".*\|{3}(.*)", "$1")

FileName = REGEX_Replace([FileName], ".*\\(.*)\|{3}.*", "$1")

Output

Does this work for your case?

[lumjingbki]

@Yoshiro_Fujimori Thank you for the quick response. am getting "The field "" is missing. Compare the tool configuration with the input stream" error.  In your workflow I notice that there are multiple excel files. In my case I have one excel file and have different sheets with different schemas. I am using batch macros to append the sheet data but I want the sheets names so that I can differentiate the data appended.  

[binuacs · Accepted solution]

@lumjingbki Since you have different schema only batch macro works, attaching the sample workflow for your referene

[someotherguy]

@Yoshiro_Fujimori  Thanks for this, didn't realize full path gave the sheet name as well. Had to modify the regex slightly to remove the ` $` that surrounds the table name

 

REGEX_Replace([FileName], ".*\|{3}(.*)", "$1")         before

REGEX_Replace([FileName], ".*\|{3}`(.*)\$`", "$1")    after