A field is missing a closing quote

[Shaaz]

Hi Team,

I've the data like below and I'm using below input tool configuration to read the data since it has a missing closing quote.

and then I'm using Text to columns to split into multiple columns basis (|) as delimiter and using Dynamic rename to get the column names.

Case1: If I use below config for Text to column tool, the data point (Row1 and Row2) are fine but Row 3 is giving incorrect values

Case2: if I use below config for Text to Columns, the data point (Row1 and Row4) are fine but Row 3 is giving incorrect values

Can you please suggest how we can handle both the cases together since the dataset is one.

Below is the expected output.

[messi007]

@Shaaz,

Could you share a sample data ? It will help us to give you the solution.

Regards,

[Shaaz]

Hi, Please find the attached sample file.

[Qiu]

@Shaaz 

Good finding. 😁
I eventually made a rather messy flow.
I replaced the quote before or after the | with special characters which are believed not to appear.

[grazitti_sapna]

@Shaaz, if your use case is limited to the sample data you have shared then try this solution if it fits your use case. 

PS: This is one of the solutions.

I hope this helps!

Thanks!

[Kurohits]

Hi @Shaaz 

Another easy method of doing this.  Attached workflow for your reference. 

Thanks 

Rohit Gupta

[Qiu]

@Kurohits 
I think the requestor still wants to keep the quote characters. 😁

[Kurohits]

@Qiu ohh yes, Didn't check his expected output screen shot. Thanks for the correction. 

Please use @Qiu solution.

Thanks 

Rohit Gupta

[Shaaz]

This solution seems ok but it isn't working when I switched delimiter from pipe to comma and also, I've added Amount column as below where I've values in brackets with a trialing space.

Here in row2 (10,000) means -10,000

Can you please help me overcome this.

[Kurohits]

Hi @Shaaz 

I have regex function to match groups. You can check out this solution. I hope this will work. 

Thanks

Rohit Gupta