Comparison between xlsx, xlsb and CSV when running batch macro(different schema)

[SH_94]

Hi Community,

I would like to ask about the type of excels files used ,ie :xlsx ,xlsb and csv when importing the file via batch macro (different schema).

May I know which type of excel file in Alteryx ,when we running the batch macro, it Is able to extract the file name and tab name for each excel file.

Secondly, in order to allow the Alteryx to read the tab name of excel file, may I know how consistency that the tab name need to be so that Alteryx can recognised them successfully?

Out of these three files, may I know which type of excel files are more compatible when running batch macro in Alteryx? 

[CokeLuke]

In my batch macros, I typically import xlsx and csv files.

Using directory tool, one can determine the file names/pass them to the batch macro. I now use the message tool to show the file and timestamp when it batches through.. 

With sheets, you might want to see this post Input multiple Excel sheets or tabs at once - Alteryx Community entrée de données eingabedaten

Typically csv files only have one sheet/tab.

[SH_94]

Hi @CokeLuke ,

Thank you for the great information. 

Can I clarify with you on the following :

1. As my data files mostly are in xlsb format and would like to ask whether it is more suitable to change it to other format ,ie : xlsx before importing it into the Alteryx. Currently, my only concern is my data mostly in xlsb and have different schema (May contain different tab within one excel file).I just worry that I need to configure a lot in Alteryx before able to use them.(which I think I don’t have enough knowledge yet to deal with it)

2.I heard about the directory tool before but never used it .Could you briefly explain on what is the purpose of we using it when dealing with macro?

Do you have any recommendations on the article or discussion about different schema? As currently I have a lot of file with have different structures but there are some consistency between all the files ,ie : some header column have the same name across all. 

I have the macro with me but so far I only know how to use it for the same schema. I haven’t try to use the Macro for different schema and I not sure if there are  any additional tools that I need to learn before using it

Lastly, thank you for the article shared.

[CokeLuke]

I've had no issues processing xlsb files in the past. Since you are expecting different schemas, by default you will get "has a different number of fields than the 1st file in the set and will be skipped. That is why it needs to be a batch macro with a dynamic input.

Directory tool provides a list of useful "fields" that can be used in your batch macro:

Check out the directory tool example in Alteryx to get a better understanding of the tool.

This link might help, too: Build Your First Batch Macro! - Alteryx Community

[SH_94]

Hi @CokeLuke ,

Thank you for the great explanation and sharing on the xlsb information 

Could you briefly explain about the batch macro with dynamic input? Any additional function tools that we need to learn when dealing with dynamic input ?

Many thanks in advance

[CokeLuke]

Something as basic as this:

Control Parameter "label":

Action to "replace a specific string" in the provided file path read from the text input tool. What's highlighted is the file with path location:

Text Input with "File" column with file path and file name:

For extra, added a Formula tool to see timestamp with the message tool:

To display message with file name and timestamp (to ensure/watch different files are being processed):

Dynamic Input with "template file" and "File" selected from dropdown to "Change Entire File Path":

Doing something with the Summarize tool:

And nothing special with the Macro Output tool to complete the macro:

Hope this helps!

[SH_94]

Hi @CokeLuke ,

Thank you so much for the detail explanation with the screenshots provided. I knew the concept for the screenshot from 1st till 4th screenshot but the screenshot from the 5th till 8th, this is quite new to me.

May I know what is the purpose we put the time stamp here?How do we know whether it is processed or not for different file?

Secondly, can I understand in this way that dynamic input is basically we just input the template file and change to the entire path ? Because previously I thought that dynamic input mean we need to select specific field to be the input of the files. Can you briefly explain on the template file? Is it mean that we need prepare one template file so that Alteryx can read other files?Do the template file need to be fill in with figures?or just need the header column?

Thirdly,may I know what is the purpose we doing summarize tool here at this stage?Will it be too early to summarize the data? Sorry for asking this question because I always thought we will use summarize tool when come to actual workflow. If we use summarize tool in the macro , will it affect our result in the actual workflow because I worry that the detail information will not be available since the information had been summarized.

Lastly , can you share with me the workflow as I feel it is very useful for me to refer when I want to update my original workflow.

Many thanks again for your help.

[CokeLuke · Accepted solution]

Time stamp column is just extra... informs me or the user what time a file is being processed. Likewise for the comment tool - informs the user that files are being processed in the Dynamic Input tool one at a time.

Dynamic input requires the entire file path and that file is the "template". Since this is a batch macro, you will not encounter files being skipped because the file doesn't match the template. 

Designers choice to process/summarize data in the batch macro or in the main workflow. 

See attached.