Powershell filter files by SQL Server result set and copy

Question

I am executing a query in SQL Server and returning a single column result set. I need to loop through the result set and find file names matching the record from the result set. Part of the file name is a sequence number. I need to sort the files in ascending order, select the first file, and then copy only that file to a subdirectory. The records in the result set look like this:

MEMBERS.net MEMBERS_COMMENTS1.net MEMBERS_COMMENTS2.net MEMBERS_LANGUAGE.net MEMBERS_COVER_OHC.net MEMBERS_PROBLEM_LIST.net

The file names have this kind of structure:

00_1914330_MEMBERS.net

Can someone tell me why this does not achieve my end result?

add-pssnapin sqlservercmdletsnapin100
add-pssnapin sqlserverprovidersnapin100
cd SQLSERVER:\SQL\LOCALHOST\DEFAULT\Databases\SYSDB\Tables\dbo.STG0_EXPORT_JOBS 
$ds = Invoke-Sqlcmd -ServerInstance 'LOCALHOST' -Query "SELECT CASE WHEN SUBSTRING([EXPORT_NAME],1,3) = 'MHC' THEN SUBSTRING([EXPORT_NAME],5,(LEN([EXPORT_NAME])))+'.net' ELSE [EXPORT_NAME]+'.net' END AS export FROM [SYSDB].[dbo].[STG0_EXPORT_JOBS] WHERE [JOB_NAME] = 'MHC_STG0_MEMBERS'"
foreach ($files in $ds) {
    $oldestfile = Get-ChildItem C:\Scripts | 
        where{!$_.PSIsContainer -and $_.Name.EndsWith("$($files.export)")} | 
        sort-object -property name | select-object -first 1 Name | 
        Copy-Item "C:\Scripts\" + $oldestfile.substring(7,$oldestfile.length - 8) `
                   C:\Scripts\MEMBERS
 }

Here is what I get when I run this:

PS H:> C:\powershell_MoveKoreFiles.ps1 Unexpected token 'in' in expression or statement. At C:\powershell_MoveKoreFiles.ps1:1 char:472 + add-pssnapin sqlserverprovidersnapin100 add-pssnapin sqlservercmdletsnapin100 Set-Location SQLSERVER:\SQL\LOCALHOST\DEFAULT\Databases\SYSDB\Tables\dbo.STG0_ EXPORT_JOBS $ds=Invoke-Sqlcmd -Query "SELECT CASE WHEN SUBSTRING([EXPORT_NAME], 1,3) = 'MHC' THEN SUBSTRING([EXPORT_NAME],5,(LEN([EXPORT_NAME])))+'.net' ELSE [ EXPORT_NAME]+'.net' END AS export FROM [SYSDB].[dbo].[STG0_EXPORT_JOBS] WHERE [ JOB_NAME] = 'MHC_STG0_MEMBERS'" -ServerInstance "LOCALHOST" foreach ($files in <<<< $ds){$oldestfile = Get-ChildItem C:\Scripts|where{!$.PSIsContainer -and $.Name.EndsWith("$($files.export)")}|sort-object -property name -descending|se lect-object -first 1 Name|Copy-Item -path "C:\Scripts\"+$oldestfile.substring(7 ,$oldestfile.length - 8) -destination C:\Scripts\MEMBERS} + CategoryInfo : ParserError: (in:String) [], ParseException + FullyQualifiedErrorId : UnexpectedToken

PS H:>

What does it return in the foreach loop for $files.GetType()? — stej
– stej, Commented Feb 12, 2010 at 5:46
It returns the below for each row, but the whole result set wouldn't fit here. PS SQLSERVER:\SQL\LOCALHOST\DEFAULT\Databases\SYSDB\Tables\dbo.STG0_EXPORT_JOBS> foreach ($files in $ds){$files.GetType()} IsPublic IsSerial Name BaseType -------- -------- ---- -------- True False DataRow System.Object — stittdba
– stittdba, Commented Feb 12, 2010 at 19:06
Hm, it's strange. When you try foreach ($files in $ds){$files.GetType()} it works but when you try the code in your question, it returns Unexpected token 'in' in expression or statement.? — stej
– stej, Commented Feb 13, 2010 at 10:31
What happens if you try to pipe it like $ds | % { $file=$_; ... — stej
– stej, Commented Feb 13, 2010 at 10:33
Don't try to execute this as a one-liner (as indicated by char 472). In your script file, use multiple lines as shown above. If you really want to put it on a single line, use a ; to separate different statements. — Keith Hill
– Keith Hill, Commented Feb 14, 2010 at 2:37

Keith Hill · Accepted Answer · 2010-02-14 02:49:03Z

I think there might be one too many pipes in the script. Remove the one after the Select -first 1 Name e.g.:

Add-PSSnapin sqlservercmdletsnapin100
Add-PSSnapin sqlserverprovidersnapin100
cd SQLSERVER:\SQL\LOCALHOST\DEFAULT\Databases\SYSDB\Tables\dbo.STG0_EXPORT_JOBS
$ds = Invoke-Sqlcmd -ServerInstance 'LOCALHOST' -Query "SELECT CASE WHEN " +
          "SUBSTRING([EXPORT_NAME],1,3) = 'MHC' THEN " +
          "SUBSTRING([EXPORT_NAME],5,(LEN([EXPORT_NAME])))+'.net' " +
          "ELSE [EXPORT_NAME]+'.net' END AS export " +
          "FROM [SYSDB].[dbo].[STG0_EXPORT_JOBS] " +
          "WHERE [JOB_NAME] = 'MHC_STG0_MEMBERS'" 
foreach ($files in $ds) 
{ 
    $oldestfile = Get-ChildItem C:\Scripts |  
        Where {!$_.PSIsContainer -and $_.Name.EndsWith("$($files.export)")} |
        Sort name | Select Name -First 1
    $oldName = "C:\Scripts\$($oldestfile.substring(7,$oldestfile.length - 8))" 
    Copy-Item $oldName C:\Scripts\MEMBERS 
}

Collectives™ on Stack Overflow

Powershell filter files by SQL Server result set and copy

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related