Load unknown number of files

Question

Hi,

I have a typical scenario,

We have around 18 -19 files, numbers might grow
Column is not fixed , example File A might have more 10 column a day and 12 another day

Solution

Since columns are not fixed but 5 columns will always be there in every file
So, we decided to create a solution like 
we kept  7 columns in table 05 for fixed 6th for all rest column names and 7th for those columns values which means
we generated record ID unique for each row
suppose we have 10 columns , values for first 5 columns will be repeated in 5 rows and rest 5 column will be transposed in 6th & 7th

Problem Statement
   Since, columns sequence is not fixed we are trying 
    if we load first row as data and somehow identify header to identify fixed 5 columns since  these 05 fixed columns could be first 5 ,
    last 5 or in middle 
   Also how can we design a solution to have these dynamic like if number files grow it should accommodate in same solution
   in a particular file column can increase or decrease

lwolfie · Accepted Answer

You could use a macro that inputs each file individually with the interface designer set to Auto Configure by Name or position (depending on if it's always the first 5 columns or specific column names) and Output fields based on the macro configuration.  You can then use the select tool to reorder them on output.

I usually do the name selection and then reorder after that.

OTrieger · Accepted Answer

@VJ_88

One way to do it will be to use the Field Info Tool in a batch macro, union all the possible headers from all files and then use a unique tool that will leave you will all unique headers. Add a RecordID so each header will have a unique number, then transpose using the RecordID as the headers, so Alteryx will not modify any of the names. Then with a Dynamic Renaming tool take the first row and now you have all the headers as is.
Send the headers into a different batch macro that will open each file and union with the headers. The batch macro output will be the aggregation of all the data based on their headers.