Я могу легко сбрасывать данные в текстовый файл, например:
sqlcmd -S myServer -d myDB -E -Q "select col1, col2, col3 from SomeTable"
-o "MyData.txt"
Однако я просмотрел файлы справки для SQLCMD, но не видел вариант специально для CSV.
Есть ли способ отправить данные из таблицы в текстовый файл CSV с помощью SQLCMD?
Вы можете запустить что-то вроде этого:
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 -s.
-W удаляет завершающие пробелы из каждого отдельного поля-s"," устанавливает -s"," столбцов на запятую (,)-W 999 устанавливает ширину строки в 999 символов Скотт ответ очень близок к тому, что я использую, но я считаю -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 , ...
С PowerShell вы можете аккуратно решить эту проблему, отправив Invoke -S qlcmd в Export-Csv.
#Requires -Module SqlServer
Invoke-Sqlcmd -Query "SELECT * FROM DimDate;" '
-Database AdventureWorksDW2012 '
-Server localhost |
Export-Csv -NoTypeInformation '
-Path "DimDate.csv" '
-Encoding UTF8
SQL Server 2016 включает в себя модуль SqlServer, который содержит командлет Invoke-Sqlcmd, который у вас будет, даже если вы просто установите SSMS 2016. До этого SQL Server 2012 включал старый модуль 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
Чтобы адаптировать пример для SQL Server 2008 и 2008 R2, полностью удалите строку #Requires и используйте утилиту sqlps.exe вместо стандартного хоста PowerShell.
Invoke -S qlcmd является эквивалентом PowerShell для sqlcmd.exe. Вместо текста он выводит объекты System.Data.DataRow.
Параметр -Query работает как параметр -Q в sqlcmd.exe. Передайте ему запрос SQL, который описывает данные, которые вы хотите экспортировать.
Параметр -Database работает подобно параметру -d в sqlcmd.exe. Передайте ему имя базы данных, которая содержит данные для экспорта.
Параметр -Server работает аналогично параметру -S в sqlcmd.exe. Передайте ему имя сервера, который содержит данные для экспорта.
Export-CSV - это командлет PowerShell, который сериализует общие объекты в CSV. Поставляется с PowerShell.
Параметр -NoTypeInformation подавляет дополнительный вывод, который не является частью формата CSV. По умолчанию командлет записывает заголовок с информацией о типе. Он позволяет вам узнать тип объекта при последующей десериализации его с помощью Import-Csv, но это сбивает с толку инструменты, которые ожидают стандартного CSV.
Параметр -Path работает подобно параметру -o в sqlcmd.exe. Полный путь к этому значению наиболее безопасен, если вы застряли, используя старый модуль SQLPS.
Параметр -Encoding работает подобно параметрам -f или -u в sqlcmd.exe. По умолчанию Export-Csv выводит только символы ASCII и заменяет все остальные знаками вопроса. Вместо этого используйте UTF8, чтобы сохранить все символы и сохранить совместимость с большинством других инструментов.
Основное преимущество этого решения перед sqlcmd.exe или bcp.exe заключается в том, что вам не нужно взламывать команду для вывода действительного CSV. Командлет Export-Csv обрабатывает все это за вас.
Основным недостатком является то, что Invoke-Sqlcmd читает весь набор результатов перед передачей его по конвейеру. Убедитесь, что у вас достаточно памяти для всего набора результатов, который вы хотите экспортировать.
Это может не работать гладко для миллиардов строк. Если это проблема, вы можете попробовать другие инструменты или развернуть собственную эффективную версию Invoke-Sqlcmd используя класс System.Data.SqlClient.SqlDataReader.
Заметка для тех, кто хочет это сделать, но также имеет заголовки столбцов, это решение, в котором я использовал пакетный файл:
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. Обратите внимание, что это не идеально, поскольку он отфильтровывает -,- — он не будет работать, если на выходе будет только один столбец, а также будет отфильтровывать законные строки, содержащие эту строку.
Альтернативный вариант с BCP:
exec master..xp_cmdshell 'BCP "sp_who" QUERYOUT C:\av\sp_who.txt -S MC0XENTC -T -c '
Этот ответ основывается на решении от @iain-old, который хорошо работает, за исключением большого случая с базой данных (как указано в его решении). Вся таблица должна соответствовать вашей системной памяти, и для меня это не вариант. Я подозреваю, что лучшее решение будет использовать System.Data.SqlClient.SqlDataReader и пользовательский сериализатор CSV (см. Здесь для пример) или на другом языке с драйвером MS SQL и сериализацией CSV. В духе оригинального вопроса, который, вероятно, искал никакого решения о зависимости, код PowerShell ниже работал у меня. Это очень медленно и неэффективно, особенно при создании массива $data и вызове Export-Csv в режиме добавления для каждой строки $chunk_size.
$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?
[email protected]()
$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, вот скрипт Bash:
#!/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:
Имя пользователя и пароль для входа иногда необходимы для запроса удаленного экземпляра SQL Server
sqlcmd -U [your_User] -P[your_password] -S [your_remote_Server] -d [your_databasename] -i "query.txt" -o "output.csv" -s"," -w 700
Вы можете сделать это хакерским способом. Осторожно, используя взломать sqlcmd. Если данные имеют двойные кавычки или запятые, у вас возникнут проблемы.
Вы можете использовать простой script, чтобы сделать это правильно:
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' 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 protected]" ' 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!")
Источник: Написание вывода SQL в CSV с VBScript.