Ryfujk

I am trying to use a Powershell script to use the account number in a file name and re-name with the ID number from a sql database. Below is the code I am using to attempt this and I am not getting the results I need. Please let me now if you have any suggestion or advise in getting this to work.

Thanks!!

File name = 111119999.docx

Table =

ID AccountNumber

5555 111119999

## Select Data from Database



function Select-Info($CliRef) 

   {

    $conn = new-object System.Data.SqlClient.SqlConnection

    $connstring = “provider=sqloledb;data source=[vmsvr039];initial catalog=[crs5_oltp];integrated security=SSPI”    

    $conn.connectionstring = $connstring



    $conn.open()

    $query = “Select convert(varchar,cnsmr_accnt_idntfr_agncy_id) as ID FROM cnsmr_accnt WHERE cnsmr_accnt_crdtr_rfrnc_id_txt = '$CliRef'”

    $cmd = New-Object System.Data.SqlClient.SqlCommand($sql,$conn)

    $cmd.connection = $conn

    $cmd.commandtext = $query

    $result = $cmd.executenonquery()

    $conn.close()



    return $query

    }



## Return ID from Database



function Return-Info($CliRef)

   {

    $conn = New-Object System.Data.SqlClient.SqlConnection

    $conn.ConnectionString = "Server=vmsvr039;Database=crs5_oltp;Integrated Security=SSPI;"

    $conn.Open()

    $cmd = New-Object System.Data.SqlClient.SqlCommand

    $cmd.commandtext = “Select convert(varchar,cnsmr_accnt_idntfr_agncy_id) as ID FROM cnsmr_accnt WHERE cnsmr_accnt_crdtr_rfrnc_id_txt = '$CliRef'”

    $cmd.connection = $conn

    $result = $cmd.ExecuteScalar()

    $conn.close()   



    return $result

    }



 ## Collect the file names



 $FiNms = Get-ChildItem H:pstest -Name



 ## Loop through each file name



 foreach ($FiNm in $FiNms)

     {



 ## Variable for current File path

    $file = “H:pstest” + $FiNm



 ##  Variable for new File path

    $newFile = “H:psrenamed” + $FiNm



 $ID = Return-Info $CliRef

 $ID = $ID + “.docx”

 Copy-Item $file -Destination $newFile

 Rename-Item $file $ID -force

     }

By changing the $ID = Return-Info $CliRef to $ID = Return-Info $FiNm I was able to get this to work. The file name will also need to have the ".docx" removed.

## Return ID from Database

 function Return-Info($CliRef)

{



$conn = New-Object System.Data.SqlClient.SqlConnection

$conn.ConnectionString = "Server=vmsvr039;Database=crs5_oltp;Integrated Security=SSPI;"

$conn.Open()

$cmd = New-Object System.Data.SqlClient.SqlCommand

$cmd.commandtext = “Select convert(varchar,cnsmr_accnt_idntfr_agncy_id) as ID FROM cnsmr_accnt WHERE cnsmr_accnt_crdtr_rfrnc_id_txt = '$CliRef'”

$cmd.connection = $conn

$result = $cmd.ExecuteScalar()

$conn.close()  



return $result



}



 ## Collect the image names

 $FiNms = Get-ChildItem H:pstest -Name



 ## Loop through each Image name

 foreach ($FiNm in $FiNms)



{



 ## Variable for current File path

 $file = “H:pstest” + $FiNm

 ##  Variable for new File path

 $newFile = “H:psrenamed” + $FiNm



$ID = Return-Info $FiNm



$ID = $ID + “.docx”



Copy-Item $file -Destination $newFile



Rename-Item $file $ID -force



}