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 |
Solved! Go to 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
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
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.
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
The one from @RolandSchubert did it. A big thanks to both @wdavis @RolandSchubert for your time. You folks rock. Cheers.
Hi @RolandSchubert ,
Could you kindly advise how to output the data into different excel files, using your workflow?
Cheers,
Jason
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
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 |