A field is missing a closing quote

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.

Datasets

Developer

Data Investigation

Accepted answers

All comments

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.

Sample_withIssue.csv

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.

0627-Shabaz.yxmd

Kurohits

Hi @Shaaz

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

Thanks

Rohit Gupta

A field is missing a closing quote.yxmd

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.

Test.csv

Kurohits

Hi @Shaaz

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

Thanks

Rohit Gupta

A field is missing a closing quote.yxmd

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703