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?

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)? Dec 6 '18 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
    Dec 6 '18 at 0:30
  • have you tried dos2unix ??
    – Siva
    Dec 6 '18 at 6:47
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.
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. Dec 6 '18 at 20:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.