CODEWITHSUNDEEP
bashawksed
moore1emu
moore1emu

Reputation: 496

Remove part of a column, if its in a specific Column number. (The column has a variable)

I have a csv with lines like this:

Last,First,A00XXXXXX,1492-01-10,2015-06-17,,Sentence Skills 104,,Elementary Algebra 38,
Last,First,A00XXXXXX,1492-01-10,2015-06-17,,,,Elementary Algebra 101,College Level Math 56
Last,First,A00XXXXXX,1492-01-10,2015-06-17,Reading Comprehension 102,,,,
Last,First,A00XXXXXX,1492-01-10,2015-06-17,,,,Elementary Algebra 118,College Level Math 97

I want to remove the word "Reading Comprehension" but leave the number, but only if its in column 6, if its in any other column, leave it alone.

Once again the variable is stumping me, I know how to remove it from the specific column if I know the number, but not remove the word and leave the number when I don't know the number

awk -v old="Reading Comprehension 102" -v new="" -v col=6 '$col==old{$col=new} 1' FS="," OFS="," mergedfile.csv > testmerg.csv

Thank you for the help,

Upvotes: 2

Views: 47

Answers (3)

josifoski
josifoski

Reputation: 1726

Let give sed a chance (althought is not it's domain) 

echo "Last,First,A00XXXXXX,1492-01-10,2015-06-17,Reading Comprehension 102,,,," |  
sed -r 's/(([^,]*,){5})Reading Comprehension /\1/'

Last,First,A00XXXXXX,1492-01-10,2015-06-17,102,,,,

or by Ed Morton's suggestion for using variables 

old="Reading Comprehension"  
new=""  
col=6  
sed -r 's/(([^,]*,){'"$((col-1))"'})'"$old"' /\1'"$new"'/'

Upvotes: 2

Ed Morton
Ed Morton

Reputation: 204015

Re-using your awk variable definitions:

$ awk -v old="Reading Comprehension " -v new="" -v col=6 'BEGIN{FS=OFS=","} {sub(old,new,$col)} 1' file
Last,First,A00XXXXXX,1492-01-10,2015-06-17,,Sentence Skills 104,,Elementary Algebra 38,
Last,First,A00XXXXXX,1492-01-10,2015-06-17,,,,Elementary Algebra 101,College Level Math 56
Last,First,A00XXXXXX,1492-01-10,2015-06-17,102,,,,
Last,First,A00XXXXXX,1492-01-10,2015-06-17,,,,Elementary Algebra 118,College Level Math 97

Get the book Effective Awk Programming, 4th Edition, by Arnold Robbins.

Upvotes: 3

anubhava
anubhava

Reputation: 785581

You can use this awk:

awk 'BEGIN{FS=OFS=","} {sub(/Reading Comprehension */, "", $6)} 1' file.csv
Last,First,A00XXXXXX,1492-01-10,2015-06-17,,Sentence Skills 104,,Elementary Algebra 38,
Last,First,A00XXXXXX,1492-01-10,2015-06-17,,,,Elementary Algebra 101,College Level Math 56
Last,First,A00XXXXXX,1492-01-10,2015-06-17,102,,,,
Last,First,A00XXXXXX,1492-01-10,2015-06-17,,,,Elementary Algebra 118,College Level Math 97

Upvotes: 3

Related Questions