Como combinar o arquivo csv da coluna 2 e atualizar a coluna com novos dados e salvá-los no novo arquivo

Question

Eu tenho ListIPv6.csv

2001:c00::-2001:dff:ffff:ffff:ffff:ffff:ffff:ffff,iana,1020211200,ZZ,,
2001:e00::-2001:fff:ffff:ffff:ffff:ffff:ffff:ffff,iana,1041379200,ZZ,,
2001:1200::-2001:13ff:ffff:ffff:ffff:ffff:ffff:ffff,iana,1036108800,ZZ,,
2001:1200::-2001:1200:ffff:ffff:ffff:ffff:ffff:ffff,lacnic,1042156800,MX,,
2001:1208::-2001:1208:ffff:ffff:ffff:ffff:ffff:ffff,lacnic,1044230400,MX,,
2001:1210::-2001:1210:ffff:ffff:ffff:ffff:ffff:ffff,lacnic,1095984000,MX,,
2001:1218::-2001:1218:ffff:ffff:ffff:ffff:ffff:ffff,lacnic,1120003200,MX,,

e CountryCode.csv

MX, MEXICO
NZ, NEWZOMBIE

Desejo atualizar a coluna 6 ListIPv6.csv da coluna 2 do CountryCode.csv quando a coluna 4 do ListIPv6.csv corresponder à coluna 1 do CountryCode.csv

A saída esperada de ListIPv6.csv atualizado será:

2001:c00::-2001:dff:ffff:ffff:ffff:ffff:ffff:ffff,iana,1020211200,ZZ,,
2001:e00::-2001:fff:ffff:ffff:ffff:ffff:ffff:ffff,iana,1041379200,ZZ,,
2001:1200::-2001:13ff:ffff:ffff:ffff:ffff:ffff:ffff,iana,1036108800,ZZ,,
2001:1200::-2001:1200:ffff:ffff:ffff:ffff:ffff:ffff,lacnic,1042156800,MX,,MEXICO
2001:1208::-2001:1208:ffff:ffff:ffff:ffff:ffff:ffff,lacnic,1044230400,MX,,MEXICO
2001:1210::-2001:1210:ffff:ffff:ffff:ffff:ffff:ffff,lacnic,1095984000,MX,,MEXICO
2001:1218::-2001:1218:ffff:ffff:ffff:ffff:ffff:ffff,lacnic,1120003200,MX,,MEXICO

Eu usei o comando abaixo, mas ele não preenche meus critérios.

sed -f <(printf 's/%s/%s/g\n' $(<CountryCode.csv)) <(head -n 1000 ListIPv6.csv) >NewListIPv6.csv

    

Answer 1

Usando awk :

awk -F', *' 'NR==FNR{a[$1]=$2;next}{print $0 a[$4]}' CountryCode.csv ListIPv6.csv

A matriz a é preenchida com linhas do arquivo CountryCode.csv e, em seguida, o outro arquivo é exibido junto com as entradas da matriz.

    

Answer 2

Unix pode se juntar sem um RDBMS:

sort -t , -k 4 ListIPv6.csv | join -a 1 -t , -1 4 -2 1 -o 1.1,1.2,1.3,1.4,1.5,2.2 - CountryCode.csv

dando:

2001:1200::-2001:1200:ffff:ffff:ffff:ffff:ffff:ffff,lacnic,1042156800,MX,, MEXICO
2001:1208::-2001:1208:ffff:ffff:ffff:ffff:ffff:ffff,lacnic,1044230400,MX,, MEXICO
2001:1210::-2001:1210:ffff:ffff:ffff:ffff:ffff:ffff,lacnic,1095984000,MX,, MEXICO
2001:1218::-2001:1218:ffff:ffff:ffff:ffff:ffff:ffff,lacnic,1120003200,MX,, MEXICO
2001:1200::-2001:13ff:ffff:ffff:ffff:ffff:ffff:ffff,iana,1036108800,ZZ,, 
2001:c00::-2001:dff:ffff:ffff:ffff:ffff:ffff:ffff,iana,1020211200,ZZ,,
2001:e00::-2001:fff:ffff:ffff:ffff:ffff:ffff:ffff,iana,1041379200,ZZ,,