Directory Tool and header row

Question

Hi,

I have found solutions to locating the header row in a single .xlsx or .csv file but have been unable to find a post about using the Directory tool for multiple .xlsx files where the header row is not the same. There is summary information in the first x rows and the header row could be on row 11 in one file and row 15 or 16 in another.  It's just 4 fields I'm trying to capture and the header names match across all files.

I thought it would be as simple as "Find the row that contains "Item" and use that row as the header row.

I've tried FindString and StartsWith but am assuming, since I'm not seeing the actual field names in my workflow, I'm getting an "unknown variable" error. These work with a single file using the Input tool.

Could someone please tell me what I might be missing or give me some keywords to search on?

Thanks, new to the site and learning a ton from everyone!

Toni

tviz21 · Accepted Answer

Thank you for providing this, I think it should work but I am getting a truncation error on the Formula tool and cannot figure out why -

If I add a Browse tool to my initial Directory, it will find and show all of the filenames. Once I bring in the Formula tool with the expression - [FullPath]+'|'+'Sheet1$' I get this error:

Formula - Sheet: "C:\Users	hart2\Documents\Alteryx Testing\Book4.xlsx|Sheet1$ was truncated.

This error appears for each file in my directory.

Any ideas as to why this is happening? I haven't been able to find anything on file names truncating.

Thanks again for your help, much appreciated.

Toni

s_pichaipillai · Accepted Answer

what is the length you have in the formula tool for Sheet?

tviz21 · Accepted Answer

Aha, that one was easy - left it at the default, changed it to 200 and all is well with that part, still working through the file you provided.

Thanks again!

Toni