Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl: the Markov chain saw

Comparing two Excel spread sheets

by Win (Novice)
on Apr 21, 2006 at 08:32 UTC ( #544814=perlquestion: print w/replies, xml ) Need Help??
Win has asked for the wisdom of the Perl Monks concerning the following question:

Dear Monks,

Is there anyway of comparing two spread sheets short of going through each cell and comparing each cell? I am really looking for a VBA command that I can activate using Perl. Not really sure if it is a Perl question though.

Replies are listed 'Best First'.
Re: Comparing two Excel spread sheets
by marto (Bishop) on Apr 21, 2006 at 08:42 UTC

    Thanks (again) for not using Super Search before posting a question.
    compare two Excel spreadsheets is returned by a search for 'compare excel'. Also A few times in the past I have pointed out to you guides for converting VBA code to Perl, calling (MS Office based) macros from Perl and using Win32::OLE to drive MS Office applications. Please start using the advice you are given, and the Super Search feature of this site.

Re: Comparing two Excel spread sheets
by rinceWind (Monsignor) on Apr 21, 2006 at 08:41 UTC

    I'll give you a Perl answer though :).

    Use a combination of the modules Spreadsheet::ParseExcel and Data::Dumper. Parse both spreadsheets, serialise the results with Data::Dumper and diff the results (though you could use Array::Compare as an alternative).


    Oh Lord, won’t you burn me a Knoppix CD ?
    My friends all rate Windows, I must disagree.
    Your powers of persuasion will set them all free,
    So oh Lord, won’t you burn me a Knoppix CD ?
    (Missquoting Janis Joplin)

      I have to be honest. I'm not really looking for a Perl solution I just want a VBA snippet that I can either activate in Perl or stand alone using VBA.

        Is there any reason you can't use google? That at least would have found you somewhere more appropriate place to ask this question.


        Personally I would dump them to csv, then use Text::CSV_XS to read them record by record and compare them. I think that two csv files can carry the same data without actually being exactly the same, although if they are produced by the same app, then one could hope this not to be the case. But I wouldn't rely on such an assumption...

Re: Comparing two Excel spread sheets
by planetscape (Chancellor) on Apr 21, 2006 at 11:52 UTC

    Here's what I use to (semi-)intelligently export all worksheets in a workbook to CSV files. In VBA:

    Sub BulkCSVExport() ' Export all worksheets to .CSV files. ' Surround text (not numbers!) with double quotes. ' Handle text that contains double quotes, as in: 18" X 11" X 11 +" Dim DestFile As String Dim FileNum As Integer Dim ColumnCount As Integer Dim RowCount As Integer Dim st As Integer Dim strTemp As String Dim wbk As Workbook Dim sht As Worksheet Dim intSheetCount As Integer Application.ScreenUpdating = False Set wbk = Application.ActiveWorkbook For intSheetCount = 1 To wbk.Sheets.Count Set sht = wbk.Sheets.Item(intSheetCount) sht.Activate DestFile = wbk.Name & "." & sht.Name & ".CSV" FileNum = FreeFile() On Error Resume Next Open DestFile For Output As #FileNum If Err <> 0 Then MsgBox "Cannot open filename " & DestFile End End If On Error GoTo 0 ActiveSheet.UsedRange.Select For RowCount = 1 To Selection.Rows.Count For ColumnCount = 1 To Selection.Columns.Count If IsNumeric(Selection.Cells(RowCount, ColumnCount +)) Then Print #FileNum, Selection.Cells(RowCount, _ ColumnCount).Value; Else ' strTemp = Selection.Cells(RowCount, ColumnCoun +t).Text ' Fix double quotes, lest they confuse the .CS +V format If InStr(strTemp, CStr(Chr$(34))) <> 0 Then st% = 1 While st% <= Len(strTemp) And InStr(st%, st +rTemp, CStr(Chr$(34))) > 0 st% = InStr(st%, strTemp, CStr(Chr$(34)) +) strTemp = Left$(strTemp, st%) & CStr(Chr +$(34)) & Mid$(strTemp, st% + 1) st% = st% + 2 Wend End If ' Print #FileNum, CStr(Chr$(34)) & strTemp & CSt +r(Chr$(34)); End If If ColumnCount = Selection.Columns.Count Then Print #FileNum, Else Print #FileNum, ","; End If Next ColumnCount Next RowCount Close #FileNum Next Application.ScreenUpdating = True End Sub

    Modifications, calling from Perl, and diffing the output are all left as exercises for the reader.

Re: Comparing two Excel spread sheets
by simon.proctor (Vicar) on Apr 21, 2006 at 09:01 UTC
    Well if you only have two sheets, one in each. You could just save each out as csv's and do a diff. Any diffs and they aren't the same.

    But that iimplies that you want to do a like for like comparison on data and data position.
      That is exactly what I want to do. Would I use the diff command with MS DOS or something else? And can anyone guide me as to what the best directory to save these files to in Windows would be? Given that the excel spreadsheet that performs this task could be on anyone’s machine in our group (they all have Perl installed now - by the way). Some people use XP and others use Windows 2000.

      Update : I think that I can use the COMP command that MS DOS understands.

        I'm not really sure about COMP; diff is a standard unix tool, you will find it in the UnxUtils collection. However also take into account the remark I made here: it may be relevant...

        All in all using perl with the suitable module to read the csv files and compare them record by record (stopping at the first discordance) will be more robust and shouldn't be terribly expensive from the computational pov.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://544814]
Approved by marto
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others rifling through the Monastery: (8)
As of 2018-03-19 18:36 GMT
Find Nodes?
    Voting Booth?
    When I think of a mole I think of:

    Results (246 votes). Check out past polls.