Transpose CSV files

[melhaynesjr]

I was tasked with building a flow that grabs an csv file from one source, transpose some data and then ftp it to another server.

Lets say a csv file is defined as:

email,product,status

test@test.com,555111,Active

test@test.com,555112,Active

test@test.com,555113,Active

test@test.com,555114,Inactive

test2@test.com,999111,Active

What I want to do is convert it to this based on grouping by email

email,products,status

test@test.com,"555111,555112,555113,555114", "Active,Active,Active,Inactive"

test2@test.com,999111,Active

Is this possible? In the FTP source, I have sorting by Email and Grouping by Email. Does it matter if the csv file is not sorted to begin with? I keep reading that if the source is not sorted, the grouping doesnt work? Even if I have Celigo sort it?

In the mapping of the FTP source I do something like this

*.email -> subscriptions.email

*.product -> subscriptions.products[*].product

*.status -> subscriptions.products[*].status

The FTP destination mapping

$.subscriptions

$.subscriptions.email

$.subscriptions.products[*].product

$.subscriptions.products[*].status

To top things off, I dont have access to the FTP destination ( its set up in Celigo, but I dont have the creds on me ) so testing this is impossible. I may have to create my own ftp server just to try it out!

Im also ok with the answer being it cant be done or requires a lot of javascript. I just cant get the previews to work so I am assuming its just wrong.

[tylerlamparter]

@melhaynesjr this is possible. First off, you would need to use the grouping and the sorting feature on the FTP export. If you don't specify a sorting column, then results may not be as expected, see help text below:

Secondly, after you add the grouping and sorting, everything would look like this:

At that point, each record is a row of records, but we then need to make some transforms to manipulate it some.

Lastly, you would have the import step to the other ftp server and utilize mapper 2.0:

Here is the output:

[melhaynesjr]

@tylerlamparter

Thank you very much for this detailed response. I wish I had included screenshots, for some reason I didn't think I could, but I was actually kinda close. This is really great and will try it tonight and report back.

I really appreciate the time you took in this answer.