将SQL查询导出为CSV时如何格式化输出 [英] How to format output when exporting SQL query to CSV

问题描述

我有一个任务将SQL Server查询的结果保存到 .csv 文件中。在一些谷歌搜索后，我决定使用PowerShell。我发现一个脚本，修改了一点，它的工作原理，几乎所有的确定。

  $ 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};集成安全性= SSPI;初始目录= {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 
  

但我有2个问题，我无法解决：

1. 当我打开 .csv 文件时，我看到第一行的列标题和注释字符串： / p>
   
   

     #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
     




2. 所有的值都被包围报价。在导出时是否可以修改脚本不使用它？自动重新排列是不好的主意，因为有可能在SQL数据中可以找到报价符号。

我尝试在文档中找到答案（ http://ss64.com/ps/export-csv.html ），但没有成功。

解决方案

您可能会遇到麻烦，删除引号，但如果这是你真正想要的，那么以下应该实现它。

-NoTypeInformation 会删除您看到的其他类型信息。

 （$ DataSet.Tables [ 0] | ConvertTo-Csv -Delimiter;-NoTypeInformation）-replace`，|`
 Out-File -Force $ extractFile 
  

这使用 convertto-csv 转换为csv的字符串表示，然后替换无的所有实例，最终字符串通过管道传递到 Out-File 。

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:

1. 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?

2. 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.

解决方案

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.

这篇关于将SQL查询导出为CSV时如何格式化输出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！