Python script to remove double quotes surrounding columns in a pipe delimited txt file-CodePudding

I have a few pipe delimited txt files. All the columns in each of those files are surrounded by double quotes. I want to remove those double quotes. The catch is one of the columns contain json data format so I dont want to remove double quotes in the json column. To give an example below are few lines of the txt file :

"Id"|"Name"|"KV_Pair"
"I03"|"Adam Smith"|"[{"objectives" : [  ]}]"
"I02"|"Jane Doe"|"[{"statementType":"Note","statementLabel":"Derm_Leave_Behinds_Disclaimer"}, {"statementType":"Note","sortOrder":2}]"
"I07"|"John Perry"|"[{"statementId":"SI098","statementType":"Note","sortOrder":1},{"statementId":"SI098","statementType":"Note1","sortOrder":2}]"

The final data should be :

Id|Name|KV_Pair
I03|Adam Smith|[{"objectives" : [  ]}]
I02|Jane Doe|[{"statementType":"Note","statementLabel":"Derm_Leave_Behinds_Disclaimer"}, {"statementType":"Note","sortOrder":2}]
I07|John Perry|[{"statementId":"SI098","statementType":"Note","sortOrder":1},{"statementId":"SI098","statementType":"Note1","sortOrder":2}]

I can use Python and powershell as two options to write script. I have been trying to write python script using the regex module but haven't quote figured out how to not remove the double quotes inside the json data. Any help would be appreciated.

CodePudding user response：

You can try to read the file line-by-line, split the line by | and then strip " from each value. Then print the values (to stdout, you can redirect the stdout to another file afterwards):

with open("your_file.txt", "r") as f_in:
    for line in map(str.strip, f_in):
        if line == "":
            continue
        row = [v.strip('"') for v in line.split("|")]
        print(*row, sep="|")

Prints:

Id|Name|KV_Pair
I03|Adam Smith|[{"objectives" : [  ]}]
I02|Jane Doe|[{"statementType":"Note","statementLabel":"Derm_Leave_Behinds_Disclaimer"}, {"statementType":"Note","sortOrder":2}]
I07|John Perry|[{"statementId":"SI098","statementType":"Note","sortOrder":1},{"statementId":"SI098","statementType":"Note1","sortOrder":2}]