DBNULL and powershell

Welcome Forums General PowerShell Q&A DBNULL and powershell

This topic contains 2 replies, has 3 voices, and was last updated by

3 years, 7 months ago.

  • Author
  • #23080

    Points: 0
    Rank: Member


    GOAL : an export to csv showing from a SQL table ; showing me the NULL value in column/row if it's present. eg. |1|"hello"|NULL|1.28
    – Powershell
    – I fill up a datatable with a SQL query
    – First I tried export-csv ; but null values are seen as empty text... it's not really the same 🙁
    – Next idea was ; let me create an export-csv myself ; I iterate for each row and each column checking if there is a DBNULL value present (row[col] -eq [System.DBNULL]::value)
    ... unfortunately I loose all speed here when doing this. For 165000 rows (15 cols) ; It takes up to 15 minutes.
    It now works... but too slow, especially if it needs to size for bigger tables.

    Maybe I started off on the wrong foot? somebody a better idea or ?
    Any help appreciated 🙂


  • #23082

    Points: 0
    Rank: Member

    Have you thought about adjusting your SQL query so that it creates a virtual table and in that table the third column is populated with a true or false indicating the null status?
    That way, SQL can do all the work, and you just return the dataset you're looking for.

  • #23123

    Points: 0
    Rank: Member


    Check out Invoke-Sqlcmd2. When you call this with the parameter -As PSObject, it will run through some inline C# code from Dave Wyatt and converts the output to a PSObject, setting any DBNulls to Null... You could modify that c# code to return the string NULL, if a particular value is null. Of you could use PowerShell to process the data in the same way.

    On a side note, the C# code in there is incredibly quick at scrubbing DBNulls, something you mentioned was a bit slow when using PowerShell (I ran into the same issue and Dave helped out)


The topic ‘DBNULL and powershell’ is closed to new replies.