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

[BalajiMRB]

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 sold	Cost per unit	City
VW	2	500000	Newyork
VW	4	600000	Seattle
MG	6	1000000	Chicago
MG	1	200000	Seattle

[wdavis · Accepted solution]

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

[RolandSchubert · Accepted solution]

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

[BalajiMRB]

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.

[wdavis]

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

[BalajiMRB]

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

[Jwwwson]

Hi @RolandSchubert , 

Could you kindly advise how to output the data into different excel files, using your workflow?

Cheers, 

Jason

[RolandSchubert]

Hi @Jwwwson,

I added a Formula tool to the workflow to create a new field containing the filename including path, and changed the setting in the Output Data" tool to "changed entire file path". Basically you can build it as flexible as you need it (different directories, filenames, sheetnames).

Let me know, if it works for you.

Best,

Roland

[Rahul_Analyst]

Hi,

Excellent! I was also able to do it perfectly. However, I want a solution if I do not want last column where (Car names are mentioned as per the above example) in my all the output files. How can I do this ?

Field1	Field2	Field3	Field4	Group
Data base name: xyz				MG
Sales Data				MG
Car	Quantity sold	Cost per unit	City	MG
MG	6	1000000	Chicago	MG
MG	1	200000	Seattle	MG