4

How to make something similar to Excel's vlookup function in Unix?

excerpt from office website, VLOOKUP

The V in VLOOKUP stands for vertical. Use VLOOKUP instead of HLOOKUP when your comparison values are located in a column to the left of the data that you want to find.

Syntax VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

Lookup_value The value to search in the first column of the table array. Lookup_value can be a value or a reference. If lookup_value is smaller than the smallest value in the first column of table_array, VLOOKUP returns the #N/A error value.

Table_array Two or more columns of data. Use a reference to a range or a range name. The values in the first column of table_array are the values searched by lookup_value. These values can be text, numbers, or logical values. Uppercase and lowercase text are equivalent.

Col_index_num The column number in table_array from which the matching value must be returned. A col_index_num of 1 returns the value in the first column in table_array; a col_index_num of 2 returns the value in the second column in table_array, and so on. If col_index_num is:

Less than 1, VLOOKUP returns the #VALUE! error value. Greater than the number of columns in table_array, VLOOKUP returns the #REF! error value.

Range_lookup A logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match:

File1:

1GR_P1:001PI
:040VG_L1
:001PO_L3
1JPI_P1:001PO_L1
1JPI_P1:001PO_L2

File2:

1JPI_P1:001PO_L1    1401UC
1JPI_P1:001PO_L2    1401UC
1HIK_P2:001ER       1402UC
1GR_P1:001PI        1402UC

Output-File3:

1GR_P1:001PI        1402UC
:040VG_L1       NA
:001PO_L3       NA
1JPI_P1:001PO_L1    1401UC
1JPI_P1:001PO_L2    1401UC
7

There isn't a general function that will do a vlookup as a general function in Unix. Rather you're giving "bricks" from which you can build solutions to problems in a more customized approach. These "bricks" are tools such as grep, awk, and sed among others.

One of the tools, awk could be used as follows:

vlookup.awk

FNR==NR{
  a[$1]=$2
  next
}
{ if ($1 in a) {print $1, a[$1]} else {print $1, "NA"}  }

Example

$ awk -f vlookup.awk file2 file1
1GR_P1:001PI 1GR_P1:001PI
:040VG_L1 NA
:001PO_L3 NA
1JPI_P1:001PO_L1 1JPI_P1:001PO_L1
1JPI_P1:001PO_L2 1JPI_P1:001PO_L2

You can use the column command to cleanup the output:

$ awk -f vlookup.awk file2 file1 | column -t
1GR_P1:001PI      1GR_P1:001PI
:040VG_L1         NA
:001PO_L3         NA
1JPI_P1:001PO_L1  1JPI_P1:001PO_L1
1JPI_P1:001PO_L2  1JPI_P1:001PO_L2

Details

The above awk script takes all the content of file2 into an array which is indexed using the value as a key.

a[$1]=$1

Once file2 has been read into array a, file1 is then gone through a line at a time and a decision is made. If the value of the first column of file1 is present in the array a, then the corresponding value in file2's column 2 is printed along with file1's column 1. If it isn't present then the "NA" message is printed.

  • Believe to meet the OP's requirement you need to change a[$1]=$1 to a[$1]=$2 – iruvar Aug 28 '13 at 15:10
  • is $1 column1 and $2 column2? Or what is the difference between a[$1]=$1 and a[$1]=$2 – HattrickNZ Aug 18 '15 at 3:07
  • The $1 and $2 in vlookup.awk are columns but we're only setting a[$1]==$2 when the FNR is equal to NR. See this example on SO for a better description of this method using awk: stackoverflow.com/questions/15065818/compare-files-with-awk. – slm Aug 18 '15 at 5:31
1

For the specific data examples you have provided, the following should work. It loads field 2 from File2 into an array indexed by field 1. File1 is then looped through and array matches or NA are printed

awk 'NR == FNR{a[$1] = $2;next}; {print $1, $1 in a?a[$1]: "NA"}' File2 File1
1

The POSIX join(1) command does something very similar to VLOOKUP(), with the caveat that the input files must already be sorted on the columns to be joined.

$ sort file1 > sfile1
$ sort file2 > sfile2
$ join -a1 sfile1 sfile2
1GR_P1:001PI 1402UC
1JPI_P1:001PO_L1 1401UC
1JPI_P1:001PO_L2 1401UC
:001PO_L3
:040VG_L1

Unfortunately, your example doesn't really illustrate how join works, since file1 contains just one column.

To get exactly the output you want, you could write a simple script using associative arrays, using awk for example, as others have suggested.

0

If you're looking for something which works from the command line have a look at awk. This is a VERY popular program used for all sorts of parsing operations. http://en.wikipedia.org/wiki/AWK

Also, it's hard to mention parsing text in UNIX without mentioning grep. grep is used for regex matching text. While not needed for this particular application it will eventually come in handy if you're doing much text parsing. http://en.wikipedia.org/wiki/Grep

Using colrm columns of text can be cut from a stream. This can be useful when you are having trouble isolating the text with awk.

sedis what you'll want to use if the text to parse is very long or if awk isn't able to easily accomplish what you want. Sed on Wikipedia

I'm sure I'm missing dozens but all you'll need for this example is awk so you're set.

  • I've added it for you krowe :) – Drav Sloan Aug 28 '13 at 16:06
  • @DravSloan - congrats o the 2k level! – slm Aug 28 '13 at 16:06
0

Try a mixture of awk and redis (an extremely quick open source NoSQL key-value store. See http://redis.io for details).

Use awk for parsing your 2 files to generate your redis commands.

Pipe the result of the 2 awk scripts into bash to execute them. That's it :-)

Step by step:

Generate your redis "SET" statements by parsing "File2" like this:

awk '{print "redis-cli SET KEY:" $1 " \"" $2"\""}' File2
redis-cli SET KEY:1JPI_P1:001PO_L1 "1401UC"
redis-cli SET KEY:1JPI_P1:001PO_L2 "1401UC"
redis-cli SET KEY:1HIK_P2:001ER "1402UC"
redis-cli SET KEY:1GR_P1:001PI "1402UC"

Pipe your generated redis "SET" statements into bash to execute them:

awk '{print "redis-cli SET KEY:" $1 " \"" $2"\""}' File2 |\
 bash
OK
OK
OK
OK

Generate your redis "GET" statements by parsing "File1" like this:

awk '{print "printf \"" $1 " \" && redis-cli GET KEY:" $1}' File1
printf "1GR_P1:001PI " && redis-cli GET KEY:1GR_P1:001PI
printf ":040VG_L1 " && redis-cli GET KEY::040VG_L1
printf ":001PO_L3 " && redis-cli GET KEY::001PO_L3
printf "1JPI_P1:001PO_L1 " && redis-cli GET KEY:1JPI_P1:001PO_L1
printf "1JPI_P1:001PO_L2 " && redis-cli GET KEY:1JPI_P1:001PO_L2

Now query redis by piping your redis "GET" statements generated above into bash:

awk '{print "printf \"" $1 " \" && redis-cli GET KEY:" $1}' File1 |\
 bash
1GR_P1:001PI "1402UC"
:040VG_L1 (nil)
:001PO_L3 (nil)
1JPI_P1:001PO_L1 "1401UC"
1JPI_P1:001PO_L2 "1401UC"

Beware that you need to escape double quotes in your strings with single backslashes to avoid redis import errors (see slm's answer at How do I modify this Perl solution so that it will substitute embedded double quotes with single quotes? ). You can also use single quotes to encapsulate your values for import into redis, if your values are containing a lot of double quotes.

HTH

bernie

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.