24 Nov

Using VLookup like Batch script to compare two excel / csv

Using Vlookup:

I have two csv files; File1 has Id and Value Columns and File2 has Id.

Problem: I need to compare both files and put respective values to File2 from File1.

Solution: VLookup in excel

Steps:

  • Open both files in Excel & arrange it vertically (Window >> Arrange; for ease)

vlookup-1

  • Click on cell where you want to put compared value: B2
  • Click on fx button and select VLOOKUP function.

vlookup-2

  • Under lookup_value, click first search field: A2.
  • Click back in Table_array text field, and click on button to select range. Select two column for comparison starting from second row, i.e. A2 to B9.
  • Put 2 in Col_index_num. If lookup succeeds in finding lookup_value in Table_array it will return 2nd value as result.
  • Put FALSE in Range_lookup to do exact match.
  • Click ok, and you will find B2 will be filled with respective value.
  • You may drag the command to other rows and values will get filled. Optionally you may copy and paste it from rows B3 to B9 to lookup and fill all values.

vlookup-3

  • You can observer in output here, if I change the value of ID in File2 which doesn’t exist, VLookup returns #N/A.

Descriptions in details for this function and each respected field are easily available in excel help.

Download Files: File1, File2

Batch script to compare and assign value-simulate vlookup:

Further I came up with a batch script to compare field value, simulate vlookup. After doinglittle testing, I managed to fix it.

for /f “tokens=1 delims=, skip=1” %%i in (File2.csv) do @findstr  “%%i,” File1.csv >nul & If errorlevel 0 if not errorlevel 1 (for /f “tokens=1,2 delims=,” %%m in (‘findstr /i /L “%%i,” File1.csv’) do (@echo %%m,%%n>>output.csv echo %%i)) else (echo %%i,NA>>output.csv)

Please consider this batch script is written specifically for these csv and prove my understanding.
This script will compare file2 with file1, extract similar data from file1 and put it in output.csv.

Download batch file: vlookup-batch.bat

If you find any difficulty or don’t yield required results by following above process or using script, comment.

2 thoughts on “Using VLookup like Batch script to compare two excel / csv

  1. Hello,

    I have tried this script to compare csv files, but the output files was not correct. the input file had 36 row and 38 rows each and the output csv have 277 rows. please guide to correct this.

    Below is the files I used:-
    File1
    —–
    189208,Tom
    918648,Viktor
    643297,Sam

    File2
    —–
    189208,London,20130718
    918648,Essex,20130807
    189208,Berkshire,20130716
    918648,Middlesex,20130809
    643297,Yorkshire,20130718

    Output
    ——
    Tom,London,20130718
    Viktor,Essex,20130807
    Tom,Berkshire,20130716
    Viktor,Middlesex,20130809
    Sam,Yorkshire,20130718

Leave a Reply

Your email address will not be published. Required fields are marked *

*

-- Kedar Vaijanapurkar --