Vbs script that can check 2 folders that has identical csv files and compare colum "C" and get just the file names that has the exact data in the colum in both files.

bsharath
bsharath used Ask the Experts™
on
Hi,
Vbs script that can check 2 folders that has identical csv files and compare colum "C" and get just the file names that has the exact data in the colum in both files.

Regards
Sharath
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Well , use this sample code , you will have to do some modifications, but this is the backbone of your program.

http://groups.google.com/group/microsoft.public.scripting.vbscript/browse_thread/thread/165fceb18de9415b 

Author

Commented:
Thanks
Can you help me with full solution please...
Meir RivkinFull stack Software Engineer

Commented:
can u post one of the csv for example?

Author

Commented:
Attached below
1.d.csv
Meir RivkinFull stack Software Engineer

Commented:
there's no Column C in the csv file
Meir RivkinFull stack Software Engineer

Commented:
the files should have also the same number of rows?

Author

Commented:
Sorry it "E"

I will need to change later which i shall do
No rows can be an number
The data in each cell in both csv files has to be identical
Case should not be an issue but content has to be same each cell
Sa in row 1 i have task121
then in the other file row1 should be task121
Meir RivkinFull stack Software Engineer

Commented:
ok got it...
Meir RivkinFull stack Software Engineer

Commented:
the result is written to log file.
the script goes like this:
loop through first folder, check if same csv file exists in other folder.
then compare the 2 csv files.
if both have the same number of rows and both have at least 5 columns and all data from the 5th column is identical, then log them into the output file.
const FOLDER1="c:\temp\f1"
const FOLDER2="c:\temp\f2"
const LOG_FILE ="c:\temp\output.log"
Set fsoLog = CreateObject("Scripting.FileSystemObject")
set objLog = fsoLog.CreateTextFile(LOG_FILE,2)
objLog.WriteLine "The following files has identical data in column 5: " 

dim fileName,compFileName
strComputer = "."
Set objWMIService = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
	
Set FileList = objWMIService.ExecQuery("ASSOCIATORS OF {Win32_Directory.Name='" & FOLDER1 & "'} Where ResultClass = CIM_DataFile")
	
For Each objFile In FileList
    If objFile.Extension = "csv" Then
        fileName = objFile.Drive & objFile.Path & objFile.FileName & "." & "csv"
		
		compFileName = FOLDER2 & "\" & objFile.FileName & "." & "csv"
		'WScript.Echo
		'WScript.Echo "*** Compare " & fileName & " with  " & compFileName & " ***"
		if IsFileExists(compFileName) then
			CompareCsvFile fileName, compFileName
		else
			'WScript.Echo compFileName & " is missing..."
		end if
		'WScript.Echo "*** End Compare ***"
    End If
Next

objLog.Close
set objLog = Nothing
set fsoLog = Nothing

function IsFileExists(fileName)
	DIM fso
	Set fso = CreateObject("Scripting.FileSystemObject")

	If (fso.FileExists(fileName)) Then
		IsFileExists=true
	Else
		IsFileExists=false
	End If
End function

sub CompareCsvFile(fileName, compFileName)
	dim fs,objTextFile,data1,data2
	set fs = CreateObject("Scripting.FileSystemObject")
	dim arrStr,col_values1,col_values2,arr1,arr2
	set objTextFile = fs.OpenTextFile(fileName)
	
	'read column 5 data from first csv file
	Do while NOT objTextFile.AtEndOfStream
		arrStr = split(objTextFile.ReadLine,",")
		if col_values1 = "" then
			col_values1 = arrStr(4)
		else
			col_values1 = col_values1 & "," & arrStr(4)
		end if
	Loop
	objTextFile.Close
	
	'read column 5 data from second csv file
	set objTextFile = fs.OpenTextFile(compFileName)
	Do while NOT objTextFile.AtEndOfStream
		arrStr = split(objTextFile.ReadLine,",")
		if col_values2 = "" then
			col_values2 = arrStr(4)
		else
			col_values2 = col_values2 & "," & arrStr(4)
		end if
	Loop
	
	'WScript.Echo "col_values1: " & col_values1
	'WScript.Echo "col_values2: " & col_values2
	
	objTextFile.Close
	arr1 = split(col_values1,",")
	arr2 = split(col_values2,",")
	
	'check if has the same number of rows
	if UBound(arr1) <> UBound(arr2) then
		exit sub
	end if

	For count = 0 to UBound(arr1)
		'check if data is identical
		data1=arr1(count)
		data2=arr2(count)
		if data1 <> data2 then
			exit sub
		end if
	Next
	
	set objTextFile = Nothing
	set fs = Nothing
	
	objLog.WriteLine fileName
	objLog.WriteLine compFileName
	objLog.WriteLine
End sub

Open in new window

Author

Commented:
Thanks
>>if both have the same number of rows and both have at least 5 columns and all data from the 5th column is identical, then log them into the output file.
Colum E cell has to match the other csv identical row cell. Is this right

Author

Commented:
I get this

---------------------------
Windows Script Host
---------------------------
Script:      H:\Compare.vbs
Line:      67
Char:      4
Error:      Subscript out of range: '[number: 4]'
Code:      800A0009
Source:       Microsoft VBScript runtime error

---------------------------
OK  
---------------------------

Author

Commented:
Can the script log the files that are not identical also?
Full stack Software Engineer
Commented:
check it:
const FOLDER1 = "c:\temp\f1"
const FOLDER2 = "c:\temp\f2"
const LOG_FILE = "c:\temp\output.log"
Set fsoLog = CreateObject("Scripting.FileSystemObject")
set objLog = fsoLog.CreateTextFile(LOG_FILE,2)

dim fileName,compFileName
strComputer = "."
Set objWMIService = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
	
Set FileList = objWMIService.ExecQuery("ASSOCIATORS OF {Win32_Directory.Name='" & FOLDER1 & "'} Where ResultClass = CIM_DataFile")
	
For Each objFile In FileList
    If objFile.Extension = "csv" Then
        fileName = objFile.Drive & objFile.Path & objFile.FileName & "." & "csv"
		compFileName = FOLDER2 & "\" & objFile.FileName & "." & "csv"
		if IsFileExists(compFileName) then
			CompareCsvFile fileName, compFileName
		end if

	End If
Next

objLog.Close
set objLog = Nothing
set fsoLog = Nothing

function IsFileExists(fileName)
	DIM fso
	Set fso = CreateObject("Scripting.FileSystemObject")

	If (fso.FileExists(fileName)) Then
		IsFileExists=true
	Else
		IsFileExists=false
	End If
End function

sub CompareCsvFile(fileName, compFileName)
	dim fs,objTextFile,data1,data2
	set fs = CreateObject("Scripting.FileSystemObject")
	dim arrStr,col_values1,col_values2,arr1,arr2
	set objTextFile = fs.OpenTextFile(fileName)
	
	'read column 5 data from first csv file
	Do while NOT objTextFile.AtEndOfStream
		arrStr = split(objTextFile.ReadLine,",")
		if  UBound(arrStr) < 4 then
			outputLogResult fileName, compFileName, false
			exit sub
		end if
		if col_values1 = "" then
			col_values1 = arrStr(4)
		else
			col_values1 = col_values1 & "," & arrStr(4)
		end if
	Loop
	objTextFile.Close
	
	'read column 5 data from second csv file
	set objTextFile = fs.OpenTextFile(compFileName)
	Do while NOT objTextFile.AtEndOfStream
		arrStr = split(objTextFile.ReadLine,",")
		if  UBound(arrStr) < 4 then
			outputLogResult fileName, compFileName, false
			exit sub
		end if
		if col_values2 = "" then
			col_values2 = arrStr(4)
		else
			col_values2 = col_values2 & "," & arrStr(4)
		end if
	Loop
	
	objTextFile.Close
	arr1 = split(col_values1,",")
	arr2 = split(col_values2,",")
	
	'check if has the same number of rows
	if UBound(arr1) <> UBound(arr2) then
		outputLogResult fileName, compFileName, false
		exit sub
	end if

	For count = 0 to UBound(arr1)
		'check if data is identical
		data1=arr1(count)
		data2=arr2(count)
		if data1 <> data2 then
			outputLogResult fileName, compFileName, false
			exit sub
		end if
	Next
	
	set objTextFile = Nothing
	set fs = Nothing
	
	outputLogResult fileName, compFileName, true
End sub
  
 sub outputLogResult(fileName, compFileName, result)
 
	 if result = true then
		objLog.WriteLine "Identical files:" 
	 else
		objLog.WriteLine "Unidentical files:" 
	 end if

	objLog.WriteLine fileName
	objLog.WriteLine compFileName
	objLog.WriteLine
end sub

Open in new window

Author

Commented:
Thank you

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial