Import multiple excel file(different schema) and union?

[abhishekyx]

Hi,
I want to import multiple excel file( to one data input tool) with different schema and union it?
I have come across multiple post which describes the method somehow I am facing issue implementing it. 
Here for example I have include few file. 

[afv2688]

Hello @abhishekyx,

this should do it:

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Regards

[SH_94]

Hi @afv2688 ,

Could you guide me on how to use the macro provided? It would be great if you can show me with the screenshot on how to use the macro and things to update when using it.

Thank you.

[TomWelgemoed]

Hi @SH_94 ,

The macro is actually simple once you know your way around a bit. All we're asking Alteryx to do here is to read the list of files and output the results one by one. There are a couple of settings you need to get right - and if you've never built a macro before, these are the steps:

1. Open a new workflow, connect to one of the CSV's you're using (with an input tool) 

2. Connect a control parameter from the Interface tool pallette to the Input tool. An action tool will automatically be added

3. In the Action tool, find the filepath of the input tool and select it. Then, at the bottom of the screen (see image below), tick the box to replace the string value (with the incoming file name). This will effectively allow Alteryx to replace the name each time it iterates through the macro

4. Connect a macro output tool - no configuration required

5. Open the Interface designer. If you can't find it, check the View menu and tick the Interface designer box. If it is already selected, it's probably hidden on the left hand pane somewhere and you can simply click it

6. Navigate to the properties menu in the interface designer (highlighted in the image below) and choose to auto configure by name. This simply ensures that the macro doesn't fail if the column names are different. By default, Alteryx would fail otherwise, as you can see from the first option (Error if different).

7. Save the macro

8. Back to your original workflow, connect the directory tool to the macro

You're done!

Regards,

Tom

[afv2688]

Hello @SH_94,

@TomWelgemoedexplained it very well. I just want to add what you would need to do to use it:

1.- Use the directory tool to browse to the desired folder.

2.- If there are if in the specific directory are many unrelated csv files remember always to try to specify in the "file specification" field from the directory tool any common parameters the file may share, for example, use:

" *.csv "

to get only the csv files of that directory or:

" 01-*.csv "

to get all the files that start with 01- and are csv

Remember that as it is set up, you can only use it for csv files.

Regards

[SH_94]

Hi @TomWelgemoed ,

Thank you for your detail explanations.I have read multiple of articles and watch youtube videos but i failed to update the macro which downloaded from community.Perhaps my knowledge is still not enough when dealing with macro. Basically i had watched those videos as per your explanation below. 

However, i have few queries which until now still not yet solved as i still can't configure the macro that i downloaded from community.

Hence, would like to seek your advice on those queries.

Based on your explanations below, i have few queries as below:

1. For the step 3,when u mentioned to tick the box to replace the string value (with the incoming file name) , may i know what it means here? and could you briefly explain what is the purpose of we doing this step? The incoming file name Is it referring to the file uploaded with the input tool?

2. For step 6, may i know can we still proceed if we have the different column name between the files and each column name (that we wanted) are different with one file another. For instance, for file A , it show the column name as : Asset No while for file B , it show the column name as Asset number while another file may show the column name as Asset part No. Can we still do it in the alteryx ?Do we need to change the name for each file in alteryx so that Alteryx can read all the file when we running the macro?

So far, i still can't successfully update the macro ( downloaded from website)and will try to build my own macro to get it familiarise. 

[SH_94]

Hi @afv2688 ,

So far i still reading the article and watch video on the importing of the files using macro.

I saw a lot of articles and video posting on how to build the macro but they didn't show how we going to update the macro or what are the things we need to configure if we download from online.

I understand when reading it but i don't know how to edit the macro downloaded as everytime i try to import the files and run the workflow, it will show errors and not sure how to fix the errors since no much discussion can be found in the internet.

My main concern is i have multiple of files and have different column name and each column position of the file that i want to import are different. Hence, i not sure whether it will work in alteryx . 

Sorry for asking the stupid question as i not sure why i still can't edit the macro successfully.😭

[TomWelgemoed]

Hi @SH_94 ,

Completely understandable - macros almost require a different way of thinking than "standard" workflows.

To recap what we're doing, we're telling Alteryx that we have a list of files (that are read through the directory tool) and that we will pass the filename (with the full directory path) into the macro. This is then what happens in the macro:

1. The control parameter "controls" which field to use as the Filepath & name

2. The action tool updates the input tool to change the input for every new file

3. The workflow runs for each instance of each file and outputs the data

So, to now answer your questions:

In step 3, where we are asking the action tool to override the filename each time the macro is run, we have the following things that are happening:

1. We're selecting the action type - Update Value in the drop down at the top. This does what it says - it will update whatever value you specify in the configuration below

2. Where choosing which value to update - in this case, we want to override the complete filename, as we want the macro to load a new file every time it's run

3. We're telling the action tool which part of the value to update. In some cases, for example, you may choose to update only the directory, or only the file name in the path - in this case, we're checking the "Replace a specific string" box and asking the action tool to override the entire file path. Be very aware that Alteryx simply sees your value in this box as text, so whatever you put in there, it will try to replace if it comes across this value. So in our example, the macro is saved with 1 of the csv files, so every time Alteryx is running, it's replacing that original file name with the new one that you're feeding into the macro. This is the clever part

To answer your second question - you do not need to worry about the columns - Alteryx takes care of that. This is because we configured the interface designer to allow the schema to be different - if we did not change the setting in the Interface designer, Alteryx would not allow new columns (a different schema). So as long as you add a macro output, save the macro and then use the macro as part of the overall workflow (use @afv2688' s workflow as your guide) you should be fine.

On a final note - if you're still getting an error: pay attention to the string that is in the Action tool that @afv2688 used - he would've had to configure that for his own folder, and you of course now need to configure it for yours. I copy images below that show all the areas you need to update.

Good luck!

[afv2688]

Hello @TomWelgemoed ,

Don't worry, everyone needs to go throgh the step of learning how to use them, it's perfectly normal.

What yo have there is called a batch macro, this macro will iterate with every line from the input and repeat the process for every record.

In our case, since we are only reading the files, what we are doing is chainging the file every time and storing them in what we will call a "temporary file", therefore it goes like this:

Reads file one -> stores the results

Reads file two -> stores the results with the prior results

The way the files are stored together depends on the selection you do on the interface designer, you can either tell the macro to store the files (like using an union) together by name, position or error if the format is different. Once every files has been processed, the output is shown toghether, with the format conditioned by yor selection on the interface designer (Cntrl + Shift + D)

In our case, if you had all the inptus in different tools unioned together or using this macro would be the same, you just need to decide wheter given how the files come if use the position using the column names from each file or the position on which they come in.

[SH_94]

Hi @TomWelgemoed ,

After i went through your explanation, it seems like now i understand what is macro about. 

Currently , i am trying on the building up the macro and it is finally connected. However, i have queries as shown below:

1.May i know how do we decide normally for Fullpath , Directory and Filename as shown in the screenshot : It seems like Fullpath is similar with Directory.

2. Do we need to change normally for the setting as per screenshot below in term of File specification or include directories ?

3. If we want to import xlsb ,csv and xlsx file together, may i know is it possible to do it with one macro or we need to create three macro in this case?

Many thanks for your help.