DBNULL and powershell

This topic contains 2 replies, has 3 voices, and was last updated by Profile photo of Warren Frame Warren Frame 1 year, 9 months ago.

  • Author
    Posts
  • #23080
    Profile photo of Tim Braes
    Tim Braes
    Participant

    Dear,

    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 🙂

    Thanks

  • #23082
    Profile photo of Tim Pringle
    Tim Pringle
    Participant

    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
    Profile photo of Warren Frame
    Warren Frame
    Participant

    Tim,

    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)

    Cheers!

You must be logged in to reply to this topic.