Import files with different number of columns

[sandrajansson]

Hi,

I am trying to import multiple files from a directory. There are separate files for each month and I have files from three years back. From this year, the files will change such that there will be two additional columns that won't be present in the old ones.

When I use the Dynamic Input Tool I receive the warning message that 'Warning: Dynamic Input (6): The file "<filename>" has a different number of fields than the 1st file in the set and will be skipped'. Is there a way to prevent these columns from being skipped and instead generate the columns for the old files but simply add NULL as value?

I have specified the latest file, i.e. a file containing the new columns, as Input Data Source Template.

[skeen503979]

Hi there, @sandrajansson.  Here are 3 steps to get your desired output. 

If it were me, I would first consolidate all of the historical monthly data into an Alteryx database file (.yxdb).  This is extremely helpful when dealing with historical data and will dramatically speed up your workflow as well!  Just input each month's file on your canvas then use the Union tool to group them all together.  The output can then be saved as one database file (.yxdb).

Next, if your new data will be saved in separate monthly files moving forward I would suggest having them saved in their own separate folder so the Directory tool can easily import the new data without being confused by other formats.

Lastly use the Union tool with that historical database file and your new current data (which contains some additional columns).  The Union tool creates data vertically as compared to the Join tool creates data horizontally.  The fact that your new data has additional columns will not affect the Union tool's goal of consolidating data with similar column headers.  The tool's default configuration is already to output all fields so you should not have to change anything to get what you want.

[messi007]

@sandrajansson,

For this case you better use a batch macro and then from the Interface Designer Propreties

You have to check Auto config by name (Wait until all iterations Run)

Attached the workflow,

Hope this helps,

Regards

[DylanDowrick]

For a lot of files you may like a macro:

It reads in all files according to the directory, then outputs as a union!

[Humphrey1990]

One thing to add for the above comments on using a Batch macro. I had more success when I changed the action type from "Update Value (Default)" to "Update Input Data Tool".