0
\$\begingroup\$

I have a table that in one of the columns exists a list of file names. The names comes from other tables and the files in a folder.

Not all the files are used, may be attached to parcels that no longer are active. My list is of only the active parcels.

After creating the table I want to go through the folder of files and move the files that are being used(exist in the table) to another folder.

Currently I iterate the files in the folder and test whether each file one at a time returns a record or not using an SQL command.

This works, but the consistent checking back to the database is slow.

Here is the code as it sits now:

param( 
    [string] $src,  
    [string] $dest, 
    [string] $table
    )

$sql_instance_name = 'db'
$db_name = 'DB2'
$sql_user = 'user'
$sql_user_pswd = 'password'



Get-ChildItem -Path $src -Recurse -File | ForEach-Object  {
    $query = "select * from " + $table + " WHERE FILE_NAME Like '" + $_.BaseName + $_.Extension +  "'"
    #write-output $query


    $expcsv = invoke-sqlcmd -Username $sql_user -PASSWORD $sql_user_pswd -Database $db_name -Query $query -serverinstance $sql_instance_name
    if($expcsv -ne $null)

    {
        $nextName = Join-Path -Path $dest ( $_.BaseName  +  $_.EXTENSION)
        Write-Output $nextName
        $_ | Move-Item -Destination   $nextName
    }
}

Is there a way to load the data into memory, and search that instead of going back and forth from the database? I have to assume it would be quicker.

\$\endgroup\$
  • \$\begingroup\$ You could run $query = "select * from " + $table; $expcsvS = invoke-sqlcmd -query $query … before Get-ChildItem -Path $src -Recurse -File | ForEach-Object { Then I'd suppose that you can check ($_.BaseName + $_.Extension) -in $expcsvS.FILE_NAME or alike (I don't know FILE_NAME format). \$\endgroup\$ – JosefZ Dec 14 '18 at 13:18
  • \$\begingroup\$ @JosefZ write it up, it works and is much faster. \$\endgroup\$ – Scott Craner Dec 14 '18 at 14:53
1
\$\begingroup\$

Output from invoke-sqlcmd is Formatted table i.e. something like PSCustomObject[] array (or collection). Hence, one could address its columns as .FILE_NAME properties and apply comparison operators -in, -contains, -match etc. (elaborated from my original comment):

param( 
    [string] $src,  
    [string] $dest, 
    [string] $table
    )

$sql_instance_name = 'db'
$db_name = 'DB2'
$sql_user = 'user'
$sql_user_pswd = 'password'

$query = "select * from $table"
$expcsvS = invoke-sqlcmd -Username $sql_user -PASSWORD $sql_user_pswd `
    -Database $db_name -Query $query -serverinstance $sql_instance_name

Get-ChildItem -Path $src -Recurse -File | ForEach-Object  {

    if ( $_.Name -in  $expcsvS.FILE_NAME )
    {
        $nextName = Join-Path -Path $dest -ChildPath $_.Name
        Write-Output $nextName
        $_ | Move-Item -Destination $nextName
    }
}

Note that I use $_.Name instead of ($_.BaseName + $_.Extension) as for files, the following code snippet always returns True.

(Get-ChildItem -Recurse -File | 
    Where-Object { $_.Name -ne ($_.BaseName + $_.Extension) }) -eq $null

Also note that if ( $_.Name -in $expcsvS.FILE_NAME ) {} is equivalent to any of (incomplete list):

if ( $expcsvS.FILE_NAME -match "^$($_.Name)$") {}
if ( $expcsvS.FILE_NAME -contains $_.Name ) {}
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.