-1

How do I replace a column value in a file when compared with another file?

I have two files test1.csv and test2.csv; I need to replace the empdep column in test1.csv if it has the value as "sign*". The second file test2.csv has the value needed to replace that "sign*".

Note : I'm using ksh and test1.csv has around 2,048,576 rows and test2.csv has 10000 rows.

test1.csv

empname,place,empdep
aaaa,city1,001
bbbb,city2,sign-1
dddd,city1,005
ffff,city5,sign-2
hhhh,city7,sign-1

test2.csv

empname,new
aaaa,001
bbbb,002
cccc,003
dddd,005
eeee,006
ffff,007
gggg,008
hhhh,009

Expected result:

empname,place,empdep
aaaa,city1,001
bbbb,city2,002
dddd,city1,005
ffff,city5,007
hhhh,city7,009
2
  • @agc I incorporated the space characters of the original images intentionally (output was consistent), but thanks for the fix! – Freddy Apr 10 '20 at 15:19
  • @Quasímodo I had considered that, but for a code-free novice Q. the spaces are irrelevant to the problem. It's highly probable those spaces were typos, because the OP's first draft was full of them. – agc Apr 10 '20 at 15:31
1

With awk:

awk '
  BEGIN{ FS=OFS="," } # set input/output field separator to `,`
  NR==FNR{            # if this is the first file `test2.csv`
    a[$1]=$2          # store field2 in array `a` using field1 as index
    next              # continue with next line
  } 
  $3 ~ /^sign/{       # if field3 of `test1.csv` begins with `sign`
    $3=a[$1]          # replace the field with array value (index of field1)
  }
  1                   # print the line
' test2.csv test1.csv
12
  • Hi Freddy, Thanks for your quick response... But i have question here... where we are checking/comparing the employename of table1 and table2 are equal or not? for example.... column 2 in table 1 is sign, then it should compare the empname in both the table, then it should replace the deptname. where the above condition is getting check? – looty Apr 10 '20 at 18:24
  • instead of $3, i tried the above code for $7( just changed $3 into $7 in code) but got empty space istead of value... Please check below xfsgdh|gdjshd|bbbb|20191228|230||sign-1|14420|44766|||||||20191228 --- original xfsgdh|gdjshd|bbb|20191228|230|||14420|44766|||||||20191228 - result after code – looty Apr 10 '20 at 18:40
  • We check if column3 in test1.csv starts with sign, we don't check if empname exists in the array, we assume it is. Example: row3 contains bbbb,city2,sign-1 and array a contains an entry a[bbbb]=002 and we replace field3 with $3=a[$1] where $1 is the value bbbb of row3. – Freddy Apr 10 '20 at 18:46
  • If you're using the separator |, then change the "," to "|". – Freddy Apr 10 '20 at 18:54
  • so, i understood here is, for this indexing method the file1 and file2 should have same number of rows.. correct me if i'm wrong... Reason for this question is, i got empty space instead of replaced value. Please check below xfsgdh|gdjshd|bbbb|20191228|230||sign-1|14420|44766|||||||20191228 --- original xfsgdh|gdjshd|bbb|20191228|230|||14420|44766|||||||20191228 - result after code in my file1 i almost 100000 rows, but in file2 i have 1000 rows only... – looty Apr 10 '20 at 19:03
0

This is one simple way :

for i in $(cat text1.csv)  
do  
    name=$(echo $i | cut -d',' -f1)   
    empdep=$(echo $i | cut -d',' -f3)  
    newvalue=$(grep $name text2.csv | cut -d',' -f2)    
    if [[ $empdep = sign* ]]    
    then  
        sed -n "s/^$name,\(.*\),.*/$name,\1,$newvalue/pg" text1.csv  
    else  
        echo $i  
    fi  
done
3
  • Yeah i agree ... it will be slow... – Stalin Vignesh Kumar Apr 10 '20 at 15:23
  • That way is not "simple". Instead of for and cat, consider using while IFS=, read name f2 emdep f4, and done < text1.csv, that way lines #3-#4 aren't needed. Consider using an associative array instead of the grep and sed stuff. – agc Apr 10 '20 at 15:25
  • I meant simple to be understandable by beginner...obviously you are right . But sometimes while loop unable to get the sub shell's value of a variable ...so i used for loop... – Stalin Vignesh Kumar Apr 10 '20 at 15:32
0

Using ksh and sed. Use sed to parse test2.csv and populate an associative array ${new[@]}. Then loop through test1.csv and use pattern substitution to print the desired outputs:

typeset -A new $(sed -n '2,${s/^/new[/;s/,/]=/p}' test2.csv)
while IFS=, read a b c; do echo $a,$b,${c/#sign*/${new[$a]}}; done < test1.csv

Output:

empname,place,empdep
aaaa,city1,001
bbbb,city2,002
dddd,city1,005
ffff,city5,007
hhhh,city7,009

Note: In this instance the input files have no quotes, and the code is visually simpler without quotes. If either input file contains, (or might contain), spaces, the variables above must be quoted.

1
  • The $b isn't strictly needed. This while IFS=, read a c; do echo $a,${c/,sign*/,${new[$a]}}; done < test1.csv would also work... – agc Apr 11 '20 at 14:46
0
csv-merge -N t1 -p test1.csv -N t2 -p test2.csv | 
csv-sqlite -T 'select t1.empname, t1.place, case when t1.empdep like "sign%" then t2.new else t1.empdep end as empdep
from t1 left join t2 on t1.empname = t2.empname'

csv-merge and csv-sqlite are from https://github.com/mslusarz/csv-nix-tools

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.