Reading multiple files from multiple locations

[N783970]

I need to make a workflow which reads an excel file. Within the excel file, column A contain a list of directories. Column B contains the file names. Now The regular way would be to create multiple directory inputs to read all the files. Now is there a work around to use only one directory file using a macro to read these multiple files and open them?

[VijayAnaparthi]

@N783970 , All the he files in column B are in similar structure. I mean all files are having same columns or different columns. 

If it is different then we need to create a macro and that user needs to select file name in that macro. So that each file will read separately.

[N783970]

All the files have different structure. Basically i need to know when the file was last accessed. I Need help in creating this Macro. Can you please help me with an example.

[VijayAnaparthi]

Can you explain the problem clearly. Do you want when was the last time each file is opened or modified by someone right. That is what you want at the end modification time of each file.

But you need to create macro so that you can select file name and directory name depending on your choice as an input so that you will get last access or modified time of that file as an output.

[EWarmestad]

I think I understood your problem, you want to search specific folder, and if the you list is in that folder you want to see when it was last accessed. I have made macro that should solve your problem, just swap the text input for the correct input type. Let me know if it did the job! 

[N783970]

Firstly Thank You for the reply.

Is this the Config file in the workflow?

<Configuration>
<Value name="BatchMacroGroupBy"><![CDATA[Control=Directory
]]></Value>
<Value name="Input.Directory and filname"><![CDATA[Directory=Directory
Filname=Filname
]]></Value>
<Value name="ControlParams"><![CDATA[Control Parameter (7)=Directory
]]></Value>
</Configuration>

ALso in my Alteryx Designer 2021, the tool is shown as a question mark

[EWarmestad]

Yes that seems to be correct, it have name the column the same as if you did just a normal dir, but made i so it reads all of the dir you put into and check if the file in the same column is in there. 

It just hit me that you are going to search all the folders multiple times with the setup I gave you. I have added a select and Unique so it will only search each folder once. 

you also might want to see the directory and filname GroupBy field to Directory

[N783970]

The workflow is not opening correctly. Can you please help with which tool is used in place of the question mark shown in the attached image.

[EWarmestad]

 I am using the 2022.3.395 VS of Alteryx and here it look like this: 

The question mark is the batch macro that does the job. 

I guess you should upgrade, that will probably solve the problem. 

[BS_THE_ANALYST]

@N783970 You may have different files, which are different file types, with different schemas. I'm pretty sure this workflow will address all of those concerns, should they arise. 

Connecting directly to your Excel document (which is acting as a directory tool). Then bringing them onto the workflow through a batch macro. See below, the schema can change. I brought csvs and also .xslx files in. Make sure when you open the workflow to configure the two highlighted bits below to match mine.

It would be beneficial for you to complete this weekly challenge and also to learn from the particpant's solutions:
https://community.alteryx.com/t5/Weekly-Challenge/Challenge-180-Thousand-File-Challenge/td-p/461356 

Lastly, just take make sure you know how to configure the workflow:

point these File Inputs to the excel file and sheets that you currently have acting as a directory tool. 

FYI, this is how I formatted that excel file: