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?
state | labeler | amount | year |
a | 00012 | 100 | 22022 |
b | 00012 | 200 | 22022 |
c | 00012 | 300 | 22022 |
d | 00012 | 400 | 22022 |
e | 00012 | 500 | 22022 |
f | 00023 | 600 | 22022 |
g | 00023 | 700 | 22022 |
h | 00023 | 800 | 22022 |
i | 00023 | 900 | 22022 |
j | 00023 | 1000 | 22022 |
k | 00023 | 1100 | 22022 |
l | 00043 | 1200 | 22022 |
m | 00043 | 1300 | 22022 |
n | 00043 | 1400 | 22022 |
o | 00043 | 1500 | 22022 |
Solucionado! Ir para Solução.
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/t...
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.