i have a folder i need to scrape where the report creator doesn't provide the month/year information in a tidy format (it's something i'd be able to pull from a single cell on the report). rather than doctoring their data or forcing them to change their process i'd like to know if i can incorporate the file name or some other file parameters so i am able to import all the reports and then incorporate the formula tool to build out the fields i need.
for example the file name might have the date in the file name (ReportX_202001.xlsx) and i want to pull that into a column similar to append fields as i import the files (not sure if this is a batch macro rather than a dynamic input since dynamic input seems to union all the files immediately)
in the case of the below screenshot of generic sample data if i know how to pull the correct data out one by one (which i guess i can do but i'm trying to learn to leverage the program more effectively. i can get the data out of cell A2 by skipping row 1 and pulling the date out using a substring but when dynamically importing the second file is not using the same row numbers so that doesn't work.
any thoughts are greatly appreciated. i attached a spreadsheet that generates random data in that format that you can maybe use to show me the error of my ways.
thanks in advance
Solved! Go to Solution.
Hi @mompermj,
IF all the files are in the same format (no schema errors) and the month and year are part of the filename this can all be done using a single Input Data tool
Setup the Input Data tool using a wildcard to bring in multiple files at once and set the Output File Names to Field to File Name Only. Each line of data is tagged with the filename from is associated source file.
I added a RegEx to further parse the data into Year and Month
If your files are in different formats (schemas) then there are other options such as batch macros that will allign the data while still using the Input Data tool for the filename.
