☰

在 SQL 中查找文件名并重命名文件

tag-icon • tag-icon powershell
在 SQL 中查找文件名并重命名文件

我正在尝试使用 Powershell 脚本在文件名中使用帐号，并使用来自 SQL 数据库的 ID 号重新命名。下面是我尝试此操作的代码，但我没有得到我需要的结果。如果您有任何建议或意见，请告诉我。

谢谢！！

文件名 = 111119999.docx

表格 =

身份证号码

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:\ps\test -Name

 ## Loop through each file name

 foreach ($FiNm in $FiNms)
     {

 ## Variable for current File path
    $file = “H:\ps\test\” + $FiNm

 ##  Variable for new File path
    $newFile = “H:\ps\renamed\” + $FiNm

 $ID = Return-Info $CliRef
 $ID = $ID + “.docx”
 Copy-Item $file -Destination $newFile
 Rename-Item $file $ID -force
     }

答案1

通过将 $ID = Return-Info $CliRef 更改为 $ID = Return-Info $FiNm，我能够使其正常工作。文件名也需要删除“.docx”。

## 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:\ps\test -Name

 ## Loop through each Image name
 foreach ($FiNm in $FiNms)

{

 ## Variable for current File path
 $file = “H:\ps\test\” + $FiNm
 ##  Variable for new File path
 $newFile = “H:\ps\renamed\” + $FiNm

$ID = Return-Info $FiNm

$ID = $ID + “.docx”

Copy-Item $file -Destination $newFile

Rename-Item $file $ID -force

}

相关内容