Batch script using VLookup logic for creating folders to move the reports

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: Name of Report.csv - R00001.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 R00001.csv file needs to be moved to BB Trust folder

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

Please have a look over on the below BATCH script and correct the bugs.
Rep.txt
Ragaz MaheAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill PrewIT / Software Engineering ConsultantCommented:
Please provide accurate samples of both input files.


»bp
0
Ragaz MaheAuthor Commented:
Hi Bill,
Below is the sample:
Report file name - R-00001.csv (file may contain any kind of data)
Reference file name is same as mentioned above (file should contain the ID that matches the file name of the report)
0
Bill PrewIT / Software Engineering ConsultantCommented:
Other files are mentioned like file1.csv and file2.csv.  You need to be more clear and exact on what these files contain and column layouts, etc.


»bp
0
Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Ragaz MaheAuthor Commented:
Sorry for the confusion, file1 refers to Report filenames like R-00001.csv, R-00002.csv.. & file2 referes to Reference files that contains ID column and CompanyName column.
0
Ragaz MaheAuthor Commented:
Can anyone assist on this?
0
Bill PrewIT / Software Engineering ConsultantCommented:
Okay, here is a starting point for you.  I always prefer to work with absolute pathnames rather than using CD or PUSHD in scripts.  It's far too easy for those to go wrong and end up updating the wrong files or folders.

I assumed that references.csv is a true comma delimited file with a single comma between the two columns.

I assumed that the folders mentioned in references.csv were to be created underneath the existing folder that contains the data files.

I reported to the console what files are moved, as well as any files that were in the existing base folder, but did not have an entry in reference.csv.

The batch file is below, folowed by my reference file, and then the console output when I ran my test.  I also included the before and after file locations.

Run the BAT script from a command window after updating your folder path near the top, so that you can see the output it generates.  As always test thoroughly.

***** EE29069856.BAT *****
@echo off
setlocal EnableDelayedExpansion

rem Define folder where files to move exist
set BaseDir=B:\EE\EE29069856\Files

rem Define path to reference file
set ReferenceFile=B:\EE\EE29069856\reference.csv

rem Look at each CSV file in base folder
for %%A in ("%BaseDir%\*.csv") do (

    rem Reset flag indicating if a match for each file is found in reference file
    set ReferenceFound=N

    rem Search reference file for match to base file name
    for /f "tokens=1* delims=," %%B in ('findstr /i /b "%%~nA," "%ReferenceFile%" 2^>NUL') do (

        rem Found match, create dest folder if needed
        if not exist "%BaseDir%\%%~C\" md "%BaseDir%\%%~C\"

        rem Move file to new folder
        echo Moving "%%~A" to "%BaseDir%\%%~C"
        move "%%~A" "%BaseDir%\%%~C\"

        rem Indicate we found a match in reference file
        set ReferenceFound=Y
    )

    rem Report any files that didn't find a match in reference file
    if "!ReferenceFound!" EQU "N" (
        echo Missing reference for "%%~nA"
    )

)

Open in new window

***** REFERENCE.CSV *****
ReportID,CompanyName
R-00001,BB Trust
R-00002,AA Trust
R-00003,ZZ Trust
R-00004,Canada Inc.

Open in new window

***** CONSOLE OUTPUT *****
Moving "B:\EE\EE29069856\Files\R-00001.csv" to "B:\EE\EE29069856\Files\BB Trust"
        1 file(s) moved.
Moving "B:\EE\EE29069856\Files\R-00002.csv" to "B:\EE\EE29069856\Files\AA Trust"
        1 file(s) moved.
Moving "B:\EE\EE29069856\Files\R-00003.csv" to "B:\EE\EE29069856\Files\ZZ Trust"
        1 file(s) moved.
Moving "B:\EE\EE29069856\Files\R-00004.csv" to "B:\EE\EE29069856\Files\Canada Inc."
        1 file(s) moved.
Missing reference for "R-00005"

Open in new window

***** FILES BEFORE TEST *****
B:.
|   reference.csv
|   EE29069856.bat
|
\---Files
        R-00001.csv
        R-00002.csv
        R-00003.csv
        R-00004.csv
        R-00005.csv

Open in new window

***** FILES AFTER TEST *****
B:.
|   reference.csv
|   EE29069856.bat
|
\---Files
    |   R-00005.csv
    |
    +---AA Trust
    |       R-00002.csv
    |
    +---ZZ Trust
    |       R-00003.csv
    |
    +---Canada Inc
    |       R-00004.csv
    |
    \---BB Trust
            R-00001.csv

Open in new window


»bp
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Windows Batch

From novice to tech pro — start learning today.