Help With Filename Comparisons and Output

%3CLINGO-SUB%20id%3D%22lingo-sub-2656389%22%20slang%3D%22en-US%22%3EHelp%20With%20Filename%20Comparisons%20and%20Output%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2656389%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20looking%20for%20some%20help%20if%20possible.%20Please%20bear%20in%20mind%20I%20am%20a%20complete%20Powershell%20novice%20so%20feel%20free%20to%20dumb%20things%20down%20as%20appropriate.%20Also%20apologies%20if%20this%20is%20in%20the%20wrong%20section%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEnd%20Product%20-%20Report%2FCSV%20to%20show%20all%20%22missing%20files%22%20between%20an%20SQL%20output%20compared%20to%20existing%20files%20in%20a%20directory%20-%20Essentially%2C%20what%20orders%20are%20in%20SQL%2C%20compare%20this%20to%20filenames%20in%20a%20directory%2C%20any%20files%20that%20are%20missing%20needs%20to%20be%20reprocessed%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIssue%3A%3C%2FP%3E%3CP%3EI%20need%20to%20compare%20an%20SQL%20CSV%20output%20that%20is%20always%20in%20a%20format%20of%2012345%20(order%20number)%20to%26nbsp%3Ba%20directory%20search%20of%20filenames%20(pdf's)%2012345.pdf%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20managed%20to%20create%20x2%20arrays%2C%20one%20to%20hold%20the%20SQL%20output%20data%2C%20and%20x1%20for%20the%20directory%20searches.%20However%20this%20is%20the%20point%20where%20I%20get%20stuck%2C%20it%20would%20appear%20that%20as%20people%20have%20been%20saving%20files%2C%20they%20are%20appending%20either%20a%20singular%20'0'%20or%20x2%20'00'%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20example%2C%20the%20SQL%20export%20will%20be%2012345%2C%20the%20file%20(if%20it%20exists)%20could%20be%20either%2012345.pdf%2C%20012345.pdf%20or%200012345.pdf%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EProcess%20so%20far.%3C%2FP%3E%3CP%3ESQL%20Export%20-%20Certain%20column%20passed%20to%20powershell%20array%20(Done)%3C%2FP%3E%3CP%3EDirectory%20scan%20for%20all%20*.pdf%20files%2C%20saved%20into%20Array%20(Done)%3C%2FP%3E%3CP%3ECompare%20array%2C%20output%20missing%20files%20list%20(This%20is%20where%20I%20am%20stuck%20due%20to%20some%20files%20being%2012345%2C%20or%20012345%20or%200012345%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20can%20complete%20the%20%22Compare%22%20job%2C%20but%20as%20there%20are%20%22sometimes%22%2000's%20in%20the%20filenames%2C%20they%20are%20also%20exported%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20would%20be%20greatly%20appreciated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Capture.PNG%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F303769i2688715CBCFB32E5%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Capture.PNG%22%20alt%3D%22Capture.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%E2%80%83%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2656389%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EWindows%20PowerShell%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
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

 

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 

Capture.PNG

0 Replies