我正在嘗試使用這樣的方法將我的 csv 檔案匯入到我的 sql 資料庫中，但我不確定它為什么這么說
Exception calling "ExecuteWithResults" with "1" argument(s): "Value cannot be null.
Parameter name: sqlCommands"
即使我的 csv 檔案中沒有 Null 值，并且我確保我的表列接受 null 值。
$s = New-Object Microsoft.SqlServer.Management.Smo.Server "server name"
$db = $s.Databases.Item("LitHold")
$csvfile = import-csv -delimiter ";" -path "C:\scripts\LitHold-OneDrive\output\Return\2022-01-12-Return.csv"
$csvfile |foreach-object{
$query = "insert into DailyReport VALUES ('$($_.MIN)','$($_.MID)','$($_.UPN)','$($_.Change)','$($_.Type)','$($_.HoldValue)','$($_.OneDrive)','$($_.Mailbox)','$($_.Created)','$($_.Modified)','$($_.MultMID)','$($_.Account)','$($_.ExistOD)')"
}
$result = $db.ExecuteWithResults($query)
# Show output
$result.Tables[0]
我的 .csv 檔案
//The top one is my columns name and it's already inside my table
"MIN","MID","UPN","Change","Type","Hold Value","OneDrive","Mailbox","Created","Modified","Mult MID","Account","Exist OD"
"338780228","lzlcdg","[email protected]","Hold Created","OneDrive and Mailbox","Y","https://devf-my.sharepoint.com/personal/lzlcdg_namqa_corpqa_geuc_corp_com","[email protected]","1/11/2022 11:38:57 AM","1/11/2022 11:38:57 AM","N","",""
"419150027","lzs8rl","[email protected]","Hold Created","OneDrive and Mailbox","Y","https://my.sharepoint.com/personal/lzs8rl_namqa_corpqa_gcom","[email protected]","1/11/2022 11:39:05 AM","1/11/2022 11:39:05 AM","N","",""
uj5u.com熱心網友回復：
不要洗掉列標題，但仔細檢查它們是如何寫的..帶空格
您的代碼忽略了這里的那些
$($_.HoldValue)--> $($_.'Hold Value')
$($_.MultMID)--> $($_.'Mult MID')
$($_.ExistOD)-->$($_.'Exist OD')
保留代碼并重寫標題（去掉空格）或確保根據標題使用屬性名稱。
通過洗掉列標題，csv 檔案中的第一行將用作列標題，除非您提供帶有引數的新標題-Header。如果多次遇到相同的欄位值，洗掉標題將導致問題，因為列標題必須是唯一的
然后是這一行：
$result = $db.ExecuteWithResults($csvfile)
應該是
$result = $db.ExecuteWithResults($query)
并且回圈遍歷 csv 檔案的記錄是沒有意義的，并且在該回圈內，每次迭代都會覆寫您的查詢字串，因此只會保留最后一條記錄......
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/411054.html
標籤：
上一篇：選擇最暢銷的產品ID