How to format output when exporting SQL query to CSV
Solution 1
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.
Solution 2
...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...
Cove
Updated on June 18, 2022Comments
-
Cove almost 2 years
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.
-