Batch Macro - Sort Files that don't match order structure of majority of files

[mholland]

Hi,

I'm using the Batch Macro and Directory Input to bring in multiple files at the same time. But I've noticed that some files don't follow the same column structure as the rest.

In my workflow I've used the Formula Tool and Multi Row Formula Tool to look for any rows where the Column Header doesn't match what I'm expecting. Here's an example of the order not matching:

The 3 highlighted rows should match the column headers. The actual columns, Account Group, Reporting Month and Profile, are further to the right in fields with generic headers, like F12, F13 and F14.

I've then filtered the rows that don't match, and pulled out just the file name - I was thinking I could use this for a Pop Up Message to flag to the user that there's an error. Here's how my workflow looks:

But I don't then know how to reorganise this to the correct order, especially if I have more than one file that doesn't match the structure I need. I did see a post that advised creating the file structure then using a Join Tool as below:

But the issue is still that I do

My questions are:

• How can I reorganise the files with the wrong order to match the rest, expecting that there could be multiple files with differing orders
• How can I create a Pop Up that would alert the user to the error

I've attached a copy of the Workflow.

Can anyone help? Or suggest alternatives to this?

Thanks,

Mark

[Qiu]

@mholland 
I would recommend to use this workflow, which will just pull in all the columns, matching or not macthcing.

Then after you can do what you need.

[gabrielvilella]

When sharing a workflow that contains a macro it is better if you export it, this way the macro file will be inserted in the package. Otherwise we are not able to see your macro. 

As for your issue, you shouldn't have to worry about the fields not matching the correct position if you set the macro to auto configure by the column name under the Interface Designer > Properties.

[mholland]

Hi @gabrielvilella ,

I've uploaded a version of the workflow with the macro.

But from the macro settings it's already set up like this but is still allowing other files to pass through.

Could it be because the Macro isn't identifying column headers? My data isn't stored in a table in Excel, it's just an excel worksheet.

[mholland]

Hi @Qiu ,

This workflow does essentially the same as the macro. Getting the data in isn't the issue, it's aligning the different files structures to one structure, finding the ones that don't match and making them match,

Does that make sense?