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

Question

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

dynamic input with trailing spaces.yxzp

Sushrut · Answer

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.

mceleavey · Answer

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.

Multi Sheet Import Example.yxmd

Dynamic Multi File Sheet Names.yxmc

Import Multiple Schema Flat Files.yxmc

honshu · Answer

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?