Append lines from csv file to another based on column 1 matches
Clash Royale CLAN TAG#URR8PPP
up vote
0
down vote
favorite
I have 2 .csv files, file1.csv and file2.csv. Say a line from file2.csv has the same 1st field as a line from file1.csv. I would like the full line from file1.csv to replace the full line from file2.csv.
I have tried a script to delete the line from file2 and add the line from file1. I've tried to awk the lines from file2 that DON'T contain the match, and then add from file1but failed.
file1.csv:
1,2,3
2,3,4
3,4,5
file2.csv
6,7,8
7,8,9
1,9,0
Desired result:
6,7,8
7,8,9
1,2,3
awk variable csv
edited Sep 30 at 18:31
Jeff Schaller
33.6k851113
asked Sep 30 at 18:12
Trevor Martin
31
New contributor
Trevor Martin is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |Â
up vote
0
down vote
favorite
I have 2 .csv files, file1.csv and file2.csv. Say a line from file2.csv has the same 1st field as a line from file1.csv. I would like the full line from file1.csv to replace the full line from file2.csv.
I have tried a script to delete the line from file2 and add the line from file1. I've tried to awk the lines from file2 that DON'T contain the match, and then add from file1but failed.
file1.csv:
1,2,3
2,3,4
3,4,5
file2.csv
6,7,8
7,8,9
1,9,0
Desired result:
6,7,8
7,8,9
1,2,3
awk variable csv
edited Sep 30 at 18:31
Jeff Schaller
33.6k851113
asked Sep 30 at 18:12
Trevor Martin
31
New contributor
Trevor Martin is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Your title says "append" but I find that misleading; the body says (reworded) "replace entire liens from file2 with matching lines from file1"; where does "append" come in?
– Jeff Schaller
Sep 30 at 18:35
Caveats: I will NOT know the values that match. I've been trying to run "while read do" inserting the variable value in different ways. Also the number of fields will vary by line, but the matching values will always be in the first field. Please help!
– Trevor Martin
Sep 30 at 18:36
Jeff, by "append" I was simply referring to the fact that the first field will remain while the rest are "updated." I assumed that replacing the entire line would be easiest (since the replacement line is one with the same value in the first field)
– Trevor Martin
Sep 30 at 18:40
What if you have more than one match in the first field?
– Goro
Sep 30 at 19:36
Luckily the type of data I'm dealing with doesn't allow for that, because I can see how that would be a problem.
– Trevor Martin
Oct 1 at 5:59
add a comment |Â
up vote
0
down vote
favorite
up vote
0
down vote
favorite
I have 2 .csv files, file1.csv and file2.csv. Say a line from file2.csv has the same 1st field as a line from file1.csv. I would like the full line from file1.csv to replace the full line from file2.csv.
I have tried a script to delete the line from file2 and add the line from file1. I've tried to awk the lines from file2 that DON'T contain the match, and then add from file1but failed.
file1.csv:
1,2,3
2,3,4
3,4,5
file2.csv
6,7,8
7,8,9
1,9,0
Desired result:
6,7,8
7,8,9
1,2,3
awk variable csv
edited Sep 30 at 18:31
Jeff Schaller
33.6k851113
asked Sep 30 at 18:12
Trevor Martin
31
New contributor
Trevor Martin is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
I have 2 .csv files, file1.csv and file2.csv. Say a line from file2.csv has the same 1st field as a line from file1.csv. I would like the full line from file1.csv to replace the full line from file2.csv.
I have tried a script to delete the line from file2 and add the line from file1. I've tried to awk the lines from file2 that DON'T contain the match, and then add from file1but failed.
file1.csv:
1,2,3
2,3,4
3,4,5
file2.csv
6,7,8
7,8,9
1,9,0
Desired result:
6,7,8
7,8,9
1,2,3
awk variable csv
awk variable csv
edited Sep 30 at 18:31
Jeff Schaller
33.6k851113
asked Sep 30 at 18:12
Trevor Martin
31
New contributor
Trevor Martin is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
edited Sep 30 at 18:31
Jeff Schaller
33.6k851113
asked Sep 30 at 18:12
Trevor Martin
31
New contributor
Trevor Martin is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
edited Sep 30 at 18:31
Jeff Schaller
33.6k851113
edited Sep 30 at 18:31
Jeff Schaller
33.6k851113
edited Sep 30 at 18:31
Jeff Schaller
33.6k851113
33.6k851113
asked Sep 30 at 18:12
Trevor Martin
31
New contributor
Trevor Martin is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked Sep 30 at 18:12
Trevor Martin
31
asked Sep 30 at 18:12
Trevor Martin
31
31
New contributor
Trevor Martin is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
Trevor Martin is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Trevor Martin is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Your title says "append" but I find that misleading; the body says (reworded) "replace entire liens from file2 with matching lines from file1"; where does "append" come in?
– Jeff Schaller
Sep 30 at 18:35
Caveats: I will NOT know the values that match. I've been trying to run "while read do" inserting the variable value in different ways. Also the number of fields will vary by line, but the matching values will always be in the first field. Please help!
– Trevor Martin
Sep 30 at 18:36
Jeff, by "append" I was simply referring to the fact that the first field will remain while the rest are "updated." I assumed that replacing the entire line would be easiest (since the replacement line is one with the same value in the first field)
– Trevor Martin
Sep 30 at 18:40
What if you have more than one match in the first field?
– Goro
Sep 30 at 19:36
Luckily the type of data I'm dealing with doesn't allow for that, because I can see how that would be a problem.
– Trevor Martin
Oct 1 at 5:59
add a comment |Â
Your title says "append" but I find that misleading; the body says (reworded) "replace entire liens from file2 with matching lines from file1"; where does "append" come in?
– Jeff Schaller
Sep 30 at 18:35
Caveats: I will NOT know the values that match. I've been trying to run "while read do" inserting the variable value in different ways. Also the number of fields will vary by line, but the matching values will always be in the first field. Please help!
– Trevor Martin
Sep 30 at 18:36
Jeff, by "append" I was simply referring to the fact that the first field will remain while the rest are "updated." I assumed that replacing the entire line would be easiest (since the replacement line is one with the same value in the first field)
– Trevor Martin
Sep 30 at 18:40
What if you have more than one match in the first field?
– Goro
Sep 30 at 19:36
Luckily the type of data I'm dealing with doesn't allow for that, because I can see how that would be a problem.
– Trevor Martin
Oct 1 at 5:59
Your title says "append" but I find that misleading; the body says (reworded) "replace entire liens from file2 with matching lines from file1"; where does "append" come in?
– Jeff Schaller
Sep 30 at 18:35
Your title says "append" but I find that misleading; the body says (reworded) "replace entire liens from file2 with matching lines from file1"; where does "append" come in?
– Jeff Schaller
Sep 30 at 18:35
Caveats: I will NOT know the values that match. I've been trying to run "while read do" inserting the variable value in different ways. Also the number of fields will vary by line, but the matching values will always be in the first field. Please help!
– Trevor Martin
Sep 30 at 18:36
Caveats: I will NOT know the values that match. I've been trying to run "while read do" inserting the variable value in different ways. Also the number of fields will vary by line, but the matching values will always be in the first field. Please help!
– Trevor Martin
Sep 30 at 18:36
Jeff, by "append" I was simply referring to the fact that the first field will remain while the rest are "updated." I assumed that replacing the entire line would be easiest (since the replacement line is one with the same value in the first field)
– Trevor Martin
Sep 30 at 18:40
Jeff, by "append" I was simply referring to the fact that the first field will remain while the rest are "updated." I assumed that replacing the entire line would be easiest (since the replacement line is one with the same value in the first field)
– Trevor Martin
Sep 30 at 18:40
What if you have more than one match in the first field?
– Goro
Sep 30 at 19:36
What if you have more than one match in the first field?
– Goro
Sep 30 at 19:36
Luckily the type of data I'm dealing with doesn't allow for that, because I can see how that would be a problem.
– Trevor Martin
Oct 1 at 5:59
Luckily the type of data I'm dealing with doesn't allow for that, because I can see how that would be a problem.
– Trevor Martin
Oct 1 at 5:59
add a comment |Â
1 Answer
1
active
oldest
votes
up vote
1
down vote
accepted
For the simple input files that you have posted:
$ awk -F, 'NR==FNR a[$1]=$0; next $1 in a $0=a[$1] 1' file1.csv file2.csv
6,7,8
7,8,9
1,2,3
Note that in general, the CSV format allows for quoted embedded separators and other nasties that may make a simple Awk script unsuitable
answered Sep 30 at 19:36
steeldriver
32.5k34980
That's great! Thank you
– Trevor Martin
Oct 1 at 8:05
add a comment |Â
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
1
down vote
accepted
For the simple input files that you have posted:
$ awk -F, 'NR==FNR a[$1]=$0; next $1 in a $0=a[$1] 1' file1.csv file2.csv
6,7,8
7,8,9
1,2,3
Note that in general, the CSV format allows for quoted embedded separators and other nasties that may make a simple Awk script unsuitable
answered Sep 30 at 19:36
steeldriver
32.5k34980
That's great! Thank you
– Trevor Martin
Oct 1 at 8:05
add a comment |Â
up vote
1
down vote
accepted
For the simple input files that you have posted:
$ awk -F, 'NR==FNR a[$1]=$0; next $1 in a $0=a[$1] 1' file1.csv file2.csv
6,7,8
7,8,9
1,2,3
Note that in general, the CSV format allows for quoted embedded separators and other nasties that may make a simple Awk script unsuitable
answered Sep 30 at 19:36
steeldriver
32.5k34980
That's great! Thank you
– Trevor Martin
Oct 1 at 8:05
add a comment |Â
up vote
1
down vote
accepted
up vote
1
down vote
accepted
For the simple input files that you have posted:
$ awk -F, 'NR==FNR a[$1]=$0; next $1 in a $0=a[$1] 1' file1.csv file2.csv
6,7,8
7,8,9
1,2,3
Note that in general, the CSV format allows for quoted embedded separators and other nasties that may make a simple Awk script unsuitable
answered Sep 30 at 19:36
steeldriver
32.5k34980
For the simple input files that you have posted:
$ awk -F, 'NR==FNR a[$1]=$0; next $1 in a $0=a[$1] 1' file1.csv file2.csv
6,7,8
7,8,9
1,2,3
Note that in general, the CSV format allows for quoted embedded separators and other nasties that may make a simple Awk script unsuitable
answered Sep 30 at 19:36
steeldriver
32.5k34980
answered Sep 30 at 19:36
steeldriver
32.5k34980
answered Sep 30 at 19:36
steeldriver
32.5k34980
answered Sep 30 at 19:36
steeldriver
32.5k34980
32.5k34980
That's great! Thank you
– Trevor Martin
Oct 1 at 8:05
add a comment |Â
That's great! Thank you
– Trevor Martin
Oct 1 at 8:05
That's great! Thank you
– Trevor Martin
Oct 1 at 8:05
That's great! Thank you
– Trevor Martin
Oct 1 at 8:05
add a comment |Â
Trevor Martin is a new contributor. Be nice, and check out our Code of Conduct.
Â
draft saved
draft discarded
Trevor Martin is a new contributor. Be nice, and check out our Code of Conduct.
Trevor Martin is a new contributor. Be nice, and check out our Code of Conduct.
Trevor Martin is a new contributor. Be nice, and check out our Code of Conduct.
Â
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2funix.stackexchange.com%2fquestions%2f472442%2fappend-lines-from-csv-file-to-another-based-on-column-1-matches%23new-answer', 'question_page');
);
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Your title says "append" but I find that misleading; the body says (reworded) "replace entire liens from file2 with matching lines from file1"; where does "append" come in?
– Jeff Schaller
Sep 30 at 18:35
Caveats: I will NOT know the values that match. I've been trying to run "while read do" inserting the variable value in different ways. Also the number of fields will vary by line, but the matching values will always be in the first field. Please help!
– Trevor Martin
Sep 30 at 18:36
Jeff, by "append" I was simply referring to the fact that the first field will remain while the rest are "updated." I assumed that replacing the entire line would be easiest (since the replacement line is one with the same value in the first field)
– Trevor Martin
Sep 30 at 18:40
What if you have more than one match in the first field?
– Goro
Sep 30 at 19:36
Luckily the type of data I'm dealing with doesn't allow for that, because I can see how that would be a problem.
– Trevor Martin
Oct 1 at 5:59