Input file received has data formatted into a different column every month

[Rob48]

I’ve created a workflow that I’m testing now that incorporates new files every month and it seems to be working well except for one hitch – one of the input files I use (an Excel file which I receive from an outside source)  has a different number of columns every month.  I use the “Select” tool to bring in the data I need from this column, but I have to change the parameters in the Configuration Box for each month to capture the right column.  The column is always the last column in the file I receive (it’s a sum of several columns in a row which represent different accounts) but it varies from column #28 to #38 or more in the months I’ve tested.

I’d like to get away from adding in the new input files and then changing the parameters in the “Select” tool every time, so that this can be a no-touch process.  What is the best way, short of altering the file I receive prior to inputting, to capture the correct column every time, regardless of its location in the file?

[Claje]

Hi,

I'm assuming that the column name changes with some frequency?

If there is a rule around the naming convention, (EG it always ends with the word Total), you could use a Dynamic Select tool to make sure this column is always selected.

The Transpose and Cross Tab tools might also get you what you need.

If you have an example spreadsheet, even just with column headers and 1 line of dummy data, that would also help to better understand the challenge.

[Rob48]

Sample File Included at request

[Rob48]

I just added a sample file - note that the first row above the data does not
have an identifying title which is another wrinkle.



Rob48

[Claje · Accepted solution]

Hi,

If I'm understanding the format right, on line 4 of the data you have the actual column header for this, which is "Sum:"?

If yes, the attached example may resolve your need.  Let me know if you have any questions!

[Rob48]

Thanks Claje, this worked (after some downstream adjustments).