value can not be null error in PowerShell

Question

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

According to your csv you posted, your delimeter is a comma, not a semicolon. — Abraham Zinala
– Abraham Zinala, Commented Jan 14, 2022 at 14:55
Hi @AbrahamZinala thanks for pointing that out and I'll ready fix that but still getting the same error. :( — ziico
– ziico, Commented Jan 14, 2022 at 14:58
Does running: import-csv -path "C:\scripts\LitHold-OneDrive\output\Return\2022-01-12-Return.csv" alone return anything? — Abraham Zinala
– Abraham Zinala, Commented Jan 14, 2022 at 15:09
@AbrahamZinala It's weired I'm got this error if I run just that one " import-csv : The member "[email protected]" is already present" — ziico
– ziico, Commented Jan 14, 2022 at 15:14
Aside... When invoking ExecuteWithResults what dataset do you expect to have returned from an insert statement? — AlwaysLearning
– AlwaysLearning, Commented Jan 14, 2022 at 15:14

Theo · Accepted Answer · 2022-01-14 15:29:52Z

3

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...

edited Jan 14, 2022 at 15:29

answered Jan 14, 2022 at 15:11

Theo

61.9k8 gold badges28 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

ziico Over a year ago

It work, thanks bro. really appreciate it but can I ask you one more question. I have multiple data in my csv file but I'm not sure why it's not inserting all of them and it only insert the last data. ;( (I uploaded tha data above)

Theo Over a year ago

@ziico Please see the last line of my answer. You may want to look up this

ziico Over a year ago

I already fixed that in my actual code but it's still inserting only the last data and not all of them. :(any idea why it might be?

Theo Over a year ago

@ziico Please edit your question and show us your fixes. Are you executing the query inside that loop too?

ziico Over a year ago

oh ok ok, I got it now. I still forgot to put the result inside the for loop. hehe Thank you so much for you help. :)

Collectives™ on Stack Overflow

value can not be null error in PowerShell

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related