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.

3
  • Does any other fields contain commas?
    – Kusalananda
    Jun 16, 2018 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, 2018 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, 2018 at 5:06

2 Answers 2

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.

7
  • This assumes (probably correctly) that the OP wants to completely delete the 31st field and not just its value.
    – Joe
    Jun 23, 2018 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. Jun 25, 2018 at 4:59
  • @needtoknow do the codes provided not generate the desired output? Jun 25, 2018 at 15:49
  • @RakeshSharma They do not. Look at the bottom of OP and see the example. Jul 5, 2018 at 1:29
  • @needtoknow They work. You need to look carefully and follow the code as provided. Jul 5, 2018 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

6
  • 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. Jun 16, 2018 at 5:09
  • will update in some time Jun 16, 2018 at 5:18
  • You probably have to set OFS="," to get commas as delimiters in the output.
    – Kusalananda
    Jun 16, 2018 at 9:42
  • Can you please post the sample input and expected output so we can help Jun 16, 2018 at 12:21
  • @PraveenKumarBS Please see original post. Jun 16, 2018 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.