2

I have a csv file which is supposed to contain 4 columns of data which include a product number, a title, a url and a price. Each column is separated by a | delimiter (this has to be maintained, there are other reasons why I can't switch to an alternative delimiter which I won't go into here). As can be seen in the bottom entry (which is the problem entry in this example) the title contains a pipe, which breaks the pattern, which could potentially causes issues if the data needs to be imported into a database.

5456435121|The making of the blue album|https://www.example1.co.uk|55
1321354567|Wow this example has no imagination|https://www.cherrypickers.co.uk|89
5456456456|King of the Barbarians | Last Man Standing|https://www.babarians.co.uk|79

What I would like to know is, how can I run a command which could effectively analyse the file, and for every line where there are more than 3 pipes( i.e. every line where the title contains a pipe) then delete the 2nd one in that line. This would effectively allow me to remove the pipe(s) in the title if there is one or more present. I don't know how to achieve it.

I would like the file to look like this once processed:

5456435121|The making of the blue album|https://www.example1.co.uk|55
1321354567|Wow this example has no imagination|https://www.cherrypickers.co.uk|89
5456456456|King of the Barbarians Last Man Standing|https://www.babarians.co.uk|79
4
  • please show sample output
    – Rahul
    Commented May 5, 2016 at 11:24
  • updated with output data.
    – neilH
    Commented May 5, 2016 at 11:39
  • FYI, you can get a list of lines with more than 4 fields with awk -F'|' 'NF > 4' filename.csv. you can use this list to help you correct all such errors.
    – cas
    Commented May 6, 2016 at 4:11
  • and BTW, sophisticated parsers for CSV files (actually, DSV since you're not using a comma) can cope with embedded delimiters by requiring double-quotes around either all fields or just those fields which have the delimiter in the field data. see en.wikipedia.org/wiki/Delimiter-separated_values
    – cas
    Commented May 6, 2016 at 4:12

1 Answer 1

2
sed -r ':;s/(\|.*)\|(.*\|.*\|)/\1\2/;t'

Deletes every pipe that is preceeded by one and followed by two pipes.

If you want to edit the file in place use -i option.

1
  • Nice one this has worked- better than I expected actually. I tried also putting 2 pipes into the title to see how it dealt with that and it also worked as I would have hoped. Thanks man.
    – neilH
    Commented May 5, 2016 at 14:04

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .