Directory Tool and header row

[tviz21]

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

[s_pichaipillai]

here is a sample workflow to read all the excel in a folder but the header row position is not the same

and i assume the column position are in the same order

if it does not work for you please post  sample files , we can try :)

[tviz21 · Accepted solution]

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\thart2\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 solution]

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

[tviz21 · Accepted solution]

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