Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Don't forget to submit your entry for the Excellence Awards by October 30! | Need more information about the program? Check out the blog here

How to create folders and output excel file

Highlighted
5 - Atom

Hi, the data I work with looks something like this, here I need to create a folder for Level1 and excel output representing Level1's data.

But there's a catch in Output. Inside this Excel workbook, we should have different sheets for each Level2.

How to do this in alteryx?

Eg: We create a folder for ABBY, Inside this folder, we have single excel workbook which has A as first sheet and B as second sheet

 

IDLevel1Level2value
1ABBYA1
2ABBYB2
3LISAC3
4LISAD4
5LISAE5
6LISAF6
7GABBYG7
Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

How about Outputting a Excel file for each Level1, each of which contains a sheet for the Level2 values? That can be done with a single formula and a couple tools. Check out the attached example to see how I use your data to write these outputs to the temp folder.

 

The trouble with the original request is that Alteryx cannot directly create folders. It can be done, but you'll need to use the Run Command tool to execute mkdir. 

Highlighted
5 - Atom

Hi 

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

In that case, here's a quick example how a Run Command tool can be used to make the necessary directory. It's inside a batch macro so you can update the command argument directly without having to write a .bat file first and then run that. 

 

The Block Until Done tool is handy for making sure the Run Command is ran first before the Output attempts to put any files in the folder.

Highlighted
5 - Atom

Thanks for the quick reply, this makes sense.
But I am using alteryx 11.7.4.43055. So, except for the run command part, I can see everything, only that tool is showing as a Question mark. If it's not too much to ask, can you just show what did you write inside the run command tool, so I can copy and paste in this?

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

That's odd because there should be no difference in anything I built between our versions. Do you know how to edit the version in the XML text of the workflow? Sometimes that works better than letting Designer overthinking it. 

 

Anyways, here's the contents of the MkDir.yxmc batch macro:

 

20200207-MkDir.png

 

 

 

 

 

 

 


The arguments only works if you edit the name (not label!) of the Control Parameter tool to "Folder". This was it can be directly referenced in the argument. 

 

/c md "%Question.ControlParam.Folder%"

 

The Text Input and %temp% output of the Run Command tool has no affect on the make directory process and are only there to satisfy the connection requirements of the Run Command tool. 

 

Is that what you needed?

Highlighted
5 - Atom

DLS_0-1581113446212.png
I am getting this above error when i run. I created my own macro (replica of your macro) and joined, but getting this error

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

Ah yes, I got that error when the directory already exists. I'm sure someone better at Command Line could suggest some edits to control for that.

 

...using out magical Alteryx Community powers, we can summon the great @patrick_digan 

 

Highlighted
9 - Comet

You should be able to specify -p as a condition in your run tool. So...

 

mkdir -p [filepath]

where [filepath] = parentfolder/ABBY

would create this file. Also, it should create any intermediate folders (e.g. parent/child/ABBY would create both the Abby and the child folder, if they didn’t already exist.)

 

Hope that helps!

Labels