Dynamic Input with files that start at different rows?

Hi,

In the simplest way, I am trying to consolidate files that I receive each month. I am just simply unioning the data to each other. I need to do this every month so therefore I intend to use the Dynamic Input tool to do this which works fine in theory. However my biggest issue is that each file does not always start from the first row. Sometimes I may have weird additional rows and therefore for each file, I want to input only from the correct line of data (wherever it may be).

I am able to do this one file at a time by using one RecordID to add row numbers for each record, and then again another RecordID tool but this time adding a filter tool afterwards to filter for the row which has my 'header' column, in this case.. it's DATE. then I can use some append and filter tools to filter out anything that sits above my DATE row and then at the end use the dynamic rename tool to push my first record as column headers.

ANYWAY, I have no clue how to do this all together. I've tried converting the step mentioned above into a standard macro but not sure how to slot this between the directory tool and dynamic input tool.

Any help would be appreciated.

Screenshot of macro attached.

MACRO.png

Datasets

Preparation

Expression

Accepted answers

LindonB

Hi there. If I'm following, the challenge is how to dynamically union files together when a variable number of unneeded rows are likely to appear at the start of the data; is that correct? If you can share some sample data, a sample workflow and/or a desired output, we can probably give some more targeted recommendations/provide a solution. My general thoughts are that you should just continue using the Dynamic Input Tool, and then apply a filter with logic that could identify which rows wouldn't be needed. This of coarse assumes that there is a column or columns that wouldn't have usual values or with contain nulls.

All comments

LindonB

TUSHAR050392

I have faced such issues while inputting multiple data files in dynamic input so I would suggest that when you are using dynamic input and defining the template file, you should select that first row contains data. This will help you avoid any issues with files when first row of your data won't be containing column names. After that once all the files have been read through dynamic input, I will do some data cleaning and then rename the columns to their actual names either using select or dynamic rename tool. Let me know if this works for you.

wasahm01

Thank you! I did exactly this. I used dynamic input to union all files and then I just filtered out non-DATE values in the main column and this removed all unwanted rows.

Quick Links

This months top contributors

LanisC 335

hhaigh1 280

erich_shiino 280

FláviaB 262

JORGE4900 235