Load Multiple Excel with unequal number of columns

[VJ_88]

HI team,

I am very new to Alteryx  so apology if I missing something

Ask - We have around 145 excel to be loaded via Alteryx and this can increase in future and solution should be dynamic enough to accommodate these 

I tried to used file directory and dynamic input to load but I got stucked as schema of all files need to be exactly same in this approach

Problem

1.   Number of file is not fixed

2. Columns too are not fixed in these files

3. Column order is not fixed 

Solution that I can think if can be achieved in Alteryx, Kindly help

We can have a master file with lets says list of 03 Columns with there order that must be loaded in final table

So if we can check each file maybe in a loop for these 03 columns which can be stored in a master lookup table

Scenario 1 -  If all of these columns are found in File number 1 and in order described in Master fie no action load in final table, Scenerio_1 file data will be written in Final_output file (exxamples attached)

Scenario 2 - If all of these columns are found in File number 2 but not in order I means maybe they are not in Column 01 to 03 we re-arrange the columns in file 2 as per master fie order, Scenario_2  file should be arranged as Scenario_2_Rearranged file the should be as Final_output fie

Scenario 3 - If we have only 02 columns in File 3 we arrange in order for remaining 01 column we add columns in order with blank values, Scenario_3  file should be arranged as Scenario_3_Rearranged file the should be as Final_output fie 

Since we can attached only 05 files I have made sheets instead of files.

Once all above 03 scenarios are handled we load the files with 03 a column and remaining n number of columns as transpose 

Kindly help

[alexnajm]

If you want to read in all files with differing schemas, a batch macro is your best approach: The Ultimate Input Data Flowchart (alteryx.com)

[alexnajm]

Once you read in everything, you can use Alteryx to get the columns that you need from each one!

[VJ_88]

I added samples in excel , Kindly take a look also if you can share an example of batch macro it would be helpful 

[alexnajm · Accepted solution]

If you looked in the article, there is a macro ready to download for you!

[VJ_88]

 thanks a lot solution worked for me only one more help if possible , is there a process for logging I mean if I have 100 files that needs to be loaded and lets assume process batch process failed after loading 74 file completely and in mid of 75th .

Is there a way to log this kind of information

[alexnajm]

You can set up logs to output: Output Log File (alteryx.com)

[VJ_88]

Hi Alexnajm,

output logs are good but not exactly how ETL audit works

Example

If we have 100 files to be loaded

1. File 1 has 100 rows it stores or display 100 of 100 rows loaded
2. File 2 has 250 rows it stores or display 250 of 250 rows loaded
3. ....
4. File 75 had 450 rows it failed mid way and so it should show loaded 225 rows and failed

[alexnajm]

I'm pretty confident you get those kinds of messages in the logs.... have you seen the information in the logs?

[VJ_88]

HI,

Unfortunately no.

Logs for normal Alteryx workflow are what we expect 700 rows read , 700 rows written

but for batch macro its not

I had to take picture hence quality might not be best.

For Batch macro it is like 4 iteration executed and then total records for all files but not individual file audit

Is there a way we have can a row inserted before 1st iteration is executed like 5 rows read then post iteration 5 records inserted ,

Iteration 2 11 records read 11 records inserted and so on