Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Dynamic import of multiple Excel files - Column based on file name

mompermj
7 - Meteor

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

 

mompermj_0-1582300462228.png

 

 

2 REPLIES 2
T_Willins
14 - Magnetar
14 - Magnetar

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. 

Input.png

 I added a RegEx to further parse the data into Year and Month

Results.png

 

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.

mompermj
7 - Meteor

i didn't realize you could use the non-dynamic input to import multiple files....mind blown. 

thanks also for teaching me about the file name portion. completely solved the issue. 

Labels