Transform raw data in 1 column into multiple columns - Challenge

[marcel_olmo]

HI guys,

I receive a csv every day with a lot of info given only in 1 column. Here's a dummy example of what I mean :

The point is that I only have the name of the header of my desired new column "Title", "Project Id" and "Amount" but I don't know how to transform the data into the new desired columns.

Here's an example of my desired Result :

I've attached an excel of a dummy example of the Input and my desired result. Maybe there's someone who knows the trick about how to do it :D.

Best regards, Marcel.

[Mark_Winter · Accepted solution]

Hi Marcel,

Sometimes it's just the intrigue that gets me... 😤 

So here's a method I went for, though I'd say now it's neither my most elegant nor robust. In particular, this isn't built to handle multiple sets of items in the input, that would need turning into an iterative process above and beyond this.

Please also don't judge the data content 😺 I took a short-handed working example instead, inspired at the time by my streaming-service-of-choice's "Now playing" 🙌

[marcel_olmo]

Thanks  @Mark_Winter I've just achieved the desired result based on your script, it was very useful.

Best regards, Marcel. 

[Mark_Winter]

Hi Marcel,

That's great to hear 😁 I am glad to hear it helped give you what you needed ✌️

Mark