Help With Filename Comparisons and Output

Occasional Visitor

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 reprocessed



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


I have managed to create x2 arrays, one to hold the SQL output data, and x1 for the directory searches. However this is the point where I get stuck, it would appear that as people have been saving files, they are appending either a singular '0' or x2 '00'


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


Process so far.

SQL Export - Certain column passed to powershell array (Done)

Directory scan for all *.pdf files, saved into Array (Done)

Compare array, output missing files list (This is where I am stuck due to some files being 12345, or 012345 or 0012345


I can complete the "Compare" job, but as there are "sometimes" 00's in the filenames, they are also exported


Any help would be greatly appreciated.




2 Replies



You could add this after line 30, it selects the last 5 numbers from the name effectively removing any prefix 0's


$scanarray2= @($scanarray2 | ForEach-Object {$_.Substring($_.length -5,5)})


It would be better to pattern match to remove leading 0's than fix the filename length to 5 chars.



#Set up 

# create test directory
new-item c:\temp\hub\demo -itemtype directory
# Move to that directory
sl c:\temp\hub\demo
# create some new files
$null = new-item c:\temp\hub\demo\123.pdf, c:\temp\hub\demo\0234.pdf, c:\temp\hub\demo\00345.pdf, c:\temp\hub\demo\100345.pdf, c:\temp\hub\demo\0034504.pdf

# example
# get all items and then remove leading zeros
get-childitem | split-path -leaf | foreach { $_ -replace '^0*'}