Weekly Data Consolidation and Cumulative Tracking

remarsha

Hi,

Every week, Have 15 odd files(all .xlsx) that needs to be uploaded to a cumulative tracker capturing the date of upload and week no.

- Since the file formats/columns of the files are different, i Select the columns accordingly.
- Once the columns are chosen, i use Union tool to have a consolidated view.
- i add two more cols (hard code the date for which the files are for + add the week no

- The above i repeat with three different sets of data. So technically 3 cumulative trackers to update every week.

Ask:

Is it possible to upload all the files (of a set) in one go rather than selecting one file after another? 

Is it possible to append the date to the data automatically? say an interface tool to put the date and that being captured in the data?

Is it possible to append the weekly consolidated records to the cumulative tracker automatically?

If Yes, please guide. 

Thanks.

rzdodson

Is it possible to upload all the files (of a set) in one go rather than selecting one file after another?
@remarsha it absolutely is possible. You can accomplish this through a Directory tool and then using a batch macro to pass those files in one at a time. If going this route, the batch macro will be able to handle the unioning of the data with respect to the column headers; then, you'll just deselect which fields are not important to your analysis. A second approach is using a Dynamic Input tool. Both routes can accomplish this task.

Is it possible to append the date to the data automatically? say an interface tool to put the date and that being captured in the data?

This is possible as well. You'll have a Calendar tool updating a Text tool, and have it set to update a specific row and column in that Text tool. From there, you can use an Append tool to bring in that date to your data set.

Is it possible to append the weekly consolidated records to the cumulative tracker automatically?

Yes, this can be accomplished as well. A lot of different routes to go here. In your use case, I would recommend that we create the 15-to-1 consolidation first, then union it to an existing data set. That way if there are any issues within a weekly consolidation activity, we can "error trap" a week's worth of data instead of having to do a full scrub. But, should you want to consolidate it in to one file, the Output tool has the "Append to Existing Sheet" which will enable you to write directly to the consolidated data set each week.