Hi, How can we split an excel to multiple excel files based on a criteria. This is the data in one excel worksheet. I want to split this excel to three excel workbooks based on the labeler. For eg: 00012 data should be in one excel , similarly 00023 and 00043 in different excel workbooks. How can we do that ? Can you please help me? statelabeleramountyeara0001210022022b0001220022022c0001230022022d0001240022022e0001250022022f0002360022022g0002370022022h0002380022022i0002390022022j00023100022022k00023110022022l00043120022022m00043130022022n00043140022022o00043150022022

@Sunithaprdp Yes, you need to add a new column named full path and update the path where you need to save your excel output files.

Multiple Excel Outputs version change.yxmc

@Sunithaprdp I've edited the XML of this to make it 2019.2 - let me know if you're still running into issues though I'm not sure this will be the cause.

All comments

@Sunithaprdp One way of doing this

SplitFile_1011.yxmd

Thanks for the solution, however the file consists of huge data more than 1000 lines so how can we modify this with Input tool. When I am trying for that I am not able to customize the Full Path formula. So how can we modify the formula. The actual excel workbook consists of various columns with different headers.

Looking forward to hearing from you.

Thanks and Regards,

Sunitha P

the file consists of huge data more than 1000 lines so how can we modify this with Input tool. When I am trying for that I am not able to customize the Full Path formula. So how can we modify the formula. The actual excel workbook consists of various columns with different headers.

Could someone please help me on this ask?

@Sunithaprdp When you say you are not able to customize the formula tool can you elaborate, please?

in the above example I'm writing all the output files into a temporary location that's why I gave '%temp%' + the field name you want to create the excel files with the name of the fields. Can you update your path in the place of '%temp%'. For example

please let me know if I'm not making any sense.

@Sunithaprdp if you don't want to go through the hassle of setting up the formula and output configuration yourself, you could try using the macro I created for exactly this purpose: https://community.alteryx.com/t5/Public-Community-Gallery/Output-to-multiple-Excel-files-or-sheets/ta-p/951995

All you have to do is download it and then instead of an Output Data tool, at the end of your workflow, right click > insert > macro and then navigate to wherever you saved it.

After that, the configuration is pretty straightforward, you would just pick your [Labeler] field as the one to split on. Of course I'm happy to answer any questions if you get stuck.

I am unable to add this macro as I am getting below error. Also wanted to confirm if this macro helps to segregate data based on the criteria as I mentioned?

I am not sure if I understood it correctly. Sorry to ask you multiple questions as still I am taking baby steps on this tool.

Do we have to add a new column named Full Path? In my case already my input has got multiple columns of which I have to segregate based on the labeler and then divide this excel into three excel workbooks in order to get three inputs for my ultimate alteryx workflow.

Hope I could convey my concern. Thanks in advance.

@Sunithaprdp Yes, you need to add a new column named full path and update the path where you need to save your excel output files.

@Sunithaprdp yes, the macro will split the data based on the [Labeler] field, as shown:

Split data based on label.yxzp

I am not sure why I am unable to import the workflow? Is it because of the old version that I am using? Even I am not able to upload the macro.

@Sunithaprdp what have you tried for importing it, just right click > insert > macro? And that's not working?

What version # are you using? I can save a copy as that and we can see if it works if it's really old.

Alteryx 2019.2 x64 is the version (.yxmd)

Multiple Excel Outputs version change.yxmc

@Sunithaprdp I've edited the XML of this to make it 2019.2 - let me know if you're still running into issues though I'm not sure this will be the cause.

Thank you so much , it worked!

No problem @Sunithaprdp - if you could mark the correct solution then that'd be great so others with the same issue can easily find it in future!