extracting sheet names from excel sheets with different schemas

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.

Developer

Accepted answers

binu_acs

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

Combine_Sheets_080523.yxzp

All comments

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?

MultiExcelBooks_Sheets.zip

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.

binu_acs

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

Combine_Sheets_080523.yxzp

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

2023-11-02 18_00_02-Alteryx Designer x64 - SAP - VK11 - KMAT pricing tables 2023-10-24.yxmd.png

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

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

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703