How to get a list of sheet names in an Excel file

Question

There may be more than one sheet in an Excel file. When reading and processing such Excel file, I want to get the sheet names and loop the process as the number of the sheets.

How can I get the sheet names using Get Sheet Name operation?

Answer

The flow of the process is to place Loop by Number of Data operation following the Get Sheet Names operation in the Excel connector.

[Get Sheet Names] -> [Loop by Number of Data] -> [Mapper]

Loop by Number of Data
https://www.hulft.com/help/en-us/HULFTSquare/Content/Designer/Connector/flow_loop_by_number_of_data.htm

Note that the output schema of Get Sheet Name operation is XML type, so it is necessary to set separation path in Loop by Number of Data operation.
The output schema of the Get Sheet Name operation is as follows:

<?xml version="1.0" encoding="UTF-8"?>
　<book>
　 <sheet><![CDATA[Sheet1]]></sheet>
　 <sheet><![CDATA[Sheet2]]></sheet>
　 <sheet><![CDATA[Sheet3]]></sheet>
　   :
　   :
　</book>

Therefore, by setting "/book/sheet" as the separation path in XML data processing tab in the properties of Loop by Number of Data operation, it is possible to loop the reading process as many sheets as there are.
The output schema of the operation is not set automatically and must be set individually.

Please refer to the following page for the schema of the operation.

Get Sheet Names
https://www.hulft.com/help/en-us/HULFTSquare/Content/Designer/Connector/excel_get_sheet_name.htm