Dynamic input tool for reading multiple sheets with trailing space in name

[honshu]

Hi,

I am trying to use the Dynamic Input tool to read multiple sheets from one Excel file but am getting the error message that he cant find the sheet names even though they are extracted with the input tool.

The error seems to be due to trailing spaces on some of the sheet names.

Does anybody know how to get around this error without changing the original file?

An example of my issue is attached.

Thanks

[suby]

Hello,

Use the Data Cleansing Tool before your Dynamic read.

[suby]

See  attached the Workflow. Hope it helps

[honshu]

thanks for your help.

I tried removing trailing spaces but I still get the same error.

My goal is to read in all 9 sheets of the Excel file. In your attached example it seems only to read in the file once. Am I using it wrongly?

[mceleavey]

Hi @honshu ,

I've attached a couple of tools (you need to save them into your macros folder) and an example workflow.

The first tool reads in all the sheets from all files found in your Directory (set the path to your folder in the directory tool).

The second loads in all sheets from all files.

If you get an error you will need to remove the trailing spaces from your tab names in the Excel documents as this causes a corruption...because Excel is dreadful as a data source.

Friends don't let friends Excel.

Hope this helps,

M.

[Sushrut]

Hello Hello !!

I'm trying to configure the tools that you have shared to import multiple .csv files. However I'm getting error that it cannot find it (Error: Record #1: Tool #8: Unable to open file for read). Could you please advice on how to either configure it to import multiple csv files or if there is any other way to achieve it. Thank you.