How to match column 2 csv file and update column with new data and save it to new file

up vote
0
down vote

favorite

I have 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,,

and CountryCode.csv

MX, MEXICO
NZ, NEWZOMBIE

I want to update column 6 ListIPv6.csv from column 2 of CountryCode.csv when column 4 of ListIPv6.csv match with column 1 of CountryCode.csv

The expected output of updated ListIPv6.csv will be:

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

I have used below command but it doesn't fullfill my criteria.

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

asked Jun 6 at 3:04

basyirstar

add a commentÂ |Â

up vote
0
down vote

favorite

I have 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,,

and CountryCode.csv

MX, MEXICO
NZ, NEWZOMBIE

I want to update column 6 ListIPv6.csv from column 2 of CountryCode.csv when column 4 of ListIPv6.csv match with column 1 of CountryCode.csv

The expected output of updated ListIPv6.csv will be:

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

I have used below command but it doesn't fullfill my criteria.

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

asked Jun 6 at 3:04

basyirstar

add a commentÂ |Â

up vote
0
down vote

favorite

I have 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,,

and CountryCode.csv

MX, MEXICO
NZ, NEWZOMBIE

I want to update column 6 ListIPv6.csv from column 2 of CountryCode.csv when column 4 of ListIPv6.csv match with column 1 of CountryCode.csv

The expected output of updated ListIPv6.csv will be:

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

I have used below command but it doesn't fullfill my criteria.

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

asked Jun 6 at 3:04

basyirstar

I have 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,,

and CountryCode.csv

MX, MEXICO
NZ, NEWZOMBIE

I want to update column 6 ListIPv6.csv from column 2 of CountryCode.csv when column 4 of ListIPv6.csv match with column 1 of CountryCode.csv

The expected output of updated ListIPv6.csv will be:

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

I have used below command but it doesn't fullfill my criteria.

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

asked Jun 6 at 3:04

basyirstar

asked Jun 6 at 3:04

basyirstar

asked Jun 6 at 3:04

basyirstar

asked Jun 6 at 3:04

basyirstar

add a commentÂ |Â

2 Answers
2

active

oldest

votes

up vote
5
down vote

Using awk:

awk -F', *' 'NR==FNRa[$1]=$2;nextprint $0 a[$4]' CountryCode.csv ListIPv6.csv

The array a is filled with lines of the file CountryCode.csv and then the other file is displayed together with the entries of the array.

edited Jun 6 at 6:29

answered Jun 6 at 6:22

oliv

90427

add a commentÂ |Â

up vote
1
down vote

Unix can join without an 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

giving:

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,,

answered Jun 6 at 9:04

Gerard H. Pille

1,073212

add a commentÂ |Â

Your Answer

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "106"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
convertImagesToLinks: false,
noModals: false,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);

);

draft saved

draft discarded

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2funix.stackexchange.com%2fquestions%2f448102%2fhow-to-match-column-2-csv-file-and-update-column-with-new-data-and-save-it-to-ne%23new-answer', 'question_page');

);

Post as a guest

Name

2 Answers
2

active

oldest

votes

2 Answers
2

active

oldest

votes

up vote
5
down vote

Using awk:

awk -F', *' 'NR==FNRa[$1]=$2;nextprint $0 a[$4]' CountryCode.csv ListIPv6.csv

The array a is filled with lines of the file CountryCode.csv and then the other file is displayed together with the entries of the array.

edited Jun 6 at 6:29

answered Jun 6 at 6:22

oliv

90427

add a commentÂ |Â

up vote
5
down vote

Using awk:

awk -F', *' 'NR==FNRa[$1]=$2;nextprint $0 a[$4]' CountryCode.csv ListIPv6.csv

The array a is filled with lines of the file CountryCode.csv and then the other file is displayed together with the entries of the array.

edited Jun 6 at 6:29

answered Jun 6 at 6:22

oliv

90427

add a commentÂ |Â

up vote
5
down vote

Using awk:

awk -F', *' 'NR==FNRa[$1]=$2;nextprint $0 a[$4]' CountryCode.csv ListIPv6.csv

The array a is filled with lines of the file CountryCode.csv and then the other file is displayed together with the entries of the array.

edited Jun 6 at 6:29

answered Jun 6 at 6:22

oliv

90427

Using awk:

awk -F', *' 'NR==FNRa[$1]=$2;nextprint $0 a[$4]' CountryCode.csv ListIPv6.csv

The array a is filled with lines of the file CountryCode.csv and then the other file is displayed together with the entries of the array.

edited Jun 6 at 6:29

answered Jun 6 at 6:22

oliv

90427

edited Jun 6 at 6:29

answered Jun 6 at 6:22

oliv

90427

answered Jun 6 at 6:22

oliv

90427

answered Jun 6 at 6:22

oliv

90427

add a commentÂ |Â

up vote
1
down vote

Unix can join without an 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

giving:

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,,

answered Jun 6 at 9:04

Gerard H. Pille

1,073212

add a commentÂ |Â

up vote
1
down vote

Unix can join without an 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

giving:

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,,

answered Jun 6 at 9:04

Gerard H. Pille

1,073212

add a commentÂ |Â

up vote
1
down vote

Unix can join without an 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

giving:

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,,

answered Jun 6 at 9:04

Gerard H. Pille

1,073212

Unix can join without an 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

giving:

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,,

answered Jun 6 at 9:04

Gerard H. Pille

1,073212

answered Jun 6 at 9:04

Gerard H. Pille

1,073212

answered Jun 6 at 9:04

Gerard H. Pille

1,073212

answered Jun 6 at 9:04

Gerard H. Pille

1,073212

add a commentÂ |Â

draft saved

draft discarded

draft saved

draft discarded

Post as a guest

Name

搜尋此網誌

mjhjmtu