CODEWITHSUNDEEP
shellcsvawkseddiff
Young Chang
Young Chang

Reputation: 21

shell - compare files and update matching string awk/sed/diff/grep/csv

I need to compare 2 csv files and make modifications to the second column. I wrote out the logic out of how I would want to achieve this however, it seems to confuse the thread a lot more than I wanted too so I'll just write out the example.

Any help would be appreciated. Thanks in advance.

file1

user1,distinguishedName1
user2,distinguishedName2
user3,distinguishedName3
user4,distinguishedName4
user5,distinguishedName5

file2

user1,distinguishedName1
user3,distinguishedName13
user5,distinguishedName12
user6,distinguishedName4

desired outcome:

user1,distinguishedName1
user2,distinguishedName2
user3,distinguishedName13
user4,distinguishedName4
user5,distinguishedName12
user6,distinguishedName4

Upvotes: 2

Views: 179

Answers (2)

karakfa
karakfa

Reputation: 67467

awk to the rescue!

awk -F, '!a[$1]++' file2 file1

user1,distinguishedName1
user3,distinguishedName13
user5,distinguishedName12
user6,distinguishedName4
user2,distinguishedName2
user4,distinguishedName4

this order is based on file2 and file1 record order, if you want sorted order just pipe to sort

awk ... | sort

Upvotes: 2

RomanPerekhrest
RomanPerekhrest

Reputation: 92854

The solution using join command combined with awk command:

join -t',' -j1 -a1 -a2  file1 file2 | awk -F',' '{if(NF==3) $0=$1FS$3}1'

The output:

user1,distinguishedName1
user2,distinguishedName2
user3,distinguishedName13
user4,distinguishedName4
user5,distinguishedName12
user6,distinguishedName4

Explanation:

-- for join command:

-t',' - defines field separator

-j1 - tells to join on first field 1

-a FILENUM - print unpairable lines coming from file FILENUM, where FILENUM is 1 or 2, corresponding to FILE1 or FILE2

-- for awk command:

NF - contains a total number of fields

FS - field separator(i.e. ,)

if(NF==3) $0=$1FS$3 - the condition, checks if there's a complement third field(as result of joining the files on lines with common first field) to perform the replacement

https://linux.die.net/man/1/join

Upvotes: 2

Related Questions