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

 
Participant
3 years, 11 months ago.

  • Author
    Posts
  • #23080

    Participant
    Points: 0
    Rank: Member

    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

    Participant
    Points: 60
    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

    Participant
    Points: 21
    Team Member
    Rank: Member

    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!

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

denizli escort samsun escort muğla escort ataşehir escort kuşadası escort