-2

I have a file:

a|b|c|d
x|y|z|n|||||||||
p|q|r|s|
g|h|i|
w|e|r||

Now the 2nd row has an extra delimiter, I want to remove it. How could I do that in unix?

The output should only have 4 pipe delimiter:

a|b|c|d
x|y|z|n
p|q|r|s
g|h|i|
w|e|r|
  • 1
    use simple sed substitution... – RomanPerekhrest Dec 20 '17 at 17:46
  • How do I substitute , I think first I need to check which rows in the files files having extra pipe and have to substitute those line , how to do selective substitution ? – Kunal Dec 20 '17 at 17:52
  • 's/\|$//' should be all you need. – DopeGhoti Dec 20 '17 at 17:55
  • Sir I have updated the sample input and output file , apologies not mentioning it correctly at first attempt , please let me know how to handle this scenario – Kunal Dec 20 '17 at 18:01
  • @Kunal your last edit make your question unclear now, How | removed from this line p|q|r|s| but it doesn't in g|h|i|? – αғsнιη Dec 20 '17 at 18:50
2

I find awk easy to read in such situations:

$ awk -F'|' -vOFS='|' '{NF=4}1' file
a|b|c|d
x|y|z|n
p|q|r|s
g|h|i|
w|e|r|

Note that this answer doesn't remove trailing pipe symbols, it just sets the columns in the output to be 4. For the following modified example:

$ cat file
a|b
x|y|z|n||||
p|q|r|s
g|h|i|
w|e|r||

$ awk -F'|' -vOFS='|' '{NF=4}1' file
a|b||
x|y|z|n
p|q|r|s
g|h|i|
w|e|r|
  • Will it use if I have more than one extra pipe delimiter in the input flle. – Kunal Dec 20 '17 at 18:47
  • @Kunal no, this sets the number of fields per line. You'll get four columns no matter what. But in your example you only remove trailing pipes if they are in the fifth column... – user000001 Dec 20 '17 at 18:50
  • @Kunal: I added an example to demonstrate what happens, not sure if it is desirable. – user000001 Dec 20 '17 at 18:54
  • 1
    I think this is perfect , I just need to check by myself , The first row , it has only two pipe in put , have been made 4 in output. Perfect. Thank you. – Kunal Dec 20 '17 at 19:00
1

You can do this with a grep one-liner:

cat data.csv | grep -Po '^(\w*\|){3}\w*$'

You can also do this easily with Python. Here's a Python script that does what you want:

#!/usr/bin/env python2
# -*- coding: ascii -*-
"""shortencsv.py"""

import sys

cols = 4

with open(sys.argv[1]) as csvfile:
    for line in csvfile:
        print('|'.join(line.strip().split('|')[:cols]))

You would run it like this:

python shortencsv.py data.csv
  • I have tried using python , getting an error saying #!/usr/bin/env python2 doesn't exists. I was running my code with following command. ./delimiter_rm.py – Kunal Dec 20 '17 at 18:21
  • @Kunal Included the shebang line (#!/usr/bin/env python2) so that the solution would be self-documenting as far as the Python version number is concerned. If you run it the way I did (python shortencsv.py data.csv) then that line should be ignored. If you run it as an exectuable (./shortencsv.py data.csv) then you need to use the correct name for your local python interpreter. If you just change python2 to python then that should do it. – igal Dec 20 '17 at 18:38
  • Ok Thank you , Understood , Will give it a try again . – Kunal Dec 20 '17 at 18:46
1

With sed to replace one-or-more pipelines at the end of each record with single pipeple.

sed 's/|\+$/|/' infile

Sample:

a|b|c|d
x|y|z|n|||
p|q|r|s
g|h|i|sx|sxa
w|e|r||

Output:

a|b|c|d
x|y|z|n|
p|q|r|s
g|h|i|sx|sxa
w|e|r|
  • 1
    The no of pipe per row should be 4 . Not more or not less. – Kunal Dec 20 '17 at 18:58
  • 1
    OK, but your given output doesn't telling this, please edit your question and clarify this my comment as well. – αғsнιη Dec 20 '17 at 19:04
1

awk is good for more complicated tasks, but this simple task can be done much faster using cut:

$ echo "$a"
a|b|c|d
x|y|z|n|||||||||
p|q|r|s|
g|h|i|
w|e|r||

$ time awk -F'|' -vOFS='|' '{NF=4}1' <(for i in {1..100000};do echo "$a";done)

a|b|c|d
x|y|z|n
p|q|r|s
g|h|i|
w|e|r|

real    0m3.850s
user    0m2.105s
sys 0m1.481s

$ time cut -d'|' -f1-4 <(for i in {1..100000};do echo "$a";done)
a|b|c|d
x|y|z|n
p|q|r|s
g|h|i|
w|e|r|
.....
.....

real    0m2.844s
user    0m1.384s
sys 0m1.268s

In your case, just use

cut -d'|' -f1-4 inputfile
  • If you notice this comment by OP, he wants to get 4 columns even for input lines that have fewer columns, which cut doesn't do. – user000001 Dec 21 '17 at 6:49
  • But cut is indeed much faster than awk, your sample considerably downplays the difference, because it includes the bash loop in the test. On my pc, with this test time cat > file <(for i in {1..100000};do echo "$a";done), time awk -F'|' -vOFS='|' '{NF=4}1' < file >file2, time cut -d'|' -f1-4 < file >file2 I got real time 2.346, 0.816 and 0.102, so it is about eight times faster. Maybe I'll ask a new question why awk is so slow in this example. – user000001 Dec 21 '17 at 6:53
  • @user000001 My answer indeed does not comply with the last comment, but on the other hand i can not track any comment in posts... Questions not typed clearly receive corresponding non clear answers. Anyway, i think cut can not comply with the revised expectations. – George Vasiliou Dec 21 '17 at 7:50
  • @user000001 Your benchmark is the correct one. In my test i just kept the same loop in both awk and cut, and still cut is faster. Maybe because in this case we use a shotgun (awk) to fire a mosquito. – George Vasiliou Dec 21 '17 at 7:52

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.