topic Re: Import multiple excel file(different schema) and union? in Alteryx Designer Desktop Discussions

Import multiple excel file(different schema) and union?

abhishekyx — Wed, 26 Feb 2020 11:25:30 GMT

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.

Re: Import multiple excel file(different schema) and union?

afv2688 — Wed, 26 Apr 2023 17:26:02 GMT

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

Re: Import multiple excel file(different schema) and union?

SH_94 — Fri, 12 Mar 2021 08:00:22 GMT

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.

Re: Import multiple excel file(different schema) and union?

TomWelgemoed — Fri, 12 Mar 2021 08:54:10 GMT

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

Re: Import multiple excel file(different schema) and union?

afv2688 — Fri, 12 Mar 2021 09:12:39 GMT

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

Re: Import multiple excel file(different schema) and union?

SH_94 — Fri, 12 Mar 2021 09:19:10 GMT

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.

Re: Import multiple excel file(different schema) and union?

SH_94 — Fri, 12 Mar 2021 09:29:02 GMT

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.😭

Re: Import multiple excel file(different schema) and union?

TomWelgemoed — Fri, 12 Mar 2021 09:38:21 GMT

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!

Re: Import multiple excel file(different schema) and union?

afv2688 — Fri, 12 Mar 2021 09:50:12 GMT

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.

Re: Import multiple excel file(different schema) and union?

SH_94 — Fri, 12 Mar 2021 10:17:06 GMT

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.

Re: Import multiple excel file(different schema) and union?

afv2688 — Fri, 12 Mar 2021 10:40:26 GMT

Now you are asking the questions! 🙂

1-.You should always go with the fullpath, unless yo are replacing a specific part of the given path on the macro, that is up to you.

2-.The file specification and directory depends on your needs, if it is going always to read from the same place there is no need to change, otherwise you would need to specify it.

3.-You can in deed read different files using the same macro, but it would need to specify the file format. Attached you will find the macro I used for my example. You will see there are numbers assigned for each file format. You can get those numbers by adding an action tool to the input, there, under File, you will see a field called "@FileFormat - value= " this will give you the number for the file format.

Re: Import multiple excel file(different schema) and union?

TomWelgemoed — Fri, 12 Mar 2021 10:46:40 GMT

Think @afv2688 explained this perfectly. Hopefully that covers it!

Re: Import multiple excel file(different schema) and union?

SH_94 — Fri, 12 Mar 2021 10:58:26 GMT

Hi @afv2688 ,

Thank you for your detail explanations. I have few queries would like to further clarify with you.

1.I'm a bit lost on this part of the explanations below and can you share the screenshot on how can i get the numbers under file?

You can get those numbers by adding an action tool to the input, there, under File, you will see a field called "@FileFormat - value= "

2. I noticed that a lot of macro workflow , they will use fullpath formula as shown in the screenshot below :May i know what is the purpose of we doing at this step ? Could you briefly explain on the circled part as per screenshot below as i not sure how to read this formula and i plan to edit it if necessary. Why we use ///Sheet1 in this case and how we going to configure it if necessary?

3.May i know why we need to use find and replace tool here in this case? What are the things that we need to replace?

4. Normally how you decide at which step you need to contact to the macro. If there is no formula tool and find & replace in this case, will the macro still can function by just directly connected to the input data?

Re: Import multiple excel file(different schema) and union?

afv2688 — Fri, 12 Mar 2021 11:23:45 GMT

Hello @SH_94 ,

Regarding the questions:

1.- Yo can get the numbers on by adding an action tool and checking the file format like this

2.- What I am adding to the paths on the excel files is the sheed within the excel book. Yo need to specify it to tell the workflow which sheet it needs to read, there is no default on which it will read the sheet even if there is just one.

3.- With the find replace what I am doing is adding the column with the file format. There are many ways to do it, I just used this one.

4.- The more information you dinamically add to the macro the more versatile it will be. The first one I shared had a fixed file format and could only read csv files, this one can now switch between csv and excel files but only when the excel files have the sheetname "Sheet1". We could add a dynamic input to get the sheetnames automatically for example and do much more to make it more versatile.

It depends basically on your needs and what you want to achieve.

Regards

Re: Import multiple excel file(different schema) and union?

SH_94 — Fri, 12 Mar 2021 11:59:25 GMT

Hi @afv2688 ,

Thanks again for the detail explanations. I have additional queries which would like to clarify with you as below:

1. Would like to confirm my understanding on the two control parameters below, is it one for the xlsx and another one for csv? Let said if we have additional xlsb , it mean we need to create three control parameters? Means that one control parameter can only read one format?

2. Noted on this and can i confirm that we would need to build the following formula if we have both csv and xlsx file? May i know how about if we have multiple sheet in the excel, can we choose all or specific tabs to be imported? Is it all just need to be adjust under this formula tools? Could you share with me how will the formula look like if we want to choose specific tab or all the tab within the excel?

3 Could you shared with me how is the workflow look like if you are adding the dynamic input tools?

Thank you

Re: Import multiple excel file(different schema) and union?

TomWelgemoed — Fri, 12 Mar 2021 12:18:11 GMT

Hi @SH_94 ,

This is where it gets a little subjective, but personally, I would prefer to split the top workflow (after the directory tool) to execute different macros as soon as the file type (csv/Excel/ other) starts to differ. Not required, but perhaps easier to differentiate.

With Excels that have multiple sheets (where you most likely don't know the name), your workflow needs to first read the list of Excels and then read (into another macro) the list of all the sheets in that Excel. Sounds tricky, but you're really just doing the same thing, but prior to the 2nd macro your only reading the sheets of the Excel that you're processing. Then you pass that list of sheet names into the 2nd macro and reading them one by one.

The one thing you do start to wonder is - if all these files have different schemas - perhaps you don't want to combine them all in one output? But that's for you to decide of course.

Regards,
Tom

Re: Import multiple excel file(different schema) and union?

SH_94 — Fri, 12 Mar 2021 12:35:04 GMT

Hi @TomWelgemoed ,

Thank you for the detail explanations.

1.Personally i also feel it it more easier to differentiate if we split it starting from Directory tools.Could you share with me the workflow which have multiple macro for me to refer as i want to see how you build the workflow and connect it with each other.

2.Do you have workflow which read the list of excels and then read the list of all the sheets in that excel? Could you share with me if you have it the workflow?

3. Yup, i also thinking on why we need to combine them if it is different schema. Can we adjust the position of the specific row to match with other row which contains similar type of information? May i know do you have the workflow tool as well?

Normally how you will decide whether you want to combine or not if there is different schema? Would like to hear your opinion on this

Re: Import multiple excel file(different schema) and union?

TomWelgemoed — Fri, 12 Mar 2021 12:39:49 GMT

Hi @SH_94 ,

Looking at your point 3: I think we're circling back perhaps to the original question... why did you want to combine all the files? I think we ( @afv2688 & I) assumed that the data is quite consistent and needs to be combined, but perhaps there is more here.

Maybe before we go into solutions, do you want to restate your requirement/objective? You have all these files - what is the end goal here?

Best,
Tom

Re: Import multiple excel file(different schema) and union?

SH_94 — Fri, 12 Mar 2021 12:51:59 GMT

Hi @TomWelgemoed ,

Yes sure.

Perhaps let me explain in the way presented below:

Type of file availables - Different type of files with different number of tabs within the excel files. They also consist of different name of header. But all the file contain certain information, for instance invoice amount. However, different name of header may used by different header. For instance, some ppl put it as Invoice Amount, some may put it as Inv Amount , or some may put total balance.

My Objective- My objective is i plan to combine all the data in alteryx and can we adjust different position of column (for specific row) to the same position of column ? So that we can use it for the calculation purpose.

Re: Import multiple excel file(different schema) and union?

afv2688 — Fri, 12 Mar 2021 12:53:18 GMT

Hello @SH_94 ,

1.- That's not like it, one control parameter adds the file name while the other specifies the file type. If you would like to add xlsb too you would just need to add the file format number on the text input on the find replace tool, thats it.

2.- To add multiple sheets you would need to change the solution like the one I just added

Regards