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. 

[TomWelgemoed]

Hi @SH_94 ,

okay, think I get it. Could you share some of the non-csv file samples to make building the process easier? 

Also, would be nice if you can share some sample columns that should be renamed in this set.

[SH_94]

Hi @TomWelgemoed ,

As requested , kindly find the file below for your reference. 

Basically i have highlighted the column in green ( which are the common between all the listing ) for your easier understanding .

Those highlighted in green are those information that should be match under one column if we able to do it in alteryx

[TomWelgemoed]

Hi @SH_94 ,

In the interest of time I've done one here only focusing on the Excel sheets - I'm assuming you'll use the CSV solution already provided together with this.

There are a few things to note:

1. The Excel sheets are messy and not well formatted. Here I would suggest that you spend a little time to come up with a mapping sheet to identify all the columns that need renaming for consistency, e.g:

FieldName	FieldRenamed
Asset No	Asset Number

2. You need to embed the renaming of the columns into the macro to avoid having to rename/merge rows after the macro has completed. This is a little more tricky to explain, but I try to do this with a screenshot:

You use your mapping sheet created in step 1. above in the 2nd macro to update columns as you read them out of the Excel sheet. This "Dynamic rename" tool is very cool - it allows you to use your mapping file and to rename any of the columns it finds with what is in your mapping sheet.

Note: As there are blanks in the files above the header, I've added a filter to remove rows where the 3rd column is empty and then used a separate dynamic rename tool to use the first row as the field names. You can see that rename just before the one that is highlighted in the image.

But that should be mostly it. I didn't try to do all your green columns - I couldn't see well what you needed - but I think you should be able to work with this now the logic is in place?

I attach the workflow. I assume you know, but on the macro you can right-click and in the subsequent menu, click Open Macro at the bottom. That allows you to see how it's been built. There are 2 macros in this small workflow.

Final point - whenever you change the file names to your local files, you need to update all the tools that refer to the file location, including the interface tool.

Good luck,

Tom

[SH_94]

Hi @TomWelgemoed ,

Thank you for your detail explanations. I would like to confirm with you on the following items:

1.Understood that the file are quite messy and let said if i rename all the columns needed to become standard name in all the excel sheets locally before importing it  to Alteryx. In this case, will alteryx read it easily despite the file are still messy but all the files contain consistent header ,ie : Asset Number with different position in the different excel sheets?

Because currently i plan to update it locally to change certain column header only and what do you think of this plan ? It is workable if i do in this way?

[TomWelgemoed]

Hi @SH_94 ,

Sure, the workflow I passed on will process whatever column you put through it - no problem. I just wanted to save you the trouble of afterwards having to clean it up! But if you, for example, don't know how the columns have been named, you may have no other choice than to load everything first and then tidying it up.

If that is your strategy, just make sure to update the Text tool in the second macro (where we did the Dynamic Rename) and then put dummy values in the Text field (e.g. Fieldname='xxxxx') and then you can add your list later on to the tool.

Regards,

Tom 

[SH_94]

Hi @afv2688 ,

Sorry to disturb you over the weekend.

I would like to ask about the concept of the workflow that you shared previously as follow. Currently i would like to build the workflow which can read the xlsx file (multiple sheet within the excel file and multiple subfolder). In addition, i would like to utilise the file name and the sheet name both together for all the files. Hence, i have few queries that would like to clarify with you so that i able to tailor the workflow accordingly.

1.May i know what is the purpose of adding the dynamic input tools in this case? For the edit part, we would need to choose the folder that we want the Alteryx to read?

2. Following the question number 1, i notice that some macro they will start with input data and some they will start with directory. Can i confirm that if we use Directory tool , then we must use the dynamic input ? 

3. Could you briefly explain on what is the purpose of we putting this formula in this macro?

4. Lastly, may i know in what circumstances that we would need to choose the specific tab / list of sheet names? Will it affect Alteryx to read the multiple tab if i choose specific tab in this case?Since this is the macro,is it normally we would need to choose full path as the options in the macro?Or we can choose file name? Because i would like to ask Alteryx to the read the file name and name of the tab concurrently.

5.Lastly, May i know how to make the build the workflow so that the file name column will show the name of each tab?

[afv2688 · Accepted solution]

Hello @SH_94 ,

Regarding your questions:

The dynamic input acts as a batch macro extracting the desired data (either the information from all the excel files or the sheet names, depending on the configration). The edit button takes and a template which would be the one your files are following, you can say it acts as a dummy, informing the macro how your files will look like. Take into account, that the files need to follow the same structre as the one on the template or they will be skipped. A message as a warning will appear on the result window if this is the case).

2.- Input data can have insert data when the files either are individual or share the same name, if that is not the case, we usually use the dynamic inpt tool. This has also other uses (for DB connections replacing parts of the query for example)

3.- Since we are extracting all the sheet names, what I am doing on that formula tool is replacing the "<List of sheet names>" parameters with the actual sheet name.

4.- Within the macro you only need to add a file, and in this case an excel file. Unless you want to tinker a little bit with it, I would recommend leaving it as it is. This is a dummy file to specify what filetype will come in. You could for example replace only part of the string.

5.- I have already done that on one of the last posts you have published:

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Maintain-File-name-and-sheet-name-usin...

Regards

[SH_94]

Hi @afv2688 ,

Thanks a lot for the detail explanations.

Appreciate it a lot.

[pokhan27]

can we save this app to alteryx server? 

actually i tried but am having an invalid directory error