Delimit a cell to columns, and then to rows based upon carriage return

[mkav9893]

Hi All,

I am exporting release notes out of jira (Excel/CSV) to import into another system. The field format in jira is exported in the following format:

Key	Description	Affected Data Fields
Ticket-1	Ticket 1 was the release of three new fields	|FieldNames|Type|Description| |CustomerID1|int|unique guest identifier1| |CustomerID2|int|unique guest identifier2| |CustomerID3|int|unique guest identifier3|
Ticket-2	Ticket 2 was the release of four new fields	|FieldNames|Type|Description| |ScenarioA|string|Indicates the first scenario| |ScenarioB|string|Indicates the second scenario| |ScenarioC|string|Indicates the third scenario| |ScenarioD|string|Indicates the fourth scenario|

In order for the other system to consume the information, I need to delimit column #3 to columns, and then delimit the inner contents to rows while duplicating the key and description fields to have the following output (The header within Affected Data Fields also needs to be removed):

Key	Description	Affected Data Fields
Ticket-1	Ticket 1 was the release of three new fields	CustomerID1	int	unique guest identifier1
Ticket-1	Ticket 1 was the release of three new fields	CustomerID2	int	unique guest identifier2
Ticket-1	Ticket 1 was the release of three new fields	CustomerID3	int	unique guest identifier3
Ticket-2	Ticket 2 was the release of four new fields	ScenarioA	string	Indicates the first scenario
Ticket-2	Ticket 2 was the release of four new fields	ScenarioB	string	Indicates the second scenario
Ticket-2	Ticket 2 was the release of four new fields	ScenarioC	string	Indicates the third scenario
Ticket-2	Ticket 2 was the release of four new fields	ScenarioD	string	INdicates the fourth scenario

Can someone please advise on what the best approach to solving this would be?

[Jonathan-Sherman · Accepted solution]

Hi @mkav9893,

I think this is what you're trying to achieve? I've split the data to rows using the new row delimiter \n and skipped the first row for each scenario which contains the column headers, finally splitting the data to columns and removing the split field.

If this solves your issue please mark the answer as correct, if not let me know! I've attached my workflow for you to download if needed.

Regards,

Jonathan