Input Batch Macro - AKA the Extra Dynamic, Dynamic Input

It’s about time I release my “Input Batch” macro, so here we go......

For those of you familiar with the Dynamic Input, you will know how much of a life saver it can be, but from time to time, you may be frustrated by the need for the file formats to be the same. Both the type of file (Excel, CSV, TXT etc) and also schema (field types, size and number/name of columns).

This can often cause a few issues, as Alteryx reads in the template and will then use that as the type of file, and then the first file it reads in dictates the schema.

Well, if you want the ability to read in many different types and many different schemas at once, you are in luck!

I have built a macro (with a sub-macro) that will be able to read in: ".yxdb",".cydb",".sz",".avro",".csv",".dbf",".mdb",".xls",".xlsx",".xlsm",".sas7bdat",".sav",".sqlite",".xml”

The input required is the full path with extension. This enables you to use the directory tool, apply any logic to filter the list (AKA get todays files) and then input that in.

After that it will go through all the files and read them in. Then using the power of Union it will align all columns named the same, any extra columns get added to the end.

There is also some extra stuff when it comes to Excel, it will actually loop through all the sheets and read them in one by one.

Hopefully some of you will find this useful.

If you provide it file types that it is not able to read, it will through a warning.

Feel free to add comments about future enhancements and I’ll look to see if I can add them in.

Attached is a zip file (.7z), with the installer (.yxi) and some sample data with a sample workflow.

InputBatch.7z

Input

Accepted answers

All comments

ThizViz

I look forward to playing with this! One of the file types I worked with consistently is .prn. Every report from our system came out as .prn. Then if I would open it in Excel, some files were .csv file (but I had to use both tab and comma, because there would often be commas inside a field, like "ABC, Inc.", or the files might possibly be fixed width. On occasion, the files were even pre-formatted "reports" with multiple headers, multiple sub-tables in one file, etc. I never had an issue with the repetitive, standard inputs in Alteryx. But these weirdos gave me more than my fair share of headaches! I wouldn't spend a lot of time on it unless other's systems also dump out .prn files.

JoeS

I can't say that I have heard of that file type before. It all seems fairly bespoke in order to add into my macro and quite complex.

Happy for others to let me know that you have the above file types and if you can send some examples I will see if I can add it in.

Aastha

Thanks for sharing this macro. I want to change the current configuration from all sheets to a particular sheet name as all my Excel files have the same sheet name from which I need to read data. Could you tell me what changes need to be done in the existing version to implement the same? Thanks in advance.

JoeS

Hi @Aastha

Glad you like it!

To do that you need to pass the sheet name through into the Macro rather than just the name of the workbook.

So something like:

C:\Users\jstrellis\Dropbox (Alteryx, Inc.)\MyDocuments\Demos\Full Demo v11\Customer.xlsx|||'Sheet1$`

Then it is built to just read that specific sheet rather than batching through all of them 🙂

DataNuker

Hi, I have tried to use the macro with xls files using a directory tool, not doing anything within the macro just added the macro and I am getting the below error:

Input Batch (5) Record #1: Tool #12: Record #1: Tool #4: No table chosen; Please select a Table from data source.

Input Batch (5) Record #1: Tool #19: No valid fields were selected.

Input Batch (5) RecordInfo::CreateRecord: A record was created with no fields.

Designer x64 Finished running in 1.7 seconds with 3 errors

Just to add I have tried the sample workflow and it did work with the sample CSV and Excel file.

Please can You kindly advise.

Kind regards

Alteryx version: 2018.4

JoeS

Doh, unfortunately I didn't test it with an XLS.

It looks like that will require a fair bit of rework as I can't request back the list of sheets. I'll see if I can incorporate them some how.

ztahir004

I have a xlsx file with around 72 sheets that I am trying to input dynamically based on column position. The simple sheet dynamic input does not work because it says some sheets have different schemas. I want to override that and input all sheets based on column position. Can you guide me?

JoeS

Hi @ztahir004

The macro is currently set up to auto configure by name rather than position.

If you down and install it, you'll then need to update the two macros (main and sub macro), to auto configure by position within the interface designer.

bchen1

Thank you so much for this macro. Quick questions

Is it possible to configure it so I can output for Rows 10 and beyond
How would I output just the filename? Right now when I batch the files, the full path is being pulled and I just want the filename

Thanks for the assistance.

JoeS

@bchen1 wrote:
Thank you so much for this macro. Quick questions
Is it possible to configure it so I can output for Rows 10 and beyond
How would I output just the filename? Right now when I batch the files, the full path is being pulled and I just want the filename
Thanks for the assistance.

You're welcome, I am glad you are finding it useful.

To update it to only output from 10 rows and beyond, you'd need to open up the macro and modify the input tools within. I'd then recommend saving this as your bespoke version as my guess is it won't always be from row 10 in all uses?

For the second part you could modify the input tool as well, but there is a nice easy function in Alteryx to extract the file name from a file path:

FileGetFileName([FileName])

That should then give you what you need without changing the macro itself.

Thanks

Joe

monsieursnow

Hi @JoeS

Thanks for sharing this macro. You don't know how many times this saved me.

I just wanted to share some potential bugs which i couldn't resolve:

1) unable to work on xlsm workbooks

2) if worksheet name has a space at the end, then it throws out an error

I'd wish i know how to fix it, but just putting it here for tracking or once i find time to "attempt"
to fix it!

Edit:
Replying to myself - i found a solution for the above problems.
i'm not sure if this will fix xls or xlsb problem, but the sheet name having a trailing space was a problem for me.

Hadn't had time to incorporate this back into your original workflow, but below formula should fix the trailing space problem.

It's currently reading the Sheet Names as-is without the "$", which might cause problem if there is a trailing space like my case.

(Somehow it trimmed it and couldn't find that particular worksheet)

Formula:

</quote>

left([FileName], FindString([FileName],"|||")+3)
+'`'+
[Sheet Names]
+'$`'

</unquote>

alinmihalcea

One huge issue with this macro is that in contains another macro with the same name and if I want to save the workflow to a gallery, it will not find the sub-macro.

And here there are two different issues:

If I add the sub-macro as additional asset with the same name it doesn't work. Since it has the same name, it will look for InputBatch.subyxmc. I can't save a file with this extension.
If I change the name of the sub-macro and add it to the main macro, something goes wrong and the [Sheet Names] field becomes unknown.

The macro is great, but why would you name the sub-macro with the same name as the main macro? (

JoeS

@alinmihalcea wrote:
One huge issue with this macro is that in contains another macro with the same name and if I want to save the workflow to a gallery, it will not find the sub-macro.
And here there are two different issues:
If I add the sub-macro as additional asset with the same name it doesn't work. Since it has the same name, it will look for InputBatch.subyxmc. I can't save a file with this extension.
If I change the name of the sub-macro and add it to the main macro, something goes wrong and the [Sheet Names] field becomes unknown.
The macro is great, but why would you name the sub-macro with the same name as the main macro? (

That's a great point to raise, I wasn't aware this wouldn't work on Server and is something for me to test.

The is no real reason why I gave the submacro the same name, as it was almost irrelevant what it was called.

The main importance I focused on, is that the extension was not .yxmc and I went with .subyxmc so that it then doesn't appear in the tool palette as a macro in it's own right, as it's not intended to be used as one.

This could then cause an issue when it comes to server, as I wouldn't want the submacro to appear in the tool palette, as this would confuse users, but if server won't pick it up, then I may be stuck - I'll have a play

JoeS

Needed a modification for 2023.1 please find an update attached

InputBatch.zip

cjaneczko

@JoeS I came across the post as I was having issues with importing files as data type were different on some files. The Dynamic input tool was throwing errors on those few files. This tool works great, but is there a way to specify which line to start pulling data? The problem with my sheets is everything starts on line 2. Line 1 happens to be a blank row so its bringing the headers into the table. I know I could filter them out, but the Fields are all set to V_String now because of this instead of the the data type associated with the actual values in each field. If it started on Line 2 the data types would all be correct.

cjaneczko

Thanks for the follow-up. I did manage to find a spot in one of the nested macros where I could alter the row level that the import starts at. I was able to import a whole directory of files properly after altering the "Start Data Import on Line" in the Example.txt input data tool within the InputBatch.subyxmc.

JoeS

@cjaneczko wrote:
Thanks for the follow-up. I did manage to find a spot in one of the nested macros where I could alter the row level that the import starts at. I was able to import a whole directory of files properly after altering the "Start Data Import on Line" in the Example.txt input data tool within the InputBatch.subyxmc.

Yeah, a hardcoded change for your use case is definitely the quickest way - sorry I can't add it as a feature to the macro.

I'll see if I can get some time and have a think if there is something else I can do.

jknutzen

Hi @JoeS, thanks for this great macro! I've been using it in 2022.1 and am in the process of upgrading to 2023.1 when I found your update that some modifications were needed for this tool. I've downloaded the .zip file, changed the extension to .yxi, and run it in Alteryx. I get an error message that it's missing a configuration file.

msg11

I've been able to use this Macro successfully when using Alteryx Version = 2022.1.1.42636, but when using Alteryx Version = 2023.1.1.336 it stops working. Is there anything different in newer versions of Alteryx for .xlsx files that would change the way they are read or how sheet names are found within the Macro?

DavidKennedy

Hi @JoeS,

I really like the concept of this macro but I haven't been able to get it to work. I'm trying to input data from the same sheet ("details") of several different Excel (.xlsx) files. I all I get is warnings stating that "Sorry .xlsx file type not available..." and I can't figure out why. Any recommendations?

Also, I'm on the 2023.1. I saw that there was an updated macro for that version but I couldn't seem to get it to install.

Thanks,

David

FrannieMac

I recently experienced this same issue and found that the .XLS format won't work. At least not when trying to build a directory and aggregating via dynamic input. Here is the Error Code I get. If anyone else finds a work around aside from installing the 32-bit Access driver please keep us posted!

Kind regards

Alteryx version: 2021.4

image.png

wyehongchia1206

I've encountered an error when trying to use the macro. Appreciate it if there's any advice on this. Thanks!

Best Regards
Alteryx Version: 2024.2

image.png

alaaan

Hey @JoeS !

After the force Alteryx Upgrade from 2023 to 2024, could not run the input batch macro tool anymore..
Current issue when running batch input on Alteryx 2024.2.1.14 😢. Any possible solution on this?

Error on Main Workflow

Error on Macro

Error on Submacro

Quick Links

This months top contributors

AkimasaKajitani 388

mceleavey 388

mbarone 337

Hollingsworth 335

LanisC 335