Vlookup logic to create a folder for moving a report

Question

I have two files say Report.csv Reference.csv

I want to write a Script will reference the Report-ID in the report file names and cross-reference with Reference file data to determine the folder location to move the report.

For eg: Report.csv

R-00001.csv

Data in Reference.csv as below:

ReportID CompanyName
R-00001  BB Trust
R-00002  AA Trust
R-00003  ZZ Trust
R-00004  Canada Inc.

Here in the above, we could see that file name of Report.csv & the first cell value in the Reference.csv is same.

After lookup, the R-00001.csv file needs to be moved to "BB Trust" folder

Likewise, for each of the Report file, same procedure needs to be followed

Please help, it would simplify my work

  echo off
  :: Gets the filenames to textfile
  dir /b "C:\Users\Reports\" > 
  reportnames.txt
  :: Compare two files to look for 
  the matching ReportID and get the 
  Companyname value from 
  Reference.csv & create a folder in 
  that name and move the report 
  files to that corresponding folder

  for /f "tokens=1" %%i in 
  (Reportnames.txt) do
  @findstr "%%i," Reference.csv >nul 
  & If errorlevel 0 if not
  errorlevel 1 (for /f "tokens=2" 
  %%m in ('findstr /i /L "%%i," 
  Reference.csv') do ( cd 
  C:\Users\Archive\ & mkdir 
  %%m))

score 0 · Answer 1 · answered Nov 23 '17 at 05:30

Your description is somewhat confusing and the example code does not match the problem description, so I chose to follow the code.

It has no case to first create the Reportnames.txt file from a dir /B listing, and then process such a file... You may directly process the list of filenames via a standard for command.

The simplest and most direct way of "lookup logic" to find the matching CompanyName for a ReportId is via an array (although you may know it as an associative array).

Finally, it is not a good idea to use the :: trick to insert comments instead of rem commands (no matter how many examples you may see with such a trick).

@echo off

rem Next line is required to correctly process array elements in a FOR loop
setlocal EnableDelayedExpansion

rem Load the associative array from Reference.csv file
for /F "skip=1 tokens=1*" %%a in (Reference.csv) do set "CompanyName[%%a]=%%b"
rem Previous FOR is equivalent to these lines:
rem set "CompanyName[R-00001]=BB Trust"
rem set "CompanyName[R-00002]=AA Trust"    etc...

rem Process the filenames
for %%a in (C:\Users\Reports\*.*) do (

   rem Get the matching CompanyName for the ReportId
   set "folder=!CompanyName[%%~Na]!"

   if not exist "C:\Users\Archive\!folder!" md "C:\Users\Archive\!folder!"
   move "%%a" "C:\Users\Archive\!folder!"

)

If you have any doubt about a command, you may review the command description entering it with the /? parameter; for example: set /?

Vlookup logic to create a folder for moving a report

1 Answers1