Import files using Folder data source, where individual files may have different column counts and .

[Chris_W17]

Good morning community,

I am trying to import files using Folder Data source. Tha files are CSV. The files are outputted daily from an online reporting portal.  The report will only populate a column if data exist. If no data exist on for that day, the column for that data is excluded in that file.  So, one file may have 12 named columns and another could have 10 named columns.  From day to day it cannot be determined which colums will show up, as a result the column order and count changes.  This process needs to be automated.  Is it possible to create a persistant table with all possible columns, and have each file, as it is loaded, dynamically map to the correct column? If so, how? Or, does anyone have a better solution?  Thanks.

[Greg_Deckler · Accepted solution]

@Chris_W17 Create a calculated column for each of your 12 possible columns. You could use this format:

try [Column] otherwise null

After this is done, select your calculated columns and Remove other columns. Then rename your columns.