0

I have a CSV that was exported with double quotes around every header and value and I need them gone but without removing double quotes that might actually exist as values. For example:

"HEADER1","HEADER2","HEADER3","HEADER4","HEADER5"
"SOME_ID_0X0","SOME_ID_1X2","false","Some blob value with "double quotes" inside of it"
"SOME_ID_0X0","SOME_ID_1X2","false","Some blob value with "double quotes" inside of it"
"SOME_ID_0X0","SOME_ID_1X2","false","Some blob value with "double quotes" inside of it"

I can remove the first " from every line with the following

$ sed -i.bak 's/^"//g' $1

And I can remove all the ones in between with this:

$ sed -i.bak 's/","/,/g' $1

And finally I thought I could remove the last " in every line with this:

$ sed -i.bak 's/"$//g' $1

But it's not working. Could I accomplish this entire task in one line?

UPDATE I used this website to paste my data for hidden characters and here was the results

enter image description here

It appears some of the comment may be accurate but I don't know what that means I need to change still. Also is there a a clean way to check if the CSV has these quotes before attempting to remove them? Perhaps even just qualifying that the first character is a quote?

Improve this question
3
  • 5
    Perhaps " is not really the last character on each line (either because of trailing whitespace, or even because of DOS CRLF line endings)?
    – steeldriver
    Commented Dec 6, 2018 at 0:29
  • 1
    That actually works fine for me when I copy & paste your data into a file; I'll hazard a guess that your real data differs from what you showed us, and that there's either a space or some other non-printing character in the last position before the \n ..
    – tink
    Commented Dec 6, 2018 at 0:30
  • have you tried dos2unix ??
    – Siva
    Commented Dec 6, 2018 at 6:47

1 Answer 1

Reset to default
2

Use dos2unix to convert DOS to UNIX text file format

dos2unix $1

You could combine all 3 sed as:

 sed -i 's/^"//g;s/","/,/g;s/"$//g' $1

SOME_ID_0X0,SOME_ID_1X2,false,Some blob value with "double quotes" inside of it
SOME_ID_0X0,SOME_ID_1X2,false,Some blob value with "double quotes" inside of it
SOME_ID_0X0,SOME_ID_1X2,false,Some blob value with "double quotes" inside of it

Using AWK : 

awk -F ',' -v OFS=',' '{for (i=1;i<=NF;i++) sub(/^\"/,"",$i) sub (/\"$/,"",$i); print $0}' 1$

HEADER1,HEADER2,HEADER3,HEADER4,HEADER5
SOME_ID_0X0,SOME_ID_1X2,false,Some blob value with "double quotes" inside of it
SOME_ID_0X0,SOME_ID_1X2,false,Some blob value with "double quotes" inside of it
SOME_ID_0X0,SOME_ID_1X2,false,Some blob value with "double quotes" inside of it
  • sub(/^\"/,"",$i) removes the " at start of each field.
  • sub(/\"$/,"",$i) removes the " at end of each field.
Improve this answer
1
  • 1
    You can do "dos2unix" in sed: sed 's/\r$//' file, so removing trailing quote is sed 's/\r$//; s/"$//' -- should work even if file has unix format.
    – glenn jackman
    Commented Dec 6, 2018 at 20:20

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .