0

I am trying to grep column 7th with pipe delimiter (|) data in that I have to removed pipe (|) from middle of text.

Sample file:

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

Problem in column no.7 : In that I have to removed | from middle of text. value7|text

Required output for column 7 is: value7 text

Now I am trying to write awk command which goes to column 7,replace (|) pipe with blank so I can treat it as single string to make my process work.

Final output should be like this:

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

please suggest.

Thanks,

  • Please add the code you have used so far. – nohillside Apr 18 at 12:58
  • What defines the column to "fix"? Is it always column 7? Or is it any column that matches a pipe within double quotes "? – Sparhawk Apr 18 at 12:58
  • @Sparhawk- We have used nawk command to fix that problem,and related to column position it can be any column position not column 7th. I just gave the example. – user347849 Apr 18 at 13:04
  • This is a beautiful example for an XY - problem. You say you want to do it "to make my process work", so you should ask for a solution to make your process work instead and not about how to implement a workaround that you think is suitable to make your process work. – pLumo Apr 18 at 13:25
  • Is it possible to solve this issue with awk,or sed command. – user347849 Apr 18 at 13:28
0

Tested with below awk command and it worked fine

awk -F '"' '{gsub(/\|/," ",$2);print $0}' filename

output

col1|col2|col3|col4|col5|col6|col7|col8|col9|col10
value1|value2|value3|value4|value5|value6| value7 text |value8|value9|value10
3

Since the data appears to be properly formatted CSV data, we can use a CSV parser:

$ csvformat -d '|' file | tr '|' ' ' | csvformat -D '|'
col1|col2|col3|col4|col5|col6|col7|col8|col9|col10
value1|value2|value3|value4|value5|value6|value7 text|value8|value9|value10

This uses csvformat from csvkit to reformat the |-delimited data into comma-delimited data. tr is then used to replace all remaining pipe symbols with spaces. At the end, csvformat is again used to reformat the comma-delimited data into |-delimited format.

Note that csvformat will only add double quotes to fields if needed, or if -U 1 is used (in which case it will double quote all fields).

0

Try this,

sed 's/\(".*\)|\(.*"\)/\1 \2/g' file

Disclaimer: I would not recommend doing this. Rather fix your process to make it work properly instead of introducing an ugly workaround like this. Your data is normal CSV (or "PSV") which can be read easily by any proper CSV parser.

This will have issues, if you have a field e.g. like this: value\"6|"value7|test".

  • I have tried sed command but its showing "sed: command garbled:". – user347849 Apr 18 at 13:46
  • maybe your sed doesn't know extended regex... what about the updated command? – pLumo Apr 18 at 13:51
  • will test the updated sed command . – user347849 Apr 18 at 16:11
  • Thanks, its working fine. – user347849 Apr 22 at 14:40

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.