CSV transpose Based on Column Criteria

Hi,

I am using Aspose. cells I am looking for Transposing the data from vertical to horizontal based on the following criteria.

Input CSV:

Column1,column2,column3,colum4,column5,column6
1,2,1,XXX,name1, value1
1,2,1,XXX,name2, value2
1,2,1,XXX,name3, value3
1,4,5,YYY,name1,value1
1,4,5,YYY,name2,value2
1,4,5,YYY,name3,value3
1,4,5,YYY,name4,value4
2,3,4,ZZZ,name1,value1
2,3,4,ZZZ,name4,value4

Output CSV:

Column1,column2,column3,Name1,name2,name3,name4
1,2,1,XXX,value1,value2,value3,
1,4,5,YYY,value1,value2,value3,Value4
2,3,4,ZZZ,value1,Value4

In this case, column 1, colum2, column3 are repeating. The number of meta columns needs to be passed as a parameter need to be dynamic as input to the code. It can be 6 columns always the last two columns are transposed to Vertical and other repeating meta-information is removed and added as one entry

Example: Please look at the image below on what needs to be accomplished

image.png (14.1 KB)

Any direction or help would be appreciated!

@abhinav4593,

I do not think there is any better way or automatic way to accomplish your task either in MS Excel or by Aspose.Cells APIs. If you know any good way other than manual way to accomplish the task in MS Excel manually, let us know with all the details and steps involved, we will check it on how to do it via Aspose.Cells APIs. I think you have to write your own code and logic (a kind of manual approach) to do the sorting of data and re-paste data as per your specific requirements by yourselves. You may use Subtotal feature but again it won’t display the data in same row as it has its own style and formatting.

Hi Amjad!

Thanks for the inputs. Here is my logic on the conversion Let me know if this is feasible.

1.Make a Hashmap of the the repeating columns (Which will be an Key based on the Primary Column)
Hashmap1 = {XXX:[1,2,1],YYY:[1,4,5],ZZZ:[2,3,4]} Or we can use the similar nested method that was used below

2.Make second Hashmap (Nested) as follows
Hashmap2 = {XXX:{name1:value1,name2:value2},YYY:{name1:value1,name2:value2}}

3.Make a set with data from Column 5
set1 = {name1,name2,name3,name4}

// Build the output Excel (Preferred) / CSV file

  1. Generate the header, the columns to the left of the primary Key Column, Primary key column, followed by entries in the set1 as the column headers
  2. Iterate through Hashmap Hashmap1 and fill up the columns from the value into the left of the primary columns.
  3. Fill up the primary column from the key of the iterating hashmap.
  4. Extract the sub-Hash Map from Hashmap2 and fill up rest of the row based on a lookup of the sub-HashMap with the Column header as the key and if no value found leave it empty and move to next column Header.
  5. Output the excel after the range is filled up.

Let me know what you think and if this is possible using aspose cells or we have to stick to basic java coding. I am thinking we can leavrage a combination of Aposecells and Java.

Any feedback would be appreciated!

@abhinav4593,

Your logic seems ok, so you have to use your own Java code to sort/place the data accordingly before rendering to final CSV file format (by Aspose.Cells).