CODEWITHSUNDEEP
sqlsql-serverpowershellcsvpowershell-2.0
ziico
ziico

Reputation: 479

value can not be null error in PowerShell

I'm trying to import my csv file into my sql database using like this but I'm not sure why it's saying

Exception calling "ExecuteWithResults" with "1" argument(s): "Value cannot be null.
Parameter name: sqlCommands"

even though I don't have Null value in my csv file and also I make sure my table columns to accept null value.


$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]

My csv file

//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","",""

Upvotes: 0

Views: 942

Answers (1)

Theo
Theo

Reputation: 61068

Don't remove the column headers, but double check how they are written.. with spaces

Your code ignores those here

$($_.HoldValue) --> $($_.'Hold Value')
$($_.MultMID) --> $($_.'Mult MID')
$($_.ExistOD) --> $($_.'Exist OD')

Either keep the code and rewrite the headers (take out the spaces) or make sure you use the property names according to the headers.

By removing the column headers, the first line in the csv file wil be used as column headers unless you supply new ones with parameter -Header. Removing headers will cause problems if the same field value is encountered more than once because column headers must be unique

Then there is this line:

$result = $db.ExecuteWithResults($csvfile)

which should be

$result = $db.ExecuteWithResults($query)

AND there is no point in looping over the records of the csv file and inside that loop overwrite your query string on every iteration so only the last record wil remain...

Upvotes: 3

Related Questions