none
Powershell script to compare one file to another.

    Question

  • Hi Guys,

    I'm new to Powershell and have a scripting issue that is bugging me. I went through the tutorials but I don't really seem to be able to find the answer.

    Ok, I need a script that compares one text file of IP addresses against a large excel file of IP addresses.

    example:

    text file                                                                                                                  excel file                                                     

    134.135.136.137                                                                                             134.135.136.137

    144.145.146.147                                                                                               144.145.146.147

    155.156.157.158  let's say no match in the excel file here                           178.179.180.181

    Now obviously there would be a lot more data than that. I understand how to ouput the differences in each file and then output it into a third file but that's not what I want. I need it to take the IP addresses in the text file and then compare against the excel file and then throw it into a seperate third file. It does not matter if all the IP address in the excel file match with the text file. Only if there are any in the text file that do not match with the excel file. Any help anybody could give would be gretaly appriciated. I really need help cause it's getting fustrating. :) 

    Friday, November 15, 2013 8:49 PM

Answers

  • Here is a possible direction.

    Import text into Ecel and use Excel "Compare"  to match files.

    OR

    Export Excel to CSV, import both into PowerSHell and use PowerShell Compare-Object.


    ¯\_(ツ)_/¯

    Saturday, November 16, 2013 4:21 AM

All replies

  • PowerShell can compare two lists by using Compare-Object.

    However, why do you need PowerShell in your scenario? Just open your IP list in a text editor and paste it directly into your spreadsheet, then do your comparison in the spreadsheet application.

    Bill

    Friday, November 15, 2013 9:44 PM
  • Well for two reasons.

    1) I need something that is quick that will spit it out relatively fast so the output is there.

    2) I've found that Excel is not always completely reliable in these situations and I don't want to miss addresses, especially when dealing with thousands of ip addresses.




    • Edited by Kismet0_0 Friday, November 15, 2013 11:04 PM
    Friday, November 15, 2013 10:54 PM
  • Unfortunately, your problem description is far too vague for anyone to guess at what you need.

    It is possible to read data from an Excel spreadsheet into a PowerShell object, but this concept is a little advanced because it requires knowledge of the Excel object model. It would also require you to understand how PowerShell objects work and how to compare your lists using Compare-Object.

    If you're not familiar with either of these topics, then you're asking how to run before you've even started to walk.

    I recommend starting with the Learn link at the top of this forum. A forum is not well suited to basic training, and this also isn't the place to ask others to write custom code.

    Bill

    Friday, November 15, 2013 11:06 PM
  • Ok, thanks anyway. I'll look elsewhere.
    Saturday, November 16, 2013 12:03 AM
  • Let's start with a couple of questions.

    1.  Do you have any scripting experience?
    2.  Are you a technician or an end user?
    3.  What is the end result being sought for?


    ¯\_(ツ)_/¯

    Saturday, November 16, 2013 12:07 AM
  • 1) Yes, although not much with Powershell.

    2)I'm an in the IT profession, not an end user.

    3) The text file is the results of a Retina discovery scan, I need to compare to the excel file which is a list of known devices and see if there is anything not showing in inventory.

    Saturday, November 16, 2013 3:55 AM
  • Ok - so you are not a technician which helps me understand why you cannot easily explain what is needed.

    You are making a statement that I very vague.  You are asking to compare files of different types and now asking about inventory.  You mention scans of something you call "Retina".

    What you need to define is what is the relationship between the text file and the Excel file. 

    Are you teying to find out if everything in the text file is in the Excel?  The other way around?  Something different?

    Can you begin to see how ambiguous your request is.  You cannot write scripts or perform technical operations without clear statements of intent or required results.


    ¯\_(ツ)_/¯

    Saturday, November 16, 2013 4:19 AM
  • Here is a possible direction.

    Import text into Ecel and use Excel "Compare"  to match files.

    OR

    Export Excel to CSV, import both into PowerSHell and use PowerShell Compare-Object.


    ¯\_(ツ)_/¯

    Saturday, November 16, 2013 4:21 AM
  • There is actually a third and preferred option but it would be much harder for you.

    Open Excel in PowerShell and lookup each line in the text file.  This requires even more different scripted subsystems.  Ii could actually be a pretty good learning exercise.


    ¯\_(ツ)_/¯

    Saturday, November 16, 2013 4:24 AM
  • Export Excel to CSV, import both into PowerSHell and use PowerShell Compare-Object.


    ¯\_(ツ)_/¯

    That's going to be the fastest and simplest solution, if you don't mind the manual step of exporting the spreadsheet to a CSV file first before you run the script.
    Saturday, November 16, 2013 5:04 AM
  • Yes, I am trying to find out if everything in the text file is in the excel file. That's exactly right, sorry I wasn't trying to be vague or ambiguous. It's not hard to find differences between two files that's why I didn't think this added step would be difficult question. I'll look into the compare-object model. Thanks for the input.
    Saturday, November 16, 2013 12:45 PM
  • Thanks I think that is the way to go.
    Saturday, November 16, 2013 12:47 PM
  • Yes, I am trying to find out if everything in the text file is in the excel file. That's exactly right, sorry I wasn't trying to be vague or ambiguous. It's not hard to find differences between two files that's why I didn't think this added step would be difficult question. I'll look into the compare-object model. Thanks for the input.
    You could export the Excel data to a csv file, then build a dictionary with its contents. Your scripting task is then reduced to checking if each text file element is present in the dictionary.
    Saturday, November 16, 2013 12:51 PM
  • Why use  dictionary.  A CSV file work just as well.  Excel can be exported to CSV very easily.

    1. Open Excel in Powershell
    2. Export to temporary CSV (one line)
    3. Import CSv
    4. Get-Content text file
    5. FOr Each line in text scan CSV.
          if($csvfile|?{$_.column -eq $line}
    6.  Out put results.

    That is all.

    I do not think there is a faster way although we can just use the "Lookup" method of Excel directly .

    $wb=$xl.Workbooks.Add(<fulenmae>)
    cat textfile |
         if($xl.Lookup(....)){ ...log results }

    that seems easier.


    ¯\_(ツ)_/¯

    Saturday, November 16, 2013 1:53 PM
  • Why use  dictionary.  A CSV file work just as well.  Excel can be exported to CSV very easily.

    1. Open Excel in Powershell
    2. Export to temporary CSV (one line)
    3. Import CSv
    4. Get-Content text file
    5. FOr Each line in text scan CSV.
          if($csvfile|?{$_.column -eq $line}
    6.  Out put results.


    ¯\_(ツ)_/¯

    That would be very slow, with large files. Building a dictionary would be better, but simplest is what you said earlier: Compare-Object. You just have to create arrays of objects from both source files (probably using Import-Csv, even for the plain text file).

    Rather than enumerating the entire CSV file once for every record in the text file, Compare-Object sorts both collections then enumerates them together once.

    Saturday, November 16, 2013 2:10 PM
  • Sorry - it is "Find" and not "Lookup". "Lookup" is a macro function.

    Here is a demo.

    $xl=New-Object -ComObject Excel.application
    $wb=$xl.Workbooks.Add('c:\scripts\demo-find.xlsx')
    $ws=$wb.Worksheets.Item(1)
    cat demo-iptest.txt |
         ForEach-Object{
              if($ws.UsedRange.Find($_)){
                   "$_ Found!"
              }else{
                   "$_ Not Found!"
              }
         }
    
    

    The Range can be tailored however needed.


    ¯\_(ツ)_/¯

    Saturday, November 16, 2013 2:36 PM
  • Agreed - the "Find" method of Excel is probably the fastest for very large  files.  Excel columns are indexed and finds are fast as long as we are searching for the whole column value and not a partial match or a computed match.

    If the Excel sheet is very large and this is done often then a database would likely be the best solution.


    ¯\_(ツ)_/¯

    Saturday, November 16, 2013 3:01 PM
  • Oh thank you for that, I'm going to play around with it and test it later and see what I can come up with. 
    Saturday, November 16, 2013 4:24 PM
  • There are very good learning resources linked at the top of this page.

    ¯\_(ツ)_/¯

    Saturday, November 16, 2013 4:43 PM