how to update one column in csv file using the mapping from another csv file using awk

Question

I need help in updating a huge csv file with 3.5 Million records. I need to update 3rd column with the mapping value from another file.

I tried reading the file and updating the 3rd column by searching the pattern in mapping file but since the actual file is having 3.5 million and mapping file is having ~1 million records, it seems to be running forever.

E.g.

Actual file:

123,123abc,456_def,456_def_ble,adsf,adsafdsa,123234,45645,435,12,42,afda,3435,wfg,34,345,sergf,5t4
234,234abc,5435_defg,345_def_ble,3adsaff,asdfgdsa,165434,456,435,12,42,afda,3435,wfg,34,345,sergf,5t4

Mapping File:

456_def,24_def
5435_defg,48_defg

Output expected:

123,123abc,24_def,456_def_ble,adsf,adsafdsa,123234,45645,435,12,42,afda,3435,wfg,34,345,sergf,5t4
234,234abc,48_defg,345_def_ble,3adsaff,asdfgdsa,165434,456,435,12,42,afda,3435,wfg,34,345,sergf,5t4

Updated the question.

Muneeb Kibriya
– Muneeb Kibriya

2017-03-02 12:30:01 +00:00
Commented Mar 2, 2017 at 12:30 — Muneeb Kibriya
– Muneeb Kibriya, Commented Mar 2, 2017 at 12:30

Inian · Accepted Answer · 2017-03-02 12:33:16Z

Pretty straight-forward in Awk

awk 'BEGIN{FS=OFS=","}FNR==NR{hash[$1]=$2; next}$3 in hash{$3=hash[$3]}1' mapFile actualFile

produces an output as you needed.

123,123abc,24_def,456_def_ble,adsf,adsafdsa,123234,45645,435,12,42,afda,3435,wfg,34,345,sergf,5t4
234,234abc,48_defg,345_def_ble,3adsaff,asdfgdsa,165434,456,435,12,42,afda,3435,wfg,34,345,sergf,5t4

To speed up things, you can change the locale setting to use ASCII,

Simply put, when using the locale C it will default to the server's base Unix/Linux language of ASCII. By default your locale is going to be internationalized and set to UTF-8, which can represent every character in the Unicode character set to help display any of the world's writing systems, currently over more than 110,000 unique characters, whereas with ASCII each character is encoded in a single byte sequence and its character set comprises of no longer than 128 unique characters. So just do

LC_ALL=C awk 'BEGIN{FS=OFS=","}FNR==NR{hash[$1]=$2; next}$3 in hash{$3=hash[$3]}1' mapFile actualFile

oliv · Accepted Answer · 2017-03-02 12:32:51Z

1

You can use awk for this:

awk 'BEGIN{FS=OFS=","}       # Set field separator as comma
     NR==FNR{a[$1]=$2;next}  # Store the mapping file into the array a
     {if($3 in a) $3=a[$3]}  # Check if there is match, and change the column value
     1                       # Print the whole line
    ' mapping actualfile

answered Mar 2, 2017 at 12:32

oliv

13.3k30 silver badges52 bronze badges

Collectives™ on Stack Overflow

how to update one column in csv file using the mapping from another csv file using awk

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related