0

I am trying to grep column 4 with pipe delimiter (|) and replace all double quotes within it and then enclose that string with double quotes again.

sample File:

col1|col2|col3|col4|col5|col6|col7|col8|col9
value1|value2|value3|"|||||value"4|value5|value6|"||value"7|value8|value9

Problem is column 4 is where user can write anything, even a pipe, which is my delimiter and it breaks my process.

E.g. column 4 may be

"|||||value"4

Now I am trying to write command which goes to column 4, replace all double quotes then enclose it with double quote again so I can treat it as single string to make my process work.

Required Output for column 4 is

"|||||value4"

Similarly I have do the same for column 7, required Output for column 7 is

"||value7"

Final output should look like this:

col1|col2|col3|col4|col5|col6|col7|col8|col9
value1|value2|value3|"|||||value4"|value5|value6|"||value7"|value8|value9

please suggest.

  • What operating system are you using? Please mention that in the question instead of using tags for it (OS tags should be used when the question is about the OS, not when you're simply using that OS). And you have tagged with both Linux and Solaris, two very different systems with different versions of the standard tools. – terdon Mar 24 '18 at 15:23
  • In addition to what terdon said, you have tagged the question with both awk and sed, but thu only utility mentioned in the question itself is grep. – Kusalananda Mar 24 '18 at 15:26
  • I have to implement it on both Linux and Solaris OS using awk cmd. – jack swan Mar 24 '18 at 16:05
  • For properly formatted CSV, you should double any existing double quotes, then enclose in double quotes: test|test_f|21/03/2017|"""|||||USER ""RIGHTa anything here"|123 – glenn jackman Mar 25 '18 at 3:02
  • And you should be fixing the process that creates the file in the first place, instead of repairing the damage. – glenn jackman Mar 25 '18 at 3:03
1

With sed assuming you only have 5 columns and the column which should be quoted is column4 in your case:

sed -E 's/"//g; s/^(([^|]*\|){3})(.*)(\|[^|]*)$/\1"\3"\4/' infile

This will work even if no quotes entered by user, if entered any quotes by user or pipes.

Here is an awk solution too as requested:

$ awk -F'|' '{ for (i=1; i<NF; i++){if (i>3)gsub("\"",""); 
  printf (i!=(NF-1))?$i"|"((i==3)?"\"":""):$i"\"|"}; print $NF}' infile

col1|col2|col3|"col4"|col5
test|test_f|21/03/2017|"|||||USER RIGHTa anything here"|123
  • -Thanks this works like a charm. Ran into another problem where I have 2 faulty columns and count from end of the line fails(NF-1). – jack swan Mar 25 '18 at 14:47
  • column4 is your column(NF-1) already! – αғsнιη Mar 25 '18 at 14:52
  • @αғsнιη-Thanks this works like a charm. Ran into another problem where I have 2 faulty columns and count from end of the line fails(NF-1). col1|col2|col3|col4|col5|col6|col7|col8|col9 test|test_f|21/03/2017|"|||||USER "RIGHTa anything "here|123|erer|""||User Comments|test|test23 Here is problem with col4 : "|||||USER "RIGHTa anything "here and col7 : ""||User Comments – jack swan Mar 25 '18 at 14:54
  • still you didn't edit into your question with these, since I cannot recognize where is the next line starts. – αғsнιη Mar 26 '18 at 9:44
  • I have edited my question, thanks for looking into it. – jack swan Mar 26 '18 at 11:32
2

Using csvkit:

$ csvformat -d '|' -D '|' file.csv
col1|col2|col3|col4|col5
test|test_f|21/03/2017|"|||||USER RIGHTa anything here"|123

The -d and -D flags define the delimiter used in the input and output respectively.

  • hmm, still I cannot recognize how double quote moved out of string! – αғsнιη Mar 24 '18 at 14:52
  • @αғsнιη Double quotes are the default quoting characters for CSV files. The whole field does not need to be quoted, but the delimiters that are part of the data needs to be. CSVkit's csvformat is able to read the partially quoted field and outputs it fully quoted. One may also choose to escape the delimiters in the field with -U 3 -P '\' (for backslash escapes, -U 3 means "no quotes"). – Kusalananda Mar 24 '18 at 14:59
  • @αғsнιη If the user was using a CSV-aware parser in his workflow, this transformation would not be needed. – Kusalananda Mar 24 '18 at 15:00
  • OK, thanks, now in OP's question, user fortunately entered two double quotes "|||||USER "RIGHTa anything here, what if user only input |||||USER RIGHTa anything here or "|||||"USER "RIGHTa anything here or some varied string? does this tool csvformat can print the same and result with "|||||USER RIGHTa anything here"? Where OP is asked to remove quotes and quote whole column 4 again. – αғsнιη Mar 24 '18 at 15:29
  • 1
    @αғsнιη It will work as long as the delimiter characters in the data field are quoted with double quotes and as long as the double quotes are paired up properly. – Kusalananda Mar 24 '18 at 15:44

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.