community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Input Batch Macro - AKA the Extra Dynamic, Dynamic Input

Alteryx
Alteryx

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

 Meme.jpg

 

 

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.

ACE Emeritus
ACE Emeritus

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. 

@thizviz aka cbridges, Bolide
http://community.alteryx.com/t5/user/viewprofilepage/user-id/2328
Alteryx
Alteryx

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.

Alteryx
Alteryx

I believe we are now able to upload .zip files, so that should help anyone wanting to install this.

 

I have actually attached an Alteryx zip installer. If you download the zip file and then change the extension to .yxi. If you double click it, Alteryx will recognise it and install it for you.

Meteoroid

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.

Alteryx
Alteryx

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  

Asteroid

@JoeS:

 

Instead of the text input tool, I've used the input tool to ingest the relevant data (which are multiple 3-tabbed excel files) for the input batch macro and input batch sub macro. I'm not getting the results I'm expecting.

 

I get the error - Unknown variable "NonRepeatableFilePath"

 

Thanks in advance for your help.

 

Alteryx
Alteryx

Hi @Ojay 

 

Sorry, not quite sure what you mean?

 

Are you using the Input Batch tool as a macro (AKA right click within an existing workflow Insert > select macro)? Or have you opened it up as a workflow?

Asteroid

I appreciate your quick response.

 

I right clicked on the input batch tool in the workflow and opened the macro.

 

Within the macro, I changed the text input tool to an input data tool to ingest the data from the 3-tabbed excel file and also right clicked again on the input batch tool.

 

Everything else I left untouched.

 

Help me crack this.

Alteryx
Alteryx

Hi @Ojay 

 

You shouldn't need to modify the macro at all.

 

Just use it as it is within a workflow.

 

If you use the sample workflow and modify the tools at the start of that to be relevant for your data, that should be good.

Labels