2

I am trying to remove the first value from the 31st column in a csv file. The columns are in quotes and separated by commas like so.

"first_name","last_name","home_address"
"Frank",Russel","123 Hampton road"

the 31st column is the image column and images are separated by commas

"http://ddd.com/124dfr3f.jpg,http://ddd.com/hjasgx37trg.jpg"

The first image and tailing comma has to be removed from the 31st column

Added - Here is an example.

"one","two","three","four","five","six","seven","eight","nine","ten","eleven","twelve","three","four","five","six","seven","eight","nine","ten","twenty-one","twenty-two","three","four","five","six","seven","eight","nine","ten","thirty-one","thirty-two","three","four","five","six","seven","eight","nine","forty"
"one","two","three","four","five","six","seven","eight","nine","ten","eleven","twelve","three","four","five","six","seven","eight","nine","ten","twenty-one","twenty-two","three","four","five","six","seven","eight","nine","http://pictures.domain.com/e/josequervo/1183/eeeffcc7927sssssssssss5d26c0eb19e1dc53548x.jpg","http://pictures.domain.com/e/josequervo/0083/eeef8cc7927bf1245d26c0eb19e1dc53545x.jpg,http://pictures.domain.com/e/josequervo/0099/eeefscc7927bf1245d26c0eb19e1dc53546x.jpg,http://pictures.domain.com/e/josequervo/0012/eeefdcc7927bf1245d26c0eb19e1dc53547x.jpg,http://pictures.domain.com/e/josequervo/1183/eeeffcc7927bf1245d26c0eb19e1dc53548x.jpg,http://pictures.domain.com/e/josequervo/1234/eeefgcc7927bf1245d26c0eb19e1dc53549x.jpg,http://pictures.domain.com/e/josequervo/asee/eeefhcc7927bf1245d26c0eb19e1dc53540x.jpg,http://pictures.domain.com/e/josequervo/2341/eeefjcc7927bf1245d26c0eb19e1dc53544x.jpg","two",three","four","five","six","seven","eight","nine","forty"

This url and comma has to be removed

http://pictures.domain.com/e/josequervo/0083/eeef8cc7927bf1245d26c0eb19e1dc53545x.jpg,

I only posted the first 2 lines of the csv file but the file is 300 - 400 lines and this image has to be removed from line 2 to the last line. We can't alter the first line.

  • Does any other fields contain commas? – Kusalananda Jun 16 '18 at 9:21
  • 1
    Is example wrong? Should "Frank",Russel","123 Hampton road" read "Frank","Russel","123 Hampton road", i.e. a " before Russel ? – steve Jun 16 '18 at 11:38
  • Do you want to completely eliminate the 31st column, or replace it with an empty string to keep all the subsequent fields in the same ordinal positions? – Joe Jun 23 '18 at 5:06
2

Using GNU sed we may approach this as shown:

sed -e '
     s/","/&\n/30
     s/\n[^,]*,//
' file.csv

To be understood as:

  • Place a placeholder at the 30th occurrence of the "," string in the current line.

  • Then, traveling right from the placeholder all the way up to the first comma we delete everything along the way.

Other method is given as follows:

 perl -F'/","/' -lane 'my $i;
      print join q[","], map { $i++ == 30 ? substr($_, 1+index($_, ",")) : $_ } @F;
 ' input-file.csv

Method:

  • perl will read the csv file on a line by line basis, and for each line it shall split it around the string "," and store the split elements in the array @F.

  • Then for the 31st element, we remove everything up to the first comma.

  • This assumes (probably correctly) that the OP wants to completely delete the 31st field and not just its value. – Joe Jun 23 '18 at 5:09
  • @joe I just want to remove the first image url in the 31st column as it has around 6 images in this column. The first image is bad. Each image url is separated by a comma so the url and the trailing comma must go. – needtoknow Jun 25 '18 at 4:59
  • @needtoknow do the codes provided not generate the desired output? – Rakesh Sharma Jun 25 '18 at 15:49
  • @RakeshSharma They do not. Look at the bottom of OP and see the example. – needtoknow Jul 5 '18 at 1:29
  • @needtoknow They work. You need to look carefully and follow the code as provided. – Rakesh Sharma Jul 5 '18 at 7:15
0

As per your question understood image exsists in 31 st column seperated by comma and we need to remove first character from 31st column

awk -F "," '{$31=substr($31,2);print $0}' file.csv

Tested and its working fine Let us know for any confusion

  • cvs file is a profile dump of people. 31st column contains their images. The images in the 31st column are separated by commands. I need to remove the first image url and the comma after it on each line. – needtoknow Jun 16 '18 at 5:09
  • will update in some time – Praveen Kumar BS Jun 16 '18 at 5:18
  • You probably have to set OFS="," to get commas as delimiters in the output. – Kusalananda Jun 16 '18 at 9:42
  • Can you please post the sample input and expected output so we can help – Praveen Kumar BS Jun 16 '18 at 12:21
  • @PraveenKumarBS Please see original post. – needtoknow Jun 16 '18 at 14:29

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.