Enhancement: Empty Strings added as DBNULL instead of " "

[jakedenyer]

Warren -

Just noticed this when working with RVTools data from Excel and importing it to a database. Have some empty columns that are adding as empty strings in the DB instead of NULL.

Here is the line I modified to add a DBNULL value instead of an empty string.

                if ($property.GetType().IsArray)
                {
                    $DR.Item($Name) = $Value | ConvertTo-XML -As String -NoTypeInformation -Depth 1
                }
                elseif($Value -eq $null)
                {
                    $DR.Item($Name) = [DBNull]::Value
                }
                else
                {
                    $DR.Item($Name) = $Value
                }

Change to: elseif($Value -eq $null -or $Value -eq "")

                if ($property.GetType().IsArray)
                {
                    $DR.Item($Name) = $Value | ConvertTo-XML -As String -NoTypeInformation -Depth 1
                }
                elseif($Value -eq $null -or $Value -eq "")
                {
                    $DR.Item($Name) = [DBNull]::Value
                }
                else
                {
                    $DR.Item($Name) = $Value
                }

[RamblingCookieMonster]

Thanks for the heads up Jake!

Is this in Out-DataTable?

I think that would make sense (or even a regex for whitespace). Can you think of any datasets where a space would be needed? Wonder if a switch 'WhitespaceIsNotNull' would make sense - convert whitespace to dbnull unless that is specified?

Thanks!

Warren

[jakedenyer]

Correct. I'm using it in conjuction with invoke-sqlbulkcopy.

I'm sure the .NET class of IsNullOrEmpty would be appropriate in this case. I just needed a quick fix.

I can't think of a reason for white space in a db. It would really cause issues because it would take up storage space - although minimal might add up.

And in powershell it would be tough to tell the difference between DBNULL and "" - unless you check the type.

Really appreciate all your work man. Your stuff has helped me more than you know. On Jan 27, 2016 13:07, "Warren F." [email protected] wrote:

Thanks for the heads up Jake!

Is this in Out-DataTable?

I think that would make sense (or even a regex for whitespace). Can you think of any datasets where a space would be needed? Wonder if a switch 'WhitespaceIsNotNull' would make sense - convert whitespace to dbnull unless that is specified?

Thanks!

Warren

— Reply to this email directly or view it on GitHub https://github.com/RamblingCookieMonster/PowerShell/issues/9#issuecomment-175776113 .

[DevOpsDBAMaverick]

I can confirm that I also had the empty string issue @jakedenyer was referring to and by adding this additional check: elseif ($null -eq $Value -or $Value -eq "") resolves the issue when writing to a database table