1

I need help comparing two Excel files in Powershell.

I have an Excel-file which contains 6 000 rows and 4-5 columns with headers:
"Number" "Name" "Mobile data".
Let's call it: $Services

Now, I want to compare that file with other Excel-files. For example:
one file containing 50 rows with header columns: "Number", "Name", etc.
Let's call it $Department

The important thing is that in $Services, it contains more important columns like "Mobile data",
so my mission is to compare column: "Number" from $Services with column "Number" from each other Excel file.
Then if they match, write "the whole row" from $Services

I'm not that familiar with Excel, so I thought, this should be possible to do in Powershell.
I'm novice in Powershell, so I only know basic stuff. I'm not that familiar with pscustomobject and param.

Anyway, what I tried to do was to first declare them in variables with ImportExcel:

$Services = Import-Excel -Path 'C:\Users\*.xlsx'
$Department = Import-Excel -Path 'C:\Users\*.xlsx'

Then I made a foreach statement: 

foreach ($Service in $Services) {
   if (($Service).Number -like ($Department).Number) 
   {Write-Output "$Service"}
}

The problem with this is that it is collecting all empty columns from ($Services).Number and writing the output of each row in $Services.
I tried to add a nullorEmpty to $Department, if the .Number is empty, but it didn't make any difference.
I also tried to add that if the row is empty in .Number, add "1234", but still it collects all .Number that is empty in $Services.

I also tried to do a:
$Services | ForEach-Object -Process {if (($_).Number -match ($Department).Number) {Write-Output $_}} But it didn't match any. When I tried -notmatch it took all.

I don't know but it seems that I have to convert the files to objects, like the columns to object so each string becomes an object. But right now my head is just spinning and I need some hints on where I can start with this.

Isaac Touché
  • 13
  • 1
  • 4
  • I'd use VLOOKUP in EXCEL. – WEBjuju Nov 19 '19 at 22:23
  • If you do not actually want to join the tables in Excel; [-Like](https://docs.microsoft.com/powershell/module/microsoft.powershell.core/about/about_comparison_operators?view=powershell-6#-like) is unlikely the correct operator for this. Beside you probably want to merge the columns and discern them in a single table. To do this, you might want to look into the answers of e.g. [In Powershell, what's the best way to join two tables into one?](https://stackoverflow.com/q/1848821/1701026). To use the `Join-Object` described in there: `$Service | Join $Department -On Number -Discern *1,*2 – iRon Nov 20 '19 at 07:10

2 Answers2

0

I would recommend downloading the Module ImportExcel from the PSGallery.

Import-Excel can easily import your Excel sheet(s) to rows of objects, especially if your sheets are 'clean', i.e., only contain (optional) headers and data rows.

Simply import the cells to PowerShell objects and use Compare-Object to discover differences.

EDIT (after reading the additional questions by poster in the comments):

To compare using specific properties you'll need to add these to the Compare-Object parameters.

Using a trivial "PSCustomObject" to create a simple set of objects to show this idea it might look like this:

$l = 1..4 | ForEach-Object { [pscustomobject]@{a=$_;b=$_+1} }
$r = 1,2,4,5 | ForEach-Object { [pscustomobject]@{a=$_;b=$_+1} }
compare-object $l $r -Property B

B SideIndicator
- -------------
6 =>
4 <=

You may also compare multiple properties this way:

compare-object $l $r -Property A,B

A B SideIndicator 
- - -------------
5 6 =>
3 4 <=

FYI: I find myself typing "Get-Command -Syntax SomeCommand" so often every day that I just made a function "Get-Syntax" (which also expands aliases) and then aliased this to simply "syn".

90% of the time once you understand the structure of PowerShell cmdlets (at least well-written ones) there is no need to even look at the full help -- the "syntax" blocks are sufficient.

Until then, type HELP (Get-Help) a lot -- 100+ times per day. :)

HerbM
  • 521
  • 6
  • 14
  • Oh! I forgot to write that! I'm using Import-Excel so these variables: $Services and $Department I declared them first in my script with: $Services = Import-Excel -patch "C:/users/*" – Isaac Touché Nov 20 '19 at 06:36
  • It's pretty tough to answer the question you did NOT ask. :) It's really not an Excel question then. Import both sheets to objects and use the Compare-Object cmdlet. – HerbM Nov 20 '19 at 20:20
  • But after importing them like variables: $Services = Import-Excel -path "" Then when I do a: "Compare-Object $Services $Department" I get no results of course. Because I want to compare the column: Numbers in each sheet. So if I do a: "Compare-Object ($Services).Numbers ($Department).Numbers" I get error: "Cannot bind argument to parameter 'ReferenceObject' because it is null." It's the same if I declare a variabel before like: "$ServicesNumb = ($Services).Numbers" So the problem is that each column, when I declared them, Powershell sees them as null. – Isaac Touché Nov 21 '19 at 14:32
  • See my edited version answer above, about "To compare using specific properties you'll need to add these to the Compare-Object parameters." I had trouble following your last comment due to StackOverflow removing most of the extra whitespace. If this doesn't answer your question try editing your original to ask a more specific question and I'll try to help more. – HerbM Nov 25 '19 at 10:20
0

So the solution for my whole problem was to add -PassThru.
Because my mission was to compare the numbers of the two Excel-files, select the numbers that equals and then take all the properties from one file. So my script became like this: 

    $Compare = Compare-Object $Services $Department -Property Numbers -IncludeEqual -ExcludeDifferent -PassThru 
        $Compare | Export-Excel -Path 'C:\Users\*

But I wonder, -PassThru sends all the objects from ReferenceObject, how can I send all the objects from DifferenceObject?

Isaac Touché
  • 13
  • 1
  • 4