DBNULL and powershell

PowerShell Help
1

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 :frowning:
  • 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 :slight_smile:

Thanks

2

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.

3

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!