none
Macro to compare CSV and Excel file RRS feed

  • Question

  • Team,

    Do we have any macro to compare CSV and Excel file.

    1) In Excel we should have two text boxes asking the user to select the files from the path where they have Stored

    2) First Text is for CSV file

    3) Second Text box is for Excel file

    4) We Should have Compare button that should run the Macro and Show the Differences.

    Thanks!
    Kiran
    Wednesday, February 11, 2015 1:40 AM

Answers

  • Hi

    Based on my understanding, we need to convert the 2 files into the same format before comparing two different formats files. 

    Here are the options:

    1. Convert the CSV file and Excel file into 2-dim Arrays, then compare them, about how to load a CSV file into VBA array, you can search it from internet or ask a solution in VBA forum.

    VBA forum: http://social.msdn.microsoft.com/Forums/en-US/home?forum=isvvba

    2. Also you can import CSV file to Excel sheet, and then compare them.

    Here is the sample code for your reference:

    ‘import csv file

    Sub InputCSV()

        Dim Wb As Workbook

        Dim Arr

        Set Wb = GetObject(Application.GetOpenFilename("csv file,*.csv", , "please choose a csv file", , False))

        Arr = Wb.ActiveSheet.Range("A1").CurrentRegion

        Wb.Close False

        Range("A1").Resize(UBound(Arr), UBound(Arr, 2)) = Arr

    End Sub

    ‘compare sheet

    Sub CompareTable()

    Dim tem, tem1 As String

    Dim text1, text2 As String

    Dim i As Integer, hang1 As Long, hang2 As Long, lie As Long, maxhang As Long, maxlie As Long

     Sheets("Sheet1").Select

      Columns("A:A").Select

      With Selection.Interior

        .Pattern = xlNone

        .TintAndShade = 0

        .PatternTintAndShade = 0

      End With

      Range("A1").Select

     

    Sheets("Sheet2").Select

    Rows("1:4").Select

      With Selection.Interior

        .Pattern = xlNone

        .TintAndShade = 0

        .PatternTintAndShade = 0

      End With

      Range("A1").Select

      

    MaxRow = 250

    MaxColumn = 40

    For hang1 = 2 To maxhang

       Dim a As Integer

       a = 0

       tem = Sheets(1).Cells(hang1, 1)

       For hang2 = 1 To maxhang

          tem1 = Sheets(2).Cells(hang2, 1)

         If tem1 = tem Then

          a = 1

          Sheets(2).Cells(hang2, 1).Interior.ColorIndex = 6

          For lie = 1 To maxlie

            text1 = Sheets(1).Cells(hang1, lie)

            text2 = Sheets(2).Cells(hang2, lie)

            If text1 <> text2 Then

              Sheets(2).Cells(hang2, lie).Interior.ColorIndex = 8

            End If

          Next

         End If

       Next

       If a = 0 Then

         Sheets(1).Cells(hang1, 1).Interior.ColorIndex = 5

       End If

    Next

    Hope this will help.

    Best Regards

    Lan


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by L.HlModerator Tuesday, February 24, 2015 3:37 AM
    Friday, February 13, 2015 9:14 AM
    Moderator

All replies

  • Hi

    Based on my understanding, we need to convert the 2 files into the same format before comparing two different formats files. 

    Here are the options:

    1. Convert the CSV file and Excel file into 2-dim Arrays, then compare them, about how to load a CSV file into VBA array, you can search it from internet or ask a solution in VBA forum.

    VBA forum: http://social.msdn.microsoft.com/Forums/en-US/home?forum=isvvba

    2. Also you can import CSV file to Excel sheet, and then compare them.

    Here is the sample code for your reference:

    ‘import csv file

    Sub InputCSV()

        Dim Wb As Workbook

        Dim Arr

        Set Wb = GetObject(Application.GetOpenFilename("csv file,*.csv", , "please choose a csv file", , False))

        Arr = Wb.ActiveSheet.Range("A1").CurrentRegion

        Wb.Close False

        Range("A1").Resize(UBound(Arr), UBound(Arr, 2)) = Arr

    End Sub

    ‘compare sheet

    Sub CompareTable()

    Dim tem, tem1 As String

    Dim text1, text2 As String

    Dim i As Integer, hang1 As Long, hang2 As Long, lie As Long, maxhang As Long, maxlie As Long

     Sheets("Sheet1").Select

      Columns("A:A").Select

      With Selection.Interior

        .Pattern = xlNone

        .TintAndShade = 0

        .PatternTintAndShade = 0

      End With

      Range("A1").Select

     

    Sheets("Sheet2").Select

    Rows("1:4").Select

      With Selection.Interior

        .Pattern = xlNone

        .TintAndShade = 0

        .PatternTintAndShade = 0

      End With

      Range("A1").Select

      

    MaxRow = 250

    MaxColumn = 40

    For hang1 = 2 To maxhang

       Dim a As Integer

       a = 0

       tem = Sheets(1).Cells(hang1, 1)

       For hang2 = 1 To maxhang

          tem1 = Sheets(2).Cells(hang2, 1)

         If tem1 = tem Then

          a = 1

          Sheets(2).Cells(hang2, 1).Interior.ColorIndex = 6

          For lie = 1 To maxlie

            text1 = Sheets(1).Cells(hang1, lie)

            text2 = Sheets(2).Cells(hang2, lie)

            If text1 <> text2 Then

              Sheets(2).Cells(hang2, lie).Interior.ColorIndex = 8

            End If

          Next

         End If

       Next

       If a = 0 Then

         Sheets(1).Cells(hang1, 1).Interior.ColorIndex = 5

       End If

    Next

    Hope this will help.

    Best Regards

    Lan


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by L.HlModerator Tuesday, February 24, 2015 3:37 AM
    Friday, February 13, 2015 9:14 AM
    Moderator
  • Hi,

    1) import .csv file.

    2) import .xls* file.

    3) set workbook of .csv file

    4)set workbook of .xls* file

    5) now you can code and compare all the data using wbook1.sheets("csvsheet") and wbook2.shhets("xlssheet")

    Thanks

    Shamim

    Friday, February 13, 2015 9:26 AM
  • As you can see, it takes a bit of work to do this because you are dealing with two different files and two different formats.  This is not a VBA solution, by any means, but it's free and it's very powerful.  It allows you to compare all kinds of different files.

    http://download.cnet.com/Beyond-Compare/3000-2242_4-10015731.html


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Friday, February 20, 2015 6:32 PM