locked
copy files to folder dest from SQL table read results RRS feed

  • Question

  •   I'm trying to copy files to a network share based upon results from a SQL query. The files names are stored in the SQL table

    with  Invoice_Path(field name)    like \\srv1\pdf\archive\5433_012819.pdf

     I want to parse the file name in table an have a result of 5433.pdf to be copied to a Destination folder example:

      


    copy \\srv1\pdf\archive\5433.pdf \\srv2\dailypdf\


      I'm also trying to keep an output of the files read that meets the sql criteria then a count of the files it actually copied.

    $targetFolder = "C:\CSV_Destination\" #"
    
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
     
    $SqlConnection.ConnectionString = "Server=(local);Database=StagingDatabase;Integrated Security=True"
     
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
     
    $SqlCmd.CommandText = $("Select * from tbl_Invoices where inv_pdf is null")
     
    $SqlCmd.Connection = $SqlConnection
     
    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
     
    $SqlAdapter.SelectCommand = $SqlCmd
     
    $DataSet = New-Object System.Data.DataSet
     
    $SqlAdapter.Fill($DataSet)
     
    $SqlConnection.Close()
     
    $data = $dataset.Tables[0]
     
    $Data
    
    foreach ($file in $Data)  { 
    	$fname = ($file.Invoice_Path).split('.')[0]	# item before the '.'
        $fname
    	$prefix = $fname.split('_')[0] # item before the '_'
        $prefix
    	$newname=$prefix + '.pdf'
    
        copy-item  -path $newname -Destination $targetFolder
       
    }
    
    Write-Host 'Total number of files read from Table '$fname ' is ' $numFiles
    Write-Host 'Total number of files that was copied to '$targetFolder ' is ' $i
     Thanks.


    • Edited by jrv Tuesday, April 23, 2019 12:01 AM fixed style sheet error
    Monday, April 22, 2019 11:38 PM

Answers

  • $targetFolder = 'C:\CSV_Destination'
    $connStr = 'Server=(local);Database=StagingDatabase;Integrated Security=True'
    $conn = New-Object System.Data.SqlClient.SqlConnection($connStr)
    $conn.Open() $cmd = $conn.CreateCommand() $cmd.CommandText = 'Select * from tbl_Invoices where inv_pdf is null' $rdr = $cmd.ExecuteReader() $dt = New-Object System.Data.DataTable $dt.Load($rdr) $dt | ForEach-Object{ if(($_.Invoice_Path| Split-Path -leaf) -match '(\d+)_') { $prefix = $matches[1] copy-item -path "$prefix.pdf" -Destination $targetFolder -Verbose }else{ Write-Host 'Not found' } }


    \_(ツ)_/




    • Edited by jrv Tuesday, April 23, 2019 12:17 AM
    • Marked as answer by hart60 Tuesday, April 23, 2019 3:52 PM
    Tuesday, April 23, 2019 12:15 AM

All replies

  • $targetFolder = 'C:\CSV_Destination'
    $connStr = 'Server=(local);Database=StagingDatabase;Integrated Security=True'
    $conn = New-Object System.Data.SqlClient.SqlConnection($connStr)
    $conn.Open() $cmd = $conn.CreateCommand() $cmd.CommandText = 'Select * from tbl_Invoices where inv_pdf is null' $rdr = $cmd.ExecuteReader() $dt = New-Object System.Data.DataTable $dt.Load($rdr) $dt | ForEach-Object{ if(($_.Invoice_Path| Split-Path -leaf) -match '(\d+)_') { $prefix = $matches[1] copy-item -path "$prefix.pdf" -Destination $targetFolder -Verbose }else{ Write-Host 'Not found' } }


    \_(ツ)_/




    • Edited by jrv Tuesday, April 23, 2019 12:17 AM
    • Marked as answer by hart60 Tuesday, April 23, 2019 3:52 PM
    Tuesday, April 23, 2019 12:15 AM
  •  WHat if a few of the entries in table where stored from the local server as the Invoice_Path.

    example:

    C:\CSV_Source\1196836_013007.pdf

     Could script handle both conditions?

     Thanks!!

    Tuesday, April 23, 2019 6:02 AM
  • Same thing.  Always takes the first digits before '_'


    \_(ツ)_/

    Tuesday, April 23, 2019 6:12 AM
  • When it does the lookup from SQL. I need to copy the file to Dest as a NewName. I think that is what throwing it off,

    From SQL Table:

    C:\CSV_Source\1196836_013007.pdf

    Copy-Item : Cannot find path 'C:\CSV_Source\1196836.pdf' because it does not exist

    Thanks.

     

    Tuesday, April 23, 2019 10:40 AM
  • You have to copy the original file to a new name.

    See:

    help copy-item -online


    \_(ツ)_/

    Tuesday, April 23, 2019 11:47 AM
  •  This is what I choose.

               $newname=$prefix + '.pdf'
                copy-item  ($_.Invoice_Path) -Destination $targetFolder$newname

     Thanks.

    Tuesday, April 23, 2019 1:08 PM
  •  Many thanks JRV!!!!

    Tuesday, April 23, 2019 3:52 PM