Compare-WorkSheet - How do i see changes?

[DreamT80]

Hi,

Let's say that I have 2 Excel files that I want to compare. I do not focus on specific columns, rather I'd like to get a report of which row and column that has changed, and the "before" and "after" values.

I can do as such:

$differences = Compare-WorkSheet -ReferenceFile $file1 -DifferenceFile $file2 -WorkSheetName $worksheetName $differences
which will produce the output

`_Side : =>
_File : 2.xlsx
_Sheet : Sheet1
_Row : 8
Region : South
State : Kansas
Units : 214
Price : 992.47

_Side : <=
_File : 1.xlsx
_Sheet : Sheet1
_Row : 8
Region : South
State : Kansas
Units : 214
Price : 992.48

_Side : =>
_File : 2.xlsx
_Sheet : Sheet1
_Row : 10
Region : South
State : Delaware
Units : 712
Price : 508.55

_Side : <=
_File : 1.xlsx
_Sheet : Sheet1
_Row : 10
Region : South
State : Delaware
Units : 713
Price : 508.55`

In the $differences object I can see which rows that are affected, and I can see the values of all columns, even if they aren't affected.

But, How can I use this to generate a difference report? I've tried to use Compare-Object, but with no luck.

What I want to produce is the following type of report:

`
Row: 8
Column: Price
Value before: 992.47
Value after: 992.48

Row: 10
Column: Units
Value before: 712
Value after: 713

`

I know that all the data is there, it's rather my limited PowerShell knowledge that is the problem here. I also know that I can markup the difference with BackgroundColor and FontColor, but that isn't what I'm after.

Thank you in advance!

[dfinke]

hmm, been a while since i looked at that

off the top of my head, a post process could further summarize and create that. You'd have to do the coding

Could you post the xlsx and short script that does the compare?

Do you have access to AI?

[DreamT80]

Thank you for the quick reply. My thought was that the module itself had functions/attributes for it so I didn't have to do the post-processing (As you see in my example I get the affected tow, but not the affected columns and values before/after).
I will probably figure out the post-processing, but I just wanted to know if this was built-in. Your module has helped me to 99%, so maybe I should be able to do the last percent on my own ;).

The code was basically this:
`$keyColumn = "Region"
$worksheetName = "Sheet1"
$file1 = "./1.xlsx"
$file2 = "./2.xlsx"

$differences = Compare-WorkSheet -ReferenceFile $file1 -DifferenceFile $file2 -WorkSheetName $worksheetName -BackgroundColor
Compare-Object -ReferenceObject $differences[0] -DifferenceObject $differences[1] `

(The Compare-Object returns no result, I'd think that it would).

Excel files:
2.xlsx
1.xlsx

I've asked AI but it couldn't help me.

[dfinke]

not built in. i like the notion. i've built that type of post process, but not for this.

be cool to have a switch on Compare-WorkSheet -ShowB4andAfter

Then in Compare-WorkSheet it would call a function that took the compar output, post process.

[DreamT80]

Yeah, that would be really nice!

In the meanwhile ( ;) ), I will have build my own post processing. I have one question: If you look at my example of the output, it seems that every difference outputs two results for each difference, one for each side. Will this always be true? I.e. will I always be able to compare $difference[0] with $difference[1], $difference[2] with $difference[3], and so on? Or maybe I should look at the _Row property to group the differences together?