Help Please - Find non matching "like" items in Array

Hi all,

I’m looking for some help if possible. Please bear in mind I am a complete PowerShell novice so feel free to dumb things down as appropriate. Also apologies if this is in the wrong section

End Product - Report/CSV to show all “missing files” between an SQL output compared to existing files in a directory - Essentially, what orders are in SQL, compare this to filenames in a directory, any files that are missing needs to be exported

Issue:

I need to compare an SQL CSV output that is always in a format of 12345 (order number) to a directory search of filenames (pdf’s) 12345.pdf

However, when users have been saving files, they call them either 12345, 012345 or 0012345
We now need to find these files and match against the original SQL export number.

I have created several arrays to hold the data but I just cannot find a way to compare them and spit out the “missing” files. If it was just one format of filename this wouldn’t be an issue, however it several formats (with the leading zeros)

In my head it is something like.

Foreach item in $Array1 (Order numbers)
Compare to array2 (Pdf files in a directory),
If not found try array3 (has a 0 appended to beginning of each number found in $Array1)
If not found try array4 (has a 00 appended to beginning of each number $Array1)
If still not found, export or write-host anything you can’t find

For example, the SQL export will be 12345, the file (if it exists) could be either 12345.pdf, 012345.pdf or 0012345.pdf

Any help would be greatly appreciated.
Please see attached “code” (if that’s the right word for what I have written :rofl:)

MG,
Welcome to the forum. :wave:t4:

It will be hard to recommend something meaningful without seeing your actual code. :wink: Please share it.

Some general thoughts about your task. I think it would be better to remove leading zeros for the comparison unless the leading zeros make a difference.

In PowerShell when you want to compare obejcts you should try to use Compare-Object.

Please read the help completely including the examples to learn how to use it.

Sorry Olaf that would have been helpful wouldn’t. I will re-write the original post :slight_smile:
Also thanks for the quick reply

Please do not post pictures of code as it makes it impossible to copy the code to reproduce your issue. And it might be helpfull as well to have some sample data if it’s needed to reproduce the issue as we do not have your SQL data.

When you post code or sample data please format it as code using the preformatted text button ( </> ).
Thanks in advance.

Sorry Olaf please find below, 
Apologies for the delay

Code below;

#################################################
############# Path/File Variables ###############
#################################################

# $D variable is searched for PDF invoices including subdirectories #
$D = "c:\Somepath\InvoiceSearchScript"

# $ScanExport is the OUTPUT of a directory search including only .PDF files - Not currently used #
# $ScanExport = "c:\Somepath\InvoiceSearchScript\SeachOutput.csv"

# $ScanDirectories is the RESULT of a directory search including only .PDF files - This variable is passed into the array called $ScanArray #
$ScanDirectories = get-childitem -Path $D -Include *.pdf -Recurse | Select-Object Basename,Name

# $SQLExport is the file exported from Dimensions (SQL) with the Order Details. The column we need is "DimensionsRef" #
$SQLExport = "c:\Somepath\InvoiceSearchScript\SQLExport.csv"

# This file is used for outputting the results for the Missing invoices, i.e files that cannot be find based on the Dimensions reference #

$MissingInvoices = "c:\Somepath\InvoiceSearchScript\Missing Invoices.csv"
$TestInvoices = "c:\Somepath\Desktop\InvoiceSearchScript\Test.csv"

#################################################
#################### ARRAYS #####################
#################################################


#### Several arrays have had to be created to check the invoice files, this is because there is no standardisation amongst the export process, files could be 12345.pdf, 012345.pdf or 0012345.pdf ####
#### As this is the case, the jobs that run will have to compare the different arrays to work out what is missing ####
#### Example: If the "OrderRef" number does not appear in any comparison Array, there is no invoice.pdf file so will have to be created by the accounts department"

# $SQLArray array is a list of all order numbers found in SQL Export "OrderRef" Column)
$SQLArray = @((import-csv $SQLExport).OrderRef) 

# $ScanArray array is populated with the file basename from the $ScanDirectories directory search - Essentially a list of all PDF files with the file extension removed #
$ScanArray = @(($ScanDirectories).Basename)            

# $Appendsql0 array appends a '0' to all items find in the $SQLArray #
$Appendsql0 = @($SQLArray | ForEach-Object {"0$_"})     # This array appends a '0' to all items find in the $SQLArray #

# $Appendsql00 array appends a '00' to all items find in the $SQLArray #
$Appendsql00 = @($SQLArray | ForEach-Object {"00$_"})  

# $CompareArray compares $SQLArray and $ScanArray, anything in this array does not have a file name match to "OrderRef" #
$CompareArray = @(Compare-Object -ReferenceObject $SQLArray -DifferenceObject $ScanArray | Where-Object SideIndicator -EQ '<=' | select-object InputObject )  

# $CompareArray0 compares $ScanArray and $Appendsql0 array, this is to check if there is a file with a "0" before "OrderRef", anything in this array does not have a file name match to "OrderRef" #
$CompareArray0 = @(Compare-Object -ReferenceObject  $Appendsql0 -DifferenceObject $ScanArray | Where-Object SideIndicator -EQ '<=' | select-object InputObject )

# $CompareArray00 compares $ScanArray and $Appendsql00 array, check if there is a file with a "00" before "OrderRef", anything in this array does not have a file name match to "OrderRef" #
$CompareArray00 = @(Compare-Object -ReferenceObject  $Appendsql00 -DifferenceObject $ScanArray | Where-Object SideIndicator -EQ '<=' | select-object InputObject )

# $ComparisonArray creates an array to store all "Comparison" array data - ($CompareArray,$CompareArray0,$CompareArray00)  #
$ComparisonArray = @($CompareArray,$CompareArray0,$CompareArray00)

$SQLCOMPARARRAY = @(Compare-Object -ReferenceObject $SQLArray -DifferenceObject $ComparisonArray | Where-Object SideIndicator -EQ '<=' | select-object InputObject )



ForEach-Object in $SQLArray
Compare $SQLArray against $ComparisonArray

If not found in format:12345
Try '0'12345
If not found in format: '0'12345
Try '00'12345
If still not found - Export "missing Orders list"![example files|469x500](upload://3Wb7X92vr8Sq1wn3jOE3gO6cfty.png)
![SQL File|642x499](upload://ecSpJRc9P1uNyqmh1mfoHUzwt3y.png)

I have a hard time understanding your code completely. Maybe we start with some general tips:

Instead of limitting your data to particular properties like you do it with this line:

$ScanDirectories = get-childitem -Path $D -Include *.pdf -Recurse | Select-Object Basename,Name

I would try to enrich them with derived data. If I got it right you want to compare the file names but some of them have leading zeros. So I would try to get rid of them like this:

$ScanDirectories = 
    Get-ChildItem -Path $D -Include *.pdf -Recurse | 
        Select-Object -Property *, @{Name = 'CompareProperty'; Expression = { $_.BaseName -as [int] }}

Now you still have all initial properties plus the one you can use for comparison. When you do the same with the objects you want to compare the file names against you’re almost done.

You can provide the complete objects to Compare-Object. They just have to have a property with the same name you can provide to the parameter -Property like you can see in the help example #5

Hi Olaf, apologies, as I’m not very good at this I’m probably massively over complicating this.

You have however given me an idea which i think is going to hugely simplify this.
Please leave with me and I will take a look tomorrow and come back you.

Again thanks for your help so far

Hi Olaf hope you are well.

Just a heads up that I have managed to sort this now.
I took your original advice and stripped the leadings zeros from the directory scan which actually made it hugely easier than what I was trying to do.

Thanks again for your help really appreciated :+1:

Great. I’m glad it helped. :+1:t4: :smiley: