Answered by:
copy files to folder dest from SQL table read results

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' } }
\_(ツ)_/
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' } }
\_(ツ)_/
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$newnameThanks.
Tuesday, April 23, 2019 1:08 PM -
Many thanks JRV!!!!
Tuesday, April 23, 2019 3:52 PM