Input Batch Macro - AKA the Extra Dynamic, Dynamic Input

[JoeS]

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.

[Ojay]

Done but apparently with more records than I was expecting even though I filtered using the most recent files.

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

[JoeS]

Just giving this a bump up as a few of the people I have worked with recently weren't able to find this when searching for:

"has a different schema than the 1st file in the set."

If you do get this error when using either a wildcard with the input tool, or using the dynamic input tool, my macro should solve this.

I also can't edit the first post, so to this one I have attached the yxzp as it now lets me.

[mahadevaswab]

Thanks for sharing the Macro, however the attached macro could not able to extract /include the Excel file type ".XLSB", Hence, could you please help us by adding the same.

Regards

Mahadevaswamy

[Pradeep66]

This is so fantastic. What If I want to select only top 5 files? meaning if my folder has 25 files, I just need to pick latest 5 files from the directory is there a way to achieve this. Please help.

[JoeS]

---

@mahadevaswab wrote:

Thanks for sharing the Macro, however the attached macro could not able to extract /include the Excel file type ".XLSB", Hence, could you please help us by adding the same.

Regards

Mahadevaswamy

---

Sorry @mahadevaswab XLSB do not enable you to read all the sheet names in a list, so it's not something I can add in unfortunately.

@Pradeep66 wrote:

This is so fantastic. What If I want to select only top 5 files? meaning if my folder has 25 files, I just need to pick latest 5 files from the directory is there a way to achieve this. Please help.

---

@Pradeep66 You can use the sort tool to sort by date and sample tool before the macro to limit the number of records going into it.

[anhdo92]

Hi @JoeS 

First of all, thanks a lot for sharing this Input Batch Macro. It works wonder and saves me a lot of time trying to to find a workaround in my workflow!

However this tool stops working when I publish my workflow to Alteryx gallery. It returns an error saying Cannot find macro "InputBatch\InputBatch.yxmc". (Tool Id: 196) every time I run the workflow in gallery.

I've been searching around for a solution but couldn't find one that works. Could you please assist ?

Many thanks