• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 95
  • Last Modified:

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
0
Ragaz Mahe
Asked:
Ragaz Mahe
  • 3
  • 3
1 Solution
 
Bill PrewCommented:
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 PrewCommented:
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
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
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 PrewCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now