Create a batch script that does the work of VLOOKUP for CSV files

Question

I have two files: File11 and File22. Their first columns are similar and named concatenatedValue. I want to use this concatenatedValue column from File22 and search it in File11 and get the column values corresponding to it from File11. The result can be stored in a new CSV file e.g., output.csv.

I have written a batch script to complete this purpose but it's not working as expected. It opens a CMD window and is just stuck on it, neither showing any output nor anything else.

Also, the batch script code is below:

@REM vlookup kind of funtionality through batch script

@ECHO OFF

for /f "tokens=1 delims=, skip=1" %%i in (File22.csv) do @findstr  "%%i," File11.csv >nul & If errorlevel 0 IF NOT errorlevel 1 (for /f "tokens=1,2 delims=," %%m in ('findstr /i /L "%%i," File11.csv') do (@echo %%m,%%n>>output.csv
echo i value: %%i, m value: %%m, n value: %%n)) ELSE (echo %%i,NA>>output.csv)

cmd /k echo Hello

Please guide me in what I'm doing wrong.

The first thing you should do, is remove the `@` characters, change ```ECHO OFF``` to ```ECHO ON```, and run it from a Command Prompt window, to see what's happening. BTW, I would start by replacing ```& If errorlevel 0 IF NOT errorlevel 1``` with `&&` and replacing ```) ELSE (``` with ```) || (```. — Compo, Mar 01 '23 at 13:13
**1.** Do NOT post your data as an image. Post it here as text so we can copy it. How do you expect that we have example data to work with? **2.** Please, post the desired output from your example input data... — Aacini, Mar 01 '23 at 16:35

Aacini · Accepted Answer · 2023-03-01T17:01:37.700

Your method is very complicated... I would do it this way:

@echo off
setlocal EnableDelayedExpansion

rem Load File11 contents into File11 array using first column as array subscript
for /F "tokens=1* delims=, skip=1" %%i in (File11.csv) do set "File11[%%i]=%%j"

rem Process File22's first column and output values from same File11's first column
(for /F "tokens=1 delims=, skip=1" %%i in (File22.csv) do (
   if defined File11[%%i] (
      echo %%i,!File11[%%i]!
   ) else (
      echo %%i,NA
   )
)) > output.csv

You can review the method of process array elements in Batch at this answer

Create a batch script that does the work of VLOOKUP for CSV files

1 Answers1