Click here to Skip to main content
14,300,872 members
Rate this:
Please Sign up or sign in to vote.
See more:
Hi,

Actually, i have to compare two excel file that are in placed in Two different folders.
Eg. Folder1. Abc_rpt1 and Folder2. Xyz_rpt1.
Note, in both the folders report template is same but report name and the values are different. In both the folders the reports are in the same sequence like in first place we have abc_rpt1 in second place abc_rpt2 same as i. Folder2 Xyz-rpt1 and Xyz_rpt2. Now, in my code it is comparing the report when the report name in both the folders will same.

What I have tried:

@echo off
set /p "MasterFolder1=MasterFolder1:"
set /p "MasterFolder2=MasterFolder2:"
Set "LogFile=%~dpn0.txt"
If exist "%LogFile%" Del "%LogFile%"

Set "PECfolder=C:\SunilKK-Latitude-E6430s\SunilKK\Software\PreciseExcelComparator\v1.1\PreciseExcelComparator_PEC"
::echo %PECfolder%
PushD "%PECfolder%" || ( Echo can't locate %PECfolder% &Pause&Exit /B 0)

FOR /f "delims=" %%f IN ('dir /b /s "%MasterFolder1%\*.xls"') do (
::echo %MasterFolder1%
    If exist "%Masterfolder2%\%%~nxf" (
        call java -jar "C:\SunilKK-Latitude-E6430s\SunilKK\Software\PreciseExcelComparator\v1.1\PreciseExcelComparator_PEC\PEC.jar" "%%~ff" "%Masterfolder2%\%%~nxf" 0 0
    ) else (
        echo No pendant to "%%f" found in "%MasterFolder2%" >> %"LogFile%"
    )
)
pause
Posted
Updated 10-Sep-19 22:47pm
v2
Comments
Maciej Los 11-Sep-19 2:34am
   
And your question is...
Sunil KK 11-Sep-19 2:52am
   
Hello Maciej Los, i didn't find your answer?
Maciej Los 11-Sep-19 2:53am
   
Because i haven't posted it.
Sunil KK 11-Sep-19 3:00am
   
Ok, first of all thanks for showing interest on my query. Well, can you please confirm what is wrong i am doing in my code.
Maciej Los 11-Sep-19 3:11am
   
I have no idea what is wrong... You told us what your code is doing right now and you didn't tell what you are trying to achieve. Can you provide more details about your issue?
Sunil KK 11-Sep-19 3:20am
   
ok, i want this code to compare one report with another report. Eg. Suppose in folder1 we have abc_report1 and folder2 xyz_report1. So by using call java -jar it should compare these two reports. the parameter should be like call java - jar "C:\softwar....\PEC.jar" "C:\Folder1\abc_report1" "C:\Folder2\xyz_report1". But because i am checking if exist condition so this will run when we will have same report name in both the folder. However, in my case as i mentioned earlier, report name is not same only template is same.
Maciej Los 11-Sep-19 3:31am
   
Waht is pec.jar software? I haven't hear about that.
Still you didn't explain what you mean by compare. Are you trying to check if both files contain the same values?
Sunil KK 11-Sep-19 3:43am
   
pec.jar is our internal tool that compares two excel file. In this tool we have two parameter and in both the parameter we have to pass report names. This tool helps when we have less number of reports, but if we have to compare 100 of reports than user have to run this tool 100 time. That's why i created a batch file that will pick one by one reports in both the folder and pass the parameter for this Tool.

For report compare, this tool will compare the report cell by cell with value, i just want to give the parameter and parameter would be like "FolderName1\reportname1.xls" "FolderName2\reportname1.xls". Again, report name in both the folder is not same. Let me give the sample name of the reports which i have in two different folders :

Folder1
C:\Users\kaushasu\Desktop\Test1\F12162-AE EBA2.9-XLS-QA-BF - AE CRD F32.01

Folder2
C:\Users\kaushasu\Desktop\Test2\F12162-AE EBA2.9-XLS-QA - AE CRD F32.01

Sunil KK 11-Sep-19 4:23am
   
I hope i explained what i want to do
Maciej Los 11-Sep-19 4:41am
   
As per me... No. I don't know what pec.jar software returns.
Sunil KK 11-Sep-19 4:43am
   
pec.jar returns the compared result in excel format. this tool colored the cell in yellow if the value is mismatched with the comparison sheet.

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

Seems, your code is incomplete, because you have to compare each file in folder1 with all files in folder2. See: Cartesian product[^].
In pseudo-code:
Set "PECfolder=C:\SunilKK-Latitude-E6430s\SunilKK\Software\PreciseExcelComparator\v1.1\PreciseExcelComparator_PEC"
Set "PECfile=pec.jar"

REM ... some lines were skipped

FOR /f "delims=" %%f1 IN ("%Masterfolder1%\*.xls") DO (
    FOR /f "delims=" %%f2 IN ("%Masterfolder2%\*.xls") DO (
        REM compare last 5 characters + ".xls" of file
        IF %f1:~-9% == %f2:~-9%
                call java -jar %PECfolder%\%PECFile% %f1% %f2% 0 0
    )
)
   
v3
Comments
Sunil KK 11-Sep-19 4:55am
   
Hello Maciej,

I don't have to compare each file in folder1 with all files in folder2

I have to compare file like report1 to report1 and report2=report2, report3=report3. As report name is not same in both the folder. So, i am assuming that in both the folders the sequence of file is same.
Maciej Los 11-Sep-19 5:00am
   
But you mentioned that the name of files can differ each other. The common part of files is a template. So, i guess that you have to compare each file in one folder to all files in second folder. If the part of file name is the same in both folders, you have to add another condition, but logic is still the same.
Sunil KK 11-Sep-19 5:07am
   
Yes, as i mentioned in comments i have file name in this manner :

Folder1
C:\Users\kaushasu\Desktop\Test1\F12162-AE EBA2.9-XLS-QA-BF - AE CRD F32.01

Folder2
C:\Users\kaushasu\Desktop\Test2\F12162-AE EBA2.9-XLS-QA - AE CRD F32.01

that's why i am seeking for a help to get the file name for PEC.jar as a parameters
Sunil KK 11-Sep-19 5:11am
   
can you just help me to get the filenames in both the folders one by one and executed with pec.jar, it would be like :

call java -jar C:\PreciseExcelComparator\PEC.jar "C:\Users\kaushasu\Desktop\Test1\F12162-AE EBA2.9-XLS-QA-BF - AE CRD F32.01.xls" "C:\Users\kaushasu\Desktop\Test2\F12162-AE EBA2.9-XLS-QA - AE CRD F32.01" 0 0

call java -jar C:\PreciseExcelComparator\PEC.jar "C:\Users\kaushasu\Desktop\Test1\F12162-AE EBA2.9-XLS-QA-BF - AE CRD F32.02.xls" "C:\Users\kaushasu\Desktop\Test2\F12162-AE EBA2.9-XLS-QA - AE CRD F32.02" 0 0

call java -jar C:\PreciseExcelComparator\PEC.jar "C:\Users\kaushasu\Desktop\Test1\F12162-AE EBA2.9-XLS-QA-BF - AE CRD F32.03.xls" "C:\Users\kaushasu\Desktop\Test2\F12162-AE EBA2.9-XLS-QA - AE CRD F32.03" 0 0

this should be run in the loop.
Sunil KK 11-Sep-19 7:17am
   
Hello Maciej Los, am i able to explain ?
Maciej Los 11-Sep-19 7:41am
   
Seems you want to compare last 5 characters of file names. If they match, you want to call pec.jar file with arguments.
Check this: variable substring - Windows CMD - SS64.com[^]. This might help you to compare last 5 characters in the file names.
[EDIT]
See updated answer.
Sunil KK 11-Sep-19 9:32am
   
Thanks Maciej for the link. Well, i want to create generic batch file that can be used by another user as well. Just help me to add another loop in my code to get the file name from folder2. rest i will do it. Because i created this code by taking reference of many codes using google :). to be honest I am not so expert in batch coding.
Maciej Los 11-Sep-19 9:42am
   
I'm not an expert too. I've been playing with batch files several years ago. Now, i prefer playing with c#...

If my answer was helpful, can you accept it (green button - formally to remove your question from unanswered list)?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100