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
