Exporting from SQL using POWERSHELL where the output contains NULLS

Question

I'm using the below POWERSHELL scrip to export the content of a SQL table to a .csv. It has been working well, unfortunately it's failing as the table in question has now had some NULLS added to it.

Is there a way to modify this to allow it to pass NULLS, or is it best to amend the underlying table.

$server = "sqlmtest"
$database = "mi_lookups"
$query = "SELECT * FROM [dbo].[SQLMtest-BIS-BUS-BPS_system_agent_lookup]"

$extractFile = @"
G:\system_Lookup_Tables\system_agent_lookup.csv
"@


$connectionTemplate = "Data Source={0};Integrated Security=SSPI;Initial Catalog={1};"
$connectionString = [string]::Format($connectionTemplate, $server, $database)
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString

$command = New-Object System.Data.SqlClient.SqlCommand
$command.CommandText = $query
$command.Connection = $connection

$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $command
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$connection.Close()


$DataSet.Tables[0] | Export-Csv $extractFile

Thank you.

You should not have to modify the table itself at all. Modify your select statement such that NULLs are not returned, or your Export-Csv pipeline such that NULLs do not cause a problem — paul
– paul, Commented Mar 2, 2015 at 11:07
Just a workaround, but couldn't you extend your query by giving column names and using ISNULL([column1], '') or ISNULL([column1], 0) according to datatype ? — Rubik
– Rubik, Commented Mar 2, 2015 at 11:08
Unfortunately the table contains rows where NULLS are present but I still need to export them. @paul - any suggestions on how I can edit the Export-csv to handle NULLS? — chris1982
– chris1982, Commented Mar 2, 2015 at 11:45
You could try this: en.community.dell.com/techcenter/powergui/f/4834/t/19571829 — paul
– paul, Commented Mar 2, 2015 at 11:46

chris1982 · Accepted Answer · 2015-03-02 14:34:51Z

1

Thanks all (@Paul did suggest this in the comments). To resolve the issue I wrapped the items in the original select with an ISNULL().

$query = "SELECT [l].client_key, ISNULL([l].client_name) FROM [dbo].[test] AS [l]"

Not a particularly elegant solution but it solved the issue.

answered Mar 2, 2015 at 14:34

chris1982

2753 gold badges5 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

score 0 · Accepted Answer · 2015-03-02 11:30:52Z

0

Just a comment regarding your PowerShell syntax: don't make it harder than necessary.

That here-string is pointless, just make a regular string:

$extractFile = "G:\system_Lookup_Tables\system_agent_lookup.csv"

This

$connectionTemplate = "Data Source={0};Integrated Security=SSPI;Initial Catalog={1};"
$connectionString = [string]::Format($connectionTemplate, $server, $database)
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString

can be rewritten like that:

$connection = New-Object System.Data.SqlClient.SqlConnection("Data Source=$server;Integrated Security=SSPI;Initial Catalog=$database;")

And this

$command = New-Object System.Data.SqlClient.SqlCommand
$command.CommandText = $query
$command.Connection = $connection

like so:

$command = New-Object System.Data.SqlClient.SqlCommand($query, $connection)

Same thing with the SqlDataAdapter.

edited Mar 2, 2015 at 11:30

answered Mar 2, 2015 at 11:22

user2226112

1 Comment

chris1982 Over a year ago

Thanks. I thought this wasn't particularly well written - I'll re-write and try to implement your suggestions.

Collectives™ on Stack Overflow

Exporting from SQL using POWERSHELL where the output contains NULLS

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related