Aug 17 2021 07:39 AM
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
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
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.
Thanks
Jan 30 2022 03:02 PM
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)})
Jan 31 2022 01:03 AM
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*'}