PowerShell to compare contents of 2 spreadsheets

[penfold]

I have a large number of spread sheets which have slightly different formats over time. I am working on standardizing this and in addition I want to be able to check when there are updates required/changes made. I have been able to do this using the following method

 

I have imported the excel file and been able to grab the cells I want to check as $test

I have the file I want to check against as $policy

 

$Match = (Select-String $test $Policy -Allmatches).Matches.Value

 

When this condition is True I then perform some additional tasks which populate the fields and output them to files which confirm either they match or we need to take some action. This works fine for most of the files, but I am seeing I have a problem when dealing with text which contains brackets - a ( or ). It seems that this is handled differently when using the above test to check a match exists.

 

For example if I had content in the files which was like this

Billy (Bob) OR (Billy) Bob

 

When I am checking for a match it doesn't work. However if I was to try and match on Billy Bob, it would work

 

If I need to use a different method that is fine, but previously this has been done by using Excel to check the files using Vlookup, but as there are over 50 I don't want to be doing this individually. What I want to be able to do is take a value from 1 spreadsheet and compare it to a value in a different spreadsheet.

 

Can anyone point me in the right direction?

 

EDIT - just to add that 1 file is a spreadsheet with headers and the second is a csv export. So I can't just compare cell value A1=A1 etc

Edited April 20, 2023 by penfold

[howartp]

Temporarily import the CSVs as tabs into a master spreadsheet then use various match(), vlookup(), xlookup() and if()'s to compare?

[chaplic]

I dont quite understand what you are trying to do, but have you looked at compare-object? it's pretty cool and fast enough for things that size.

[penfold]

Basically I want to take the values in the 1 spreadsheet and check if they are also in a second csv file. My code as is works fine when the value I want to compare does not contain any brackets. It seems if I try to assign a variable a value which contains a bracket it drops it.

 

Basic Example

$test = "(L1)"

(Select-String -pattern $test $Policy -Allmatches).Matches.Value

 

Will provide a match for L1. But I need the check to match on "(L1)" so I need the brackets to be part of the match criteria

[Ditto]

Have you considered using an open source utility to help here - chances are the tool will be well tested and manage the problems you are encountering with brackets (or as some would call them, parenthesis!). Depending on version, even Excel offer a a built-in tool - https://support.microsoft.com/en-us/office/compare-two-versions-of-a-workbook-by-using-spreadsheet-compare-0e1627fd-ce14-4c33-9ab1-8ea82c6a5a7e , but I can't comment on it's usefulness.

[penfold]

I think I kind of got this resolved by using

(Select-String -pattern $test $Policy -SimpleMatch)

 

The only problem I have now, is I can't grab the matched value like I did from -AllMatches).Matches.Value. Although I'm not actually sure that matters as I am just looking for what is matched.

[penfold]

By the way, the reason for not wanting to do it directly in Excel is because of the number of files. I don't want to be manually checking 50 files to see the differences if I can run 1 script and get the result of which files I need to check (which will hopefully be low numbers)