Comment exporter des données au format CSV à partir de SQL Server à l'aide de l'utilitaire sqlcmd?

Vous pouvez exécuter quelque chose comme ceci:

sqlcmd -S MyServer -d myDB -E -Q "select col1, col2, col3 from SomeTable" 
       -o "MyData.csv" -h-1 -s"," -w 700

• -h-1 supprime le nom de la colonne en-têtes de la suite
• -s"," définit la colonne séparateur d' ,
• -w 700 définit la largeur de l'emprise de 700 caractères (ce qui devra être aussi large que la rangée la plus longue, ou il va automatiquement à la ligne suivante)

N'est-ce pas bcp était destiné?

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

L'exécuter à partir de votre ligne de commande pour vérifier la syntaxe.

bcp /?

Par exemple:

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]

S'il vous plaît, notez que bcp ne pouvez pas sortie en-têtes de colonne.

Voir: Utilitaire bcp docs page.

Exemple à partir de la page mentionnée ci-dessus:

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

Une remarque pour ceux qui cherchent à le faire, mais également les en-têtes de colonne, c'est la solution que j'ai utilisé un fichier de commandes:

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

Ce sorties les premiers résultats (y compris l' ----,---- séparateurs entre les en-têtes et les données) dans un fichier temporaire, puis supprime cette ligne par le filtrage par findstr. Notez qu'il n'est pas parfait, puisqu'il est le filtrage -,-—cela ne fonctionnera pas si il n'y a qu'une seule colonne de la sortie, et il sera également filtrer des lignes qui contiennent cette chaîne.

Cette réponse s'appuie sur la solution de @iain-frère, qui fonctionne bien, sauf pour la grande base de données de cas (comme l'a fait remarquer dans sa solution). L'ensemble de la table doit s'inscrire dans la mémoire de votre système, et pour moi ce n'était pas une option. Je crois que la meilleure solution serait d'utiliser la Système.Les données.SqlClient.SqlDataReader et personnalisé CSV sérialiseur (voir ici pour un exemple) ou une autre langue avec un MS SQL pilote et CSV de sérialisation. Dans l'esprit de la question d'origine qui était probablement à la recherche d'un pas de dépendance de la solution, le PowerShell code ci-dessous a fonctionné pour moi. Il est très lent et inefficace, en particulier dans l'instanciation de la variable $data array et en appelant à l'Exportation au format Csv en mode ajout pour chaque $chunk_size lignes.

$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()

Une réponse au-dessus de presque résolu pour moi, mais il ne prend pas correctement en créer un analysée CSV.

Voici ma version:

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

Quelqu'un dire que sqlcmd est obsolète en faveur de certains PowerShell alternative, c'est d'oublier que sqlcmd n'est pas seulement pour Windows. Je suis sur Linux (et quand sur Windows je éviter PS de toute façon).

Après avoir dit tout cela, je trouve bcp plus facile.

Vous pouvez le faire dans un hackish façon. Attention à l'aide de la sqlcmd hack. Si les données des guillemets ou des virgules, vous serez confronté à des problèmes.

Vous pouvez utiliser un script simple pour le faire correctement:

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' 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!")

^{Source: L'écriture SQL de sortie au format CSV avec VBScript.}