How to format output when exporting SQL query to CSV

Question

I have a task to save the results of a SQL Server query into a .csv file. After some googling I decided to use PowerShell. I found a script, modified it a bit, it works and almost all is ok.

$server = "server"
$database = "database"
$query = "SELECT * from et_thanks"

$tod = Get-Date;
$file = "{0:yyyyMMdd}_go.csv" -f $tod;
$extractFile = @"
\\info\export_files\$file
"@

$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 -Force -Delimiter ";" $extractFile

But I have 2 problems which I can't solve:

When I open the .csv file I see columns headers and commented string on first line:

#TYPE System.Data.DataRow
"ob_no","c_name","c_visible","c_fp","e_from","e_to"
"436439","09.09.2013 11:29:08","0","","10937","260153"

How can I get rid of it?

All values are surrounded with quotes. Is it possible to modify script not to use it while exporting? Autoreplace isn't good idea, cause there is a possibility that quote symbol can be found in sql data.

I tried to find answers in documentation (http://ss64.com/ps/export-csv.html) but with no success.

David Martin · Accepted Answer · 2014-01-15 10:16:42Z

3

You might run in to trouble removing the quotes, but if that's what you really want then the following should achieve it.

-NoTypeInformation will remove the additional type information you are seeing.

($DataSet.Tables[0] | ConvertTo-Csv -Delimiter ";" -NoTypeInformation) -replace "`"", "" | `
Out-File -Force $extractFile

This uses convertto-csv to convert to a string representation of the csv followed by replacing all instances of " with nothing and the final string is piped to Out-File.

answered Jan 15, 2014 at 10:16

David Martin

12.4k2 gold badges67 silver badges75 bronze badges

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

1 Comment

Cove Over a year ago

Thanks David, -1 problem. It seems to me that it's impossible to get read of quotes and column name, cause export goes via csv-standarts. Export-Csv cmdlet not the very best idea for my task.

user1390375 · Accepted Answer · 2014-03-13 00:27:07Z

1

...and, to get rid of the header record, if you first convert the data to csv (Convert-Csv), then pipe those results to Select to skip the 1st record:

($DataSet.Tables[0] | ConvertTo-Csv -Delimiter "`t" -NoTypeInformation ) -Replace "`"","" | Select -skip 1 | Out-File blahblahblah...

answered Mar 13, 2014 at 0:27

user1390375

7566 silver badges5 bronze badges

Comments

Chad Miller · Accepted Answer · 2014-01-19 15:45:20Z

0

Agreed export-csv isn't the best tool for the job. I would use sqlcmd.exe or bcp.exe provided SQL Server command-lines tools are installed. You could also build a simple routine to create a CSV from a datatable:

$result = new-Object text.stringbuilder
$dt = $DataSet.Tables[0]

foreach ($dr in $dt.Rows) {
           for ($i = 0; $i -lt $dt.Columns.Count; $i++) {

                $null = $result.Append($($dr[$i]).ToString())
                $null = $result.Append($(if ($i -eq $dt.Columns.Count - 1) {"`n" } else { ","} ))
            }
}

$result.ToString()

answered Jan 19, 2014 at 15:45

Chad Miller

42.4k4 gold badges33 silver badges34 bronze badges

Collectives™ on Stack Overflow

How to format output when exporting SQL query to CSV

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related