Select File(s) from a Directory for Processing

H there,

I want be able to select a file or a number files from a given directory and for each selection copy the data to a specific tab in a consolidated Excel macro-enabled workbook. To help with this the the source files start with B[NNN] so B001 - Daily TB would go the tab B001 in the consolidated workbook.

I have gotten as I can with the attached screen shot (the data is sensitive so can't post the workflow) but it shows all files in the directory where I only want ones that start with B[NNN] and keeps asking for the directory.

Any help to get this workflow working would be appreciated.

Regards,

Robert

Alteryx Question - How to Select Files for Processing.JPG

Datasets

Accepted answers

caltang

The first workflow:

Is to serve as the first or genesis app for end users to find the folder they want. Then you can add conditions to the filter etc like your starts with EE etc., then you can use a summarize tool to generate the NAME && VALUE (rename the columns selected). So NAME basically means what you show as a selection later, and VALUE is basically what is the value underneath the selection. They can be the same or different depending on how you design it. In my example, I used FileName as NAME and FullPath as VALUE, because the filename is easier to read, and the fullpath is what we need later anyway when we use a Batch Macro or Dynamic input to call the data to our workflow.

So for me, I know the output here is just for selection in my 2nd workflow (analytic app), so I will save it somewhere temporary. You can find the temp directory here in your User Settings:

Before proceeding with the other part, make sure your first workflow serves as a selection and is an analytic app with the interface designer configured as above. Obviously it will lead to Workflow 2, so make sure to save accordingly.

Once the setup is complete, head over to Workflow 2, where this serves as your main ETL point after selection:

So as you can see below:

Your temp file needs to be configured in the List Box tool:

Make sure to edit all the parts highlighted in yellow to achieve what you want. So remember the NAME && VALUE thing? It plays here for the selection.

I've attached the workflow and its component assets for you to study. Feel free to ask questions.

If the above solved your need, kindly like & mark as accepted solution so that you may help others find the solution more quickly + to close the thread as is. Thanks!

Workflow 1.yxzp

caltang

@TheEmeraldKnight I think your issue is that you are now stuck at the load phase. You've got the selection down but you don't know how to call the data out and manipulate it.

Here's how:

In your second workflow, you need to add a LIST OF SHEET NAMES MACRO which I created for you. It will dynamically go search for each sheet name in your files from your directory after you selected them. Then, it will load the data from your selected files as one big stack.

I've attached it and you can study it. You can also mark this as a solution if it helps.

Workflow 1.yxzp

All comments

caltang

You'll need a chained app to do what you want.

The first workflow is to call the Directory files and to prepare a temp YXDB that has NAME && VALUE only for your files with condition (starting with... etc.)

The second workflow uses that temp YXDB as an input and is called in via the List Box, allowing you to choose the file of your liking.

caltang

The first workflow:

So for me, I know the output here is just for selection in my 2nd workflow (analytic app), so I will save it somewhere temporary. You can find the temp directory here in your User Settings:

Once the setup is complete, head over to Workflow 2, where this serves as your main ETL point after selection:

So as you can see below:

Your temp file needs to be configured in the List Box tool:

Make sure to edit all the parts highlighted in yellow to achieve what you want. So remember the NAME && VALUE thing? It plays here for the selection.

I've attached the workflow and its component assets for you to study. Feel free to ask questions.

If the above solved your need, kindly like & mark as accepted solution so that you may help others find the solution more quickly + to close the thread as is. Thanks!

Workflow 1.yxzp

abacon

@TheEmeraldKnight I will add onto this - IF you are wanting to run this on Alteryx Gallery, you will need to replace the Folder Browse tool with a Text Box and have the user paste the desired location into it because the Folder Browse tool is not supported on the gallery. This means the user would have to paste the fully UNC path (not a mapped drive location) into the Text Box for the Gallery app to find it.

If you aren't putting this on Gallery, then this point is irrelevant.

Bacon

caltang

@TheEmeraldKnight I think your issue is that you are now stuck at the load phase. You've got the selection down but you don't know how to call the data out and manipulate it.

Here's how:

I've attached it and you can study it. You can also mark this as a solution if it helps.

Workflow 1.yxzp

TheEmeraldKnight

Hi Calvin,

Could you please also share Workflow2? I started going the path of using a list box to select which container(s) to run which though slow seems to be working for the first three imports:

One other consideration is that there be multiple files with the same prefix to import into the consolidated workbook i.e. B005 will have a separate report for Australian and Singapore. I have used the * wildcard within the containers to achieve this.

Many thanks,

Robert

caltang

It's inside the YXZP I gave you:

TheEmeraldKnight

Sorry Kelvin - rookie mistakes.

I know you say "you need to add a LIST OF SHEET NAMES MACRO" but I don't know to do that I'm afraid. I see a XML panel open when I click on the tool (that has now become a question mark) but not sure how to populate the tags.

The following is a list of the files B001 - B015 of which each have a separate tab in the Suspense Template.xlsm workbook i.e. B001, B002, B003...B015. The idea is just to be able to select one or more of the B[NNN] tabs that will then be copied into their respective tab within the Suspense Template.xlsm workbook. There are times that the same file layout may need to be copied in (consolidated) multiple times like B005.

Regards,

Robert

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703