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?

  • 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 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 ?? – msp9011 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
    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 Dec 6 '18 at 20:20
0

Running 'sed -i.bak' 3 times will overwrite the backup file each time, so at the end you won't have the original file in your backup file. You should use '-i.bak' once and '-i' the rest of the times.

sed -i.bak -e 's/^"//' -e 's/","/,/g' -e 's/"$//' a.txt

Use -e <expression> to use multiple sed expressions.

You don't need the 'g' for the first and last lines, because you're only doing one replacement.

I don't know why the last one isn't working for you, because it's correct as written. Either a typo on your end or maybe an issue with an environment variable or locale.

Another way to do it is search for

  1. a quote
  2. text that isn't a quote
  3. a quote
  4. either a comma or end-of-line

and keep and restore the 2nd and 4th parts. Like this:

sed 's/"\([^"]*\)"\([,$]\)/\1\2/g' a.txt

The \( and \) store the 2nd and 4th parts, and they're restored in the replace portion.

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.