Can't get rid of double quotation showing in output of split expression

[JTischler]

at a high level I have a CSV file that when file is created in SP teh flow looks at the file and compares to a list on SP and only adds new rows with the specified fields populated.

I am adding an additional field, but for some reason the flow is adding in a backslash and a single double quote that I can't get rid of.

This field contains instructor names.  I think that because there are multiple instructors separated by a comma this is impacting the way the column is read:

Picture of input:

Raw Input:

"�Username,Email,Course Unique ID,Course code,Course Name,Session Name,Session Unique ID,Session Start Date,Session End Date,Session instructor full name\r\n205875,xxxxxx@xxxxxxx.com,I-1LG480,GL00059,Cultural Discussion: Leadership Panel  - World Day for Cultural Diversity,Cultural Discussion: Leadership Panel,B7YN3RV,2023-05-23 11:30:00,2023-05-23 12:30:00,\"Janei Finney, Jessica Barnett, Lorena Castillo\"\r\n\n"

In the above, the final field is the only one that has the quotations.

In the output of the select, this is where the data changes and I am not sure how to correct.  The Instructor name would ideally contain all 3 names, why is there only one name with the double quote?:

 Here is this portion of the flow:

Do I need to change the comma to a backslash in the last split expression?

[ManishSolanki]

Hi @JTischler 

If we are sure that 'Instructor Name' will always be the last string in each csv row, then the below expression will give all the instructors name as comma separated string, use this expression for 'Instructor Name' in 'Select' action:

join(take(skip(split(item(),','),9),sub(length(split(item(),',')),1)),',')

here, the expression will take all the elements after index 9 and join them to return a comma separated string. 

If this helps & solves your problem, please remember to give a 👍 and accept my solution as it will help others in the future.

Thanks

[JTischler]

@ManishSolanki  Thank you, this did exactly as you stated but it is still showing the double quotes.  IS there a way to have those not show?

This is Sharepoint:

[ManishSolanki]

Hi @JTischler 

To get rid of double quotes, please try with the below expression:

replace(join(take(skip(split(item(),','),9),sub(length(split(item(),',')),1)),','),'"','')

If this helps & resolve the issue, please remember to give a 👍 and accept my solution as it will help others in the future.

Thanks

[JTischler]

@ManishSolanki  That's coming back as an invalid expression

[ManishSolanki · Accepted solution]

@JTischler , pls check if you have put double quotes in the expression:

replace(join(take(skip(split(item(),','),9),sub(length(split(item(),',')),1)),','),'"','')

If this solves your problem, please remember to give a 👍 and accept my solution as it will help others in the future.

Thanks

[JTischler]

@ManishSolanki  Thank you!!!  Even though I was copy/pasting, for some reason the close parentheses was omitted.  This worked perfect!

[PaulCS]

What if Instructor Name is not the last value? 

i.e.

field0,field 1,field2,field super 3,"this has a,comma in it",field5

how do I get field[4] (note - I don't know in advance which columns might have double quotes with a comma inside it - the data in certain columns varies. One line might have no double quotes. Another line might have double quotes on several fields because several fields have an embedded comma. So sometimes the line has no double quotes and sometimes it might have one or two or more fields with an embedded comma and double quotes.)

[ManishSolanki]

Hi @PaulCS 

Are you getting double quotes in csv file? Are you also getting output as field0,field 1,field2,field super 3,"\"this has a,comma in it"\",field5 when looking at run instance of flow?

Pls share the sample string.

[PaulCS]

Maybe I'm missing something. When I read through the answer, it seems to need field 9 to be the last field in the row. And it works because any fake fields after that can be included in field 9.

So the example at the top has extra commas in field 9 that also happens to be the last field. 

1LG480,GL00059,Cultural Discussion: Leadership Panel  - World Day for Cultural Diversity,Cultural Discussion: Leadership Panel,B7YN3RV,2023-05-23 11:30:00,2023-05-23 12:30:00,\"Janei Finney, Jessica Barnett, Lorena Castillo\"

What if one of the rows of data looked like this with an extra comma in field 3?

1LG480,GL00059,\"Cultural Discussion: Leadership Panel,Thought Panel  - World Day for Cultural Diversity\",

Cultural Discussion: Leadership Panel,B7YN3RV,2023-05-23 11:30:00,2023-05-23 12:30:00,\"Janei Finney, Jessica Barnett, Lorena Castillo\"

My data comes back from a webservice API query and looks like this: 

Caption1,Caption2,Caption3,Caption4,Caption5,Caption6

field0,87,field2,field super 3,"this has a,comma in it",field5

field0,94,field2,field super 3,this does not,field5

field0,45,field2,"field super wacky,3",this has no comma,field5

[PaulCS]

Note: I did figure out an answer. I just happen to hate it. It uses about 10 composes where I replace various string combinations with other combinations and send a nodejs packet to an azure function that runs nodejs for me and sends back the return value. 

It's yucky, but it works. I'm going to try to clean it up later this week, but I have to finish making the software work using the output tonight.

[Wendy_Lumsden]

Hi Paul,

Did you ever find a simple method?  I am having a similar problem where I need to use concat on strings which have double quotations for an xml query and when I use concat, this expression adds a blackslash before the double quotations.
Have tried using replace around the concat to remove the backslashes, this did not work.
Any assistance greatly appreciated.
Wendy

[johnjohnPter]

@Wendy_Lumsden same issue here...