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

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

      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

  • Question
    Sign in to vote
    0
    Sign in to vote 
    $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

  • Question
    Sign in to vote
    0
    Sign in to vote 
    $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
  • Question
    Sign in to vote
    0
    Sign in to vote

     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
  • Question
    Sign in to vote
    0
    Sign in to vote

    Same thing.  Always takes the first digits before '_'

    \_(ツ)_/

    Tuesday, April 23, 2019 6:12 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    You have to copy the original file to a new name.

    See:

    help copy-item -online

    \_(ツ)_/

    Tuesday, April 23, 2019 11:47 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

     This is what I choose.

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

     Thanks.

    Tuesday, April 23, 2019 1:08 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

     Many thanks JRV!!!!

    Tuesday, April 23, 2019 3:52 PM