I have csv file that is auto generated by a script but for some of the records (line items) received I need to search column 2 and if the values contains "*.app" I need to print "INVALID" into column 2 for all records matching and shift cells to the right.

Example Data File:

   www,test,biz.app,bob,6-1-18,09:33,OK   //Example of good line    
   www,biz.app,tony,7-11-17,06:22,ok      //Example of bad line

   Wanted output:
   www,INVALID,biz.app,tony,7-11-17,06:22,ok   //Example of fixed line

I have unsuccessfully tried awk, sed, and if statement but not getting the results I need


    if [ awk -F',' '{ print $2 } < FILE' ] == "*.app" ; then ; echo "INVALID"; fi

Which is obviously terrible... New to bash thanks all!

awk -F, -vOFS=, '$2 ~ /\.app$/ { for (i = NF + 1; i > 2; --i) $i = $(i-1); $2 = "INVALID" } 1' file >newfile

This would create newfile from file. The awk command sets both the input and output field delimiter to a comma, then tests the value of column two against a regular expression which matches the string .app at the end of the value. If the test succeeds, the record's fields are shifted right one step to make place for the string INVALID as the new second field.

The trailing 1 could be replaced by { print } (it causes every line to be outputted).

With the given example data, the output file would contain

  • Thanks so much Kusalananda, this works great! Can you tell me how to modify this command so that when the value ".app" is found in column 2 it does not shift the cells right but rather replaces .app with INVALID? – SSDdude Jun 26 '18 at 16:15
  • @SSDdude Just remove the for loop: { $2 = "INVALID" }. Keep the rest as is. – Kusalananda Jun 26 '18 at 16:16
  • Sorry I don't fully understand... if I remove { $2 = "INVALID" } how will the code replace .app with INVALID? – SSDdude Jun 26 '18 at 16:20
  • @SSDdude No, that's what you are left with if you remove the for loop. The complete command: awk -F, -vOFS=, '$2 ~ /\.app$/ { $2 = "INVALID } 1' – Kusalananda Jun 26 '18 at 17:26

Awk approach:

awk 'BEGIN{ FS = OFS = "," }NR > 1 && $2 ~ /.*\.app/{ $2 = "INVALID" OFS $2 }1' file
  • NR > 1 && $2 ~ /.*\.app/ - if record number is greater than 1 (all but first) and the 2nd field $2 matchs the pattern /.*\.app/

The output:

  • Hey Roman thanks for the answer but this doesn't shift biz.app to the right it replaces the value with INVALD but does not align the other columns. Any idea how I can do this? Thanks! – SSDdude Jun 25 '18 at 20:08
  • @SSDdude, see my update – RomanPerekhrest Jun 25 '18 at 20:10
  • Hey Roman thanks again... this works great for everything but the first record (new test had bad line as first line).. I tried modifying your awk statement but couldn't figure out how to NOT IGNORE the first records if the second cell contains .app... Thoughts? THANKS! – SSDdude Jun 25 '18 at 20:26
  • @SSDdude, your new conditions seem to be unclear. My approach provides the current expected output. Extend your question with full input fragment and respective output. – RomanPerekhrest Jun 26 '18 at 5:13

Using the GNU sed tool we can approach this problem as follows:

 sed -e '
 '  file.csv

To be read as:

 ° Only for lines that are not the first, meaning, skip the header from being considered for processing, whilst for the others, place a marker at the second occurrence of the comma.
 ° Any line that has the 2nd field terminating in a .app append the string INVALID after the first field.
 ° Now take away the marker.
 ° N. B.  Lines whose 2nd field doesn't comprise *.app  are passed on unmodified.

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.