Remove double quotes from Data

Question

I am getting data in csv file with double quotes around string columns but while reading csv file using U-sql i am getting errors because of double quotes in data as well.

I am thinking of replacing double quotes which is in data at first step then read that file but not sure how to do that as we have double quotes everywhere.

Any suggestions would be appreciated or if someone can help me giving the powershell or .net code to do the same that would be great help as I am not good in .net or powershell.

Sample Data

“Name”;”Department” “Abc”;”Education”Teaching” “Cde”;”Test”Another”

It should be

“Name”;”Department” “Abc”;”EducationTeaching” “Cde”;”TestAnother”

Its txt extractor and sample data is in question. Data doesn’t contain only letters it can have numbers and special char as well — Raj, Oct 26 '18 at 20:51
Have you tried to sat parameter quoting: true? https://learn.microsoft.com/en-us/u-sql/functions/operators/extractors/extractor-parameters#quoting — Jorge Ribeiro, Oct 29 '18 at 09:36
Yes in this case string should be wrapped in "AA" but if there is a " in data the U-SQL get confused. — Raj, Oct 30 '18 at 17:47

score 0 · Answer 1 · answered Oct 16 '18 at 18:32

You can use a regex find/replace in Visual Studio Code. For example (and assuming that the data only contains letters, you can edit the regex as needed):

Find regex: "([a-zA-Z]+)"([a-zA-Z]+)"
Replace string: "$1$2"
Input string: "Name";"Department" "Abc";"Education"Teaching" "Cde";"Test"Another"
Output string: "Name";"Department" "Abc";"EducationTeaching" "Cde";"TestAnother"

score 0 · Answer 2 · answered Oct 16 '18 at 18:54

So it seems that your quotes are not the standard [Char]34. Instead they are [Char]8220; [Char]8221

So we need to do a replace in powershell

$TEST = @"
“Name”;”Department” “Abc”;”Education”Teaching” “Cde”;”Test”Another”
"@

$TEST | %{ 
    $_ = $_ -replace [char]8220, '"'
    $_ = $_ -replace [char]8221, '"'
    $_ -replace '"([a-zA-Z]+)"([a-zA-Z]+)"','"$2 $1"'
}

this would make the output :

"Name";"Department" "Abc";"Teaching Education" "Cde";"Another Test"

score 0 · Answer 3 · answered Oct 25 '18 at 19:04

You could also do this in a custom row processor. Have the initial data read the CSV file into a variable as a single column row (raw data). Then pass each row through a row processor to parse the data and remove the offending characters. I've done something similar for handling Fixed Width text files.

Remove double quotes from Data

3 Answers3