如何使用 sqlcmd 从 SQLServer 以 CSV 格式导出数据？

我可以很容易地将数据转储到一个文本文件中，比如:

sqlcmd -S myServer -d myDB -E -Q "select col1, col2, col3 from SomeTable"
-o "MyData.txt"

但是，我已经查看了 SQLCMD的帮助文件，但是没有看到针对 CSV 的特定选项。

有没有一种方法可以使用 SQLCMD将数据从表转储到 CSV 文本文件中？

476897 次浏览
小开

你可以用黑客的方式来做。小心使用 sqlcmd黑客技术。如果数据包含双引号或逗号，则会遇到麻烦。

您可以使用一个简单的脚本来正确执行:

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Data Exporter                                                 '
'                                                               '
' Description: Allows the output of data to CSV file from a SQL '
'       statement to either Oracle, SQL Server, or MySQL        '
' Author: C. Peter Chen, http://dev-notes.com                   '
' Version Tracker:                                              '
'       1.0   20080414 Original version                         '
'   1.1   20080807 Added email functionality                '
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
option explicit
dim dbType, dbHost, dbName, dbUser, dbPass, outputFile, email, subj, body, smtp, smtpPort, sqlstr


'''''''''''''''''
' Configuration '
'''''''''''''''''
dbType = "oracle"                 ' Valid values: "oracle", "sqlserver", "mysql"
dbHost = "dbhost"                 ' Hostname of the database server
dbName = "dbname"                 ' Name of the database/SID
dbUser = "username"               ' Name of the user
dbPass = "password"               ' Password of the above-named user
outputFile = "c:\output.csv"      ' Path and file name of the output CSV file
email = "email@me.here"           ' Enter email here should you wish to email the CSV file (as attachment); if no email, leave it as empty string ""
subj = "Email Subject"          ' The subject of your email; required only if you send the CSV over email
body = "Put a message here!"    ' The body of your email; required only if you send the CSV over email
smtp = "mail.server.com"        ' Name of your SMTP server; required only if you send the CSV over email
smtpPort = 25                   ' SMTP port used by your server, usually 25; required only if you send the CSV over email
sqlStr = "select user from dual"  ' SQL statement you wish to execute
'''''''''''''''''''''
' End Configuration '
'''''''''''''''''''''






dim fso, conn


'Create filesystem object
set fso = CreateObject("Scripting.FileSystemObject")


'Database connection info
set Conn = CreateObject("ADODB.connection")
Conn.ConnectionTimeout = 30
Conn.CommandTimeout = 30
if dbType = "oracle" then
conn.open("Provider=MSDAORA.1;User ID=" & dbUser & ";Password=" & dbPass & ";Data Source=" & dbName & ";Persist Security Info=False")
elseif dbType = "sqlserver" then
conn.open("Driver={SQL Server};Server=" & dbHost & ";Database=" & dbName & ";Uid=" & dbUser & ";Pwd=" & dbPass & ";")
elseif dbType = "mysql" then
conn.open("DRIVER={MySQL ODBC 3.51 Driver}; SERVER=" & dbHost & ";PORT=3306;DATABASE=" & dbName & "; UID=" & dbUser & "; PASSWORD=" & dbPass & "; OPTION=3")
end if


' Subprocedure to generate data.  Two parameters:
'   1. fPath=where to create the file
'   2. sqlstr=the database query
sub MakeDataFile(fPath, sqlstr)
dim a, showList, intcount
set a = fso.createtextfile(fPath)


set showList = conn.execute(sqlstr)
for intcount = 0 to showList.fields.count -1
if intcount <> showList.fields.count-1 then
a.write """" & showList.fields(intcount).name & ""","
else
a.write """" & showList.fields(intcount).name & """"
end if
next
a.writeline ""


do while not showList.eof
for intcount = 0 to showList.fields.count - 1
if intcount <> showList.fields.count - 1 then
a.write """" & showList.fields(intcount).value & ""","
else
a.write """" & showList.fields(intcount).value & """"
end if
next
a.writeline ""
showList.movenext
loop
showList.close
set showList = nothing


set a = nothing
end sub


' Call the subprocedure
call MakeDataFile(outputFile,sqlstr)


' Close
set fso = nothing
conn.close
set conn = nothing


if email <> "" then
dim objMessage
Set objMessage = CreateObject("CDO.Message")
objMessage.Subject = "Test Email from vbs"
objMessage.From = email
objMessage.To = email
objMessage.TextBody = "Please see attached file."
objMessage.AddAttachment outputFile


objMessage.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
objMessage.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = smtp
objMessage.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = smtpPort


objMessage.Configuration.Fields.Update


objMessage.Send
end if


'You're all done!!  Enjoy the file created.
msgbox("Data Writer Done!")

资料来源: 用 VBScript 将 SQL 输出写入 CSV。

小开
最佳答案

你可以这样运行:

sqlcmd -S MyServer -d myDB -E -Q "select col1, col2, col3 from SomeTable"
-o "MyData.csv" -h-1 -s"," -w 700
  • -h-1从结果中删除列名标头
  • -s","将列分隔符设置为,
  • -w 700将行宽设置为700个字符(这将需要与最长的行一样宽，否则将换行到下一行)
小开 
sqlcmd -S myServer -d myDB -E -o "MyData.txt" ^
-Q "select bar from foo" ^
-W -w 999 -s","

最后一行包含特定于 CSV 的选项。

  • -W删除每个字段的尾随空格
  • -s","将列分隔符设置为逗号(,)
  • -w 999将行宽设置为999个字符

Scottm 的回答 与我使用的非常接近，但是我发现 -W是一个非常好的补充: 当我在其他地方使用 CSV 时，我不需要修剪空白。

也可以看看 MSDN sqlcmd 引用，它让 /?选项的输出相形见绌。

小开

这不是 bcp的目的吗？

bcp "select col1, col2, col3 from database.schema.SomeTable" queryout  "c:\MyData.txt"  -c -t"," -r"\n" -S ServerName -T

从命令行运行此命令以检查语法。

bcp /?

例如:

usage: bcp {dbtable | query} {in | out | queryout | format} datafile
[-m maxerrors]            [-f formatfile]          [-e errfile]
[-F firstrow]             [-L lastrow]             [-b batchsize]
[-n native type]          [-c character type]      [-w wide character type]
[-N keep non-text native] [-V file format version] [-q quoted identifier]
[-C code page specifier]  [-t field terminator]    [-r row terminator]
[-i inputfile]            [-o outfile]             [-a packetsize]
[-S server name]          [-U username]            [-P password]
[-T trusted connection]   [-v version]             [-R regional enable]
[-k keep null values]     [-E keep identity values]
[-h "load hints"]         [-x generate xml format file]
[-d database name]

请注意，bcp不能输出列标题。

见: Bcp 实用程序文档页。

例如:

bcp.exe MyTable out "D:\data.csv" -T -c -C 65001 -t , ...
小开

对于那些希望这样做但是又有列标题的人，需要注意的是，这是我使用批处理文件的解决方案:

sqlcmd -S servername -U username -P password -d database -Q "set nocount on; set ansi_warnings off; sql query here;" -o output.tmp -s "," -W
type output.tmp | findstr /V \-\,\- > output.csv
del output.tmp

这会将初始结果(包括标头和数据之间的—— ，——分隔符)输出到一个临时文件中，然后通过 findstr 过滤掉该行，从而删除该行。注意，它并不完美，因为它过滤掉了 -,- & mash; 如果输出中只有一列，它就不会工作，它还会过滤掉包含该字符串的合法行。

小开

使用 PowerShell，您可以通过将 Invoke-Sqlcmd连接到 Export-Csv来巧妙地解决这个问题。

#Requires -Module SqlServer
Invoke-Sqlcmd -Query "SELECT * FROM DimDate;" `
-Database AdventureWorksDW2012 `
-Server localhost |
Export-Csv -NoTypeInformation `
-Path "DimDate.csv" `
-Encoding UTF8

Invoke-Sqlcmd 是相当于 sqlcmd.exe 的 PowerShell，它输出的不是文本，而是 System.Data.DataRow对象。

-Query参数的工作方式与 sqlcmd.exe 的 -Q参数类似。传递一个描述要导出的数据的 SQL 查询。

-Database参数的工作方式与 sqlcmd.exe 的 -d参数类似。将包含要导出的数据的数据库名称传递给它。

-Server参数的工作方式与 sqlcmd.exe 的 -S参数类似。传递包含要导出的数据的服务器的名称。

Export-CSV 是一个 PowerShell cmdlet，它将通用对象序列化为 CSV。

-NoTypeInformation参数抑制不属于 CSV 格式的额外输出。默认情况下，cmdlet 将使用类型信息写入标头。当您稍后使用 Import-Csv反序列化对象时，它可以让您知道对象的类型，但是它混淆了期望标准 CSV 的工具。

-Path参数的工作方式与 sqlcmd.exe 的 -o参数类似。

-Encoding参数的工作方式类似于 sqlcmd.exe 的 -f或 -u参数。默认情况下，Export-CSv 只输出 ASCII 字符，并用问号替换所有其他字符。使用 UTF8代替保留所有字符，并与大多数其他工具保持兼容。

与 sqlcmd.exe 或 bcp.exe 相比，这种解决方案的主要优势在于不需要修改命令来输出有效的 CSV。Export-CSV cmdlet 为您处理所有这些事情。

主要缺点是，Invoke-Sqlcmd在沿管道传递结果集之前会读取整个结果集。确保您有足够的内存来导出整个结果集。

对于数十亿行，它可能无法正常工作。如果这是一个问题，您可以尝试其他工具，或者使用 系统。数据。 SqlClient.SqlDataReader类滚动自己的高效 Invoke-Sqlcmd版本。

SQLServer 版本之间的差异

从 SQLServer2016开始，Invoke-Sqlcmd作为 SqlServer模块的一部分发布。

SQLServer2012使用的是旧的 SQLPS 模组。当导入模块时，它将当前位置更改为 SQLSERVER:\。所以你需要把上面的 #Requires行改为:

Push-Location $PWD
Import-Module -Name SQLPS
# dummy query to catch initial surprise directory change
Invoke-Sqlcmd -Query "SELECT 1" `
-Database  AdventureWorksDW2012 `
-Server localhost |Out-Null
Pop-Location
# actual Invoke-Sqlcmd |Export-Csv pipeline

如果使用旧的 SQLPS 模块，Export-Csv的 -Path参数的完整路径是最安全的。

为了使该示例适用于 SQLServer2008和2008R2，请完全删除 #Requires行，并使用 Exe 实用程序代替标准的 PowerShell 主机。

小开

BCP 的备选方案:

exec master..xp_cmdshell 'BCP "sp_who" QUERYOUT C:\av\sp_who.txt -S MC0XENTC -T -c '
小开

这个答案建立在@iain-elder 的解决方案之上，该解决方案除了大型数据库案例(正如他的解决方案中所指出的)之外运行良好。整个表需要放入您的系统内存，而对我来说，这不是一个选项。我认为最好的解决方案是使用 系统。数据。 SqlClient.SqlDataReader和定制的 CSV 序列化器(看这里的例子) ，或者使用带有 MS SQL 驱动程序和 CSV 序列化的其他语言。最初的问题可能是寻找一个没有依赖关系的解决方案，本着这个精神，下面的 PowerShell 代码对我很有用。特别是在实例化 $data 数组和在追加模式下为每个 $block _ size 行调用 Export-CSv 时，这种方法非常缓慢和低效。

$chunk_size = 10000
$command = New-Object System.Data.SqlClient.SqlCommand
$command.CommandText = "SELECT * FROM <TABLENAME>"
$command.Connection = $connection
$connection.open()
$reader = $command.ExecuteReader()


$read = $TRUE
while($read){
$counter=0
$DataTable = New-Object System.Data.DataTable
$first=$TRUE;
try {
while($read = $reader.Read()){


$count = $reader.FieldCount
if ($first){
for($i=0; $i -lt $count; $i++){
$col = New-Object System.Data.DataColumn $reader.GetName($i)
$DataTable.Columns.Add($col)
}
$first=$FALSE;
}


# Better way to do this?
$data=@()
$emptyObj = New-Object System.Object
for($i=1; $i -le $count; $i++){
$data +=  $emptyObj
}


$reader.GetValues($data) | out-null
$DataRow = $DataTable.NewRow()
$DataRow.ItemArray = $data
$DataTable.Rows.Add($DataRow)
$counter += 1
if ($counter -eq $chunk_size){
break
}
}
$DataTable | Export-Csv "output.csv" -NoTypeInformation -Append
}catch{
$ErrorMessage = $_.Exception.Message
Write-Output $ErrorMessage
$read=$FALSE
$connection.Close()
exit
}
}
$connection.close()
小开

通常 sqlcmd附带 bcp实用程序(作为 mssql-tools的一部分) ，它默认导出为 CSV。

用法:

bcp {dbtable | query} {in | out | queryout | format} datafile

例如:

bcp.exe MyTable out data.csv

要将所有表转储到相应的 CSV 文件中，以下是 巴斯脚本:

#!/usr/bin/env bash
# Script to dump all tables from SQL Server into CSV files via bcp.
# @file: bcp-dump.sh
server="sql.example.com" # Change this.
user="USER" # Change this.
pass="PASS" # Change this.
dbname="DBNAME" # Change this.
creds="-S '$server' -U '$user' -P '$pass' -d '$dbname'"
sqlcmd $creds -Q 'SELECT * FROM sysobjects sobjects' > objects.lst
sqlcmd $creds -Q 'SELECT * FROM information_schema.routines' > routines.lst
sqlcmd $creds -Q 'sp_tables' | tail -n +3 | head -n -2 > sp_tables.lst
sqlcmd $creds -Q 'SELECT name FROM sysobjects sobjects WHERE xtype = "U"' | tail -n +3 | head -n -2 > tables.lst


for table in $(<tables.lst); do
sqlcmd $creds -Q "exec sp_columns $table" > $table.desc && \
bcp $table out $table.csv -S $server -U $user -P $pass -d $dbname -c
done
小开

上面的一个答案几乎为我解决了这个问题，但是它没有正确地创建一个已解析的 CSV。

我的版本是这样的:

sqlcmd -S myurl.com -d MyAzureDB -E -s, -W -i mytsql.sql | findstr /V /C:"-" /B > parsed_correctly.csv

有人说 sqlcmd已经过时了，而更喜欢 PowerShell 的替代品，这是忘记了 sqlcmd不仅仅适用于 Windows。我使用的是 Linux (在 Windows 上我尽量避免使用 PS)。

说了这么多，我确实发现 bcp更容易。

小开

由于以下两个原因，您应该在 CMD 中运行我的解决方案:

  1. 查询中可能有双引号

  2. 登录用户名和密码有时是查询远程 SQLServer 实例所必需的

    sqlcmd -U [your_User]  -P[your_password] -S [your_remote_Server] -d [your_databasename]  -i "query.txt" -o "output.csv" -s"," -w 700
小开

尝试使用 python 包 sqlcmd-csv将逗号分隔的输出后处理到有效的 csv。

Https://github.com/shadiakiki1986/sqlcmd-csv

sqlcmd ... -s, ...
pip install git+https://github.com/shadiakiki1986/sqlcmd-csv.git
sqlcmd_csv out.txt out.csv

提交答案