Accounting for different number of columns

[jamesgough]

Hi,

I am looking to automate processing data from PDFs. Unfortunately, whilst the layout is similar, there are some differences, most of which I've accounted for in the workflow. However, one layout has stumped me.

If you see the attached excel file, Sheet A is the normal format which works with my workflow. Sheet B is the new layout. You see it contains no column "Proposal Number", which is central to a lot of my preparation downstream. I have considered applying Esther's incredibly helpful solution here at the start of my workflow to have all the names in place to begin with https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Help-identifying-correct-data-fields/m...

However, "Meeting Date" differs from being in Proposal Text and Number so I don't think it will work. 

Also, I would like to be able to batch these documents even if the format differs and so I don't want to have a unique workflow for Sheet B.

I think what I need is a way of being able to verify if Proposal Number exists and if it does "do A" and if it doesn't then "do B". I was thinking the detour function but not sure that is right.

Thank you in advance, I really appreciate your help. 

James.

[DavidThorpe · Accepted solution]

Hi @jamesgough 

There is a macro on the public gallery which will append (union) multiple sheets in a file, by their field position - however this is not ideal for your solution, as it will align the 'Number' field in Sheet A with the 'Proposal Text' field in Sheet B.

What I have done is built on this slightly, to dynamically create the headers and instead union by name.

This macro contains the below steps:

- Ascertains whether field F1 contains the text 'Number', or 'Proposal Text' - the two formats for column 1 in Sheet A and Sheet B respectively.

- Renames the columns accordingly, appending a filename field so you can track where the information has come from.

- The dynamic rename will rename the fields using the new headers (drawn from either Sheet A or B).

- The nature of the batch macro will union your outputs and append them by field name, rather than position.

You can then run a filter on the 'FileName' field to test whether it is source Sheet A or B, and build downstream workflows to parse the data respectively.

This should hopefully work for you!

Thanks

David 

[jamesgough]

Thanks a lot for the reply David.

I ended up using a summarize tool to ascertain the names of the columns and then a filter to split the data accordingly. Then I used a select tool to both filter paths to ensure the names were the same and identically positioned before unioning them back together based on field name. So a similar thought process I think. The batch macro is great and solves the problem of misaligned columns due to varying positions. Thanks!

[DavidThorpe]

Very similar process, glad you've resolved it!