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
– 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 🙂
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.
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)