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

Alteryx designer Discussions

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

The highly anticipated Alteryx Community tradition is back! We hope you'll join us!

Learn More
SOLVED

Split excel data based on a field, add headers and export as separate files

Meteoroid

Hi,

 

I have a table with headers, i want to split the data based on "Car" field and export those set of rows to different files. Before exporting these files i want the first three rows (in red) to be static in each file that I export. how to do this? i dont know macros or other programming languages.

 

 

Data base name: xyz   
Sales Data   
Car Quantity soldCost per unitCity
VW2500000Newyork
VW4600000Seattle
MG61000000Chicago
MG1200000Seattle
Alteryx
Alteryx

Hi @BalajiMRB 

 

Would it work if you had the Database name and Sales Data information stored in fields?

 

If so, then the following workflow should work for you. By splitting the data out by the first 2 rows (defined by Null) you can then append them on to your entire data set with new fields.

 

Then in your final output tool, we have checked the box 'Take File/Table Name from Field' and modified this based on the 'Car' Field. This creates a separate sheet for every different car manufacturer.

 

Let me know if this works for you!

 

Kind Regards

Will

Alteryx Certified Partner
Alteryx Certified Partner

Hi @BalajiMRB ,

 

you can split the first 3 row using the SAMPLE tool, create a list of cars using the UNIQUE tool, generate the header rows for each car type by using APPEND ROW (append cars to header generates headers for each car), then union the headers and the data and output by a car. See attached workflow.

 

Best regards

 

Roland

 

10-10-_2019_12-29-28.png

 

 

Meteoroid

Hi Davis,

 

Thanks for the response. Your solution provides two additional fields with required data (which is right in most of the ways), but my request is more focused on the format of the output. I want the first three rows in the output to be exactly same as the input. This is because the end users use these files as input into a different tool which processes these files and the format cannot be changed.

Alteryx
Alteryx

Hi @BalajiMRB 

 

In that case, the workflow that @RolandSchubert produced below should do the trick for you. You just need to make sure in your union tool you have checked the box at the bottom of the configuration to 'Set a Specific Output Order'. This will keep your headers all at the top.

 

Thanks

Will

Meteoroid

The one from @RolandSchubert did it. A big thanks to both @wdavis @RolandSchubert for your time. You folks rock. Cheers.

Labels