Pregunta ¿Cómo exportar datos como formato CSV desde SQL Server usando sqlcmd?


Puedo fácilmente volcar datos en un archivo de texto como:

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

Sin embargo, he consultado los archivos de ayuda para SQLCMD pero no he visto una opción específica para CSV.

¿Hay alguna manera de volcar datos de una tabla en un archivo de texto CSV utilizando SQLCMD?


103
2018-01-08 18:49


origen


Respuestas:


Puedes ejecutar algo como esto:

sqlcmd -S MyServer -d myDB -E -Q "select col1, col2, col3 from SomeTable" 
       -o "MyData.csv" -h-1 -s"," -w 700
  • -h-1 elimina los encabezados de nombre de columna del resultado
  • -s"," establece el separador de columna en,
  • -w 700 establece el ancho de la fila a 700 caracteres (esto tendrá que ser tan ancho como la fila más larga o se ajustará a la siguiente línea)

109
2018-01-08 19:08



sqlcmd -S myServer -d myDB -E -o "MyData.txt" ^
    -Q "select bar from foo" ^
    -W -w 999 -s","

La última línea contiene opciones específicas de CSV.

  • -W eliminar espacios finales de cada campo individual
  • -s"," establece el separador de columna en la coma (,)
  • -w 999 establece el ancho de fila a 999 caracteres

la respuesta de scottm está muy cerca de lo que uso, pero encuentro el -W para ser una adición realmente agradable: no necesito recortar el espacio en blanco cuando consumo el CSV en otro lugar.

Ver también el Referencia sqlcmd de MSDN. Pone el /? salida de la opción a la vergüenza.


59
2018-03-11 16:42



¿No es esto? bcp fue destinado para?

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

Ejecute esto desde su línea de comando para verificar la sintaxis.

bcp /?

Por ejemplo:

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]

Tenga en cuenta que bcp no puede dar salida a los encabezados de columna.

Ver: utilidad bcp página de documentos.

Ejemplo de la página de arriba:

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

56
2018-03-25 05:19



Con PowerShell puede resolver el problema ordenadamente insertando Invoke-Sqlcmd en 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 incluye el Servidor SQL módulo, que contiene el Invoke-Sqlcmd cmdlet, que tendrá incluso si solo instala SSMS 2016. Antes de eso, SQL Server 2012 incluía el antiguo SQLPS módulo, que cambiaría el directorio actual a SQLSERVER:\ cuando el módulo se usó por primera vez (entre otros errores), para ello necesitará cambiar el #Requires línea arriba a:

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

Para adaptar el ejemplo de SQL Server 2008 y 2008 R2, elimine #Requires línea por completo y utilizar el utilidad sqlps.exe en lugar del host PowerShell estándar.

Invoke-Sqlcmd es el equivalente de PowerShell de sqlcmd.exe. En lugar de texto, sale System.Data.DataRow objetos.

los -Query el parámetro funciona como -Q parámetro de sqlcmd.exe. Pase una consulta SQL que describa los datos que desea exportar.

los -Database el parámetro funciona como -d parámetro de sqlcmd.exe. Pase el nombre de la base de datos que contiene los datos que se exportarán.

los -Server el parámetro funciona como -S parámetro de sqlcmd.exe. Pase el nombre del servidor que contiene los datos que se exportarán.

Export-CSV es un cmdlet de PowerShell que serializa objetos genéricos a CSV. Se envía con PowerShell.

los -NoTypeInformation parámetro suprime el resultado adicional que no forma parte del formato CSV. De forma predeterminada, el cmdlet escribe un encabezado con información de tipo. Te permite saber el tipo de objeto cuando lo deserializas más tarde con Import-Csv, pero confunde las herramientas que esperan CSV estándar.

los -Path el parámetro funciona como -o parámetro de sqlcmd.exe. Una ruta completa para este valor es más segura si está atrapado usando el viejo SQLPSmódulo.

los -Encoding el parámetro funciona como -f o -u parámetros de sqlcmd.exe. De forma predeterminada, Export-Csv genera solo caracteres ASCII y reemplaza a todos los demás con signos de interrogación. Use UTF8 en su lugar para conservar todos los caracteres y ser compatible con la mayoría de las otras herramientas.

La principal ventaja de esta solución sobre sqlcmd.exe o bcp.exe es que no tiene que hackear el comando para generar un CSV válido. El cmdlet Export-Csv lo maneja todo por ti.

La principal desventaja es que Invoke-Sqlcmd lee todo el conjunto de resultados antes de pasarlo a lo largo de la tubería. Asegúrese de tener suficiente memoria para todo el conjunto de resultados que desea exportar.

Es posible que no funcione sin problemas en miles de millones de filas. Si eso es un problema, puede probar las otras herramientas, o lanzar su propia versión eficiente de Invoke-Sqlcmd utilizando System.Data.SqlClient.SqlDataReader clase.


48
2017-08-31 16:04



Una nota para cualquiera que quiera hacer esto pero que también tenga los encabezados de las columnas, esta es la solución que utilicé con un archivo por lotes:

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

Esto genera los resultados iniciales (incluidos los separadores ----, ---- entre los encabezados y los datos) en un archivo temporal, luego elimina esa línea filtrándola a través de findstr. Tenga en cuenta que no es perfecto ya que se está filtrando -,--No funcionará si solo hay una columna en el resultado, y también filtrará las líneas legítimas que contienen esa cadena.


10
2017-11-16 05:04



Opción alternativa con BCP:

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

1
2018-06-27 21:05



Esta respuesta se basa en la solución de @ iain-elder, que funciona bien, excepto en el caso de la gran base de datos (como se señala en su solución). Toda la tabla debe caber en la memoria de su sistema, y ​​para mí esta no era una opción. Sospecho que la mejor solución usaría el System.Data.SqlClient.SqlDataReader y un serializador CSV personalizado (mira aquí para un ejemplo) u otro idioma con un controlador MS SQL y serialización CSV. En el espíritu de la pregunta original que probablemente buscaba una solución sin dependencia, el siguiente código de PowerShell funcionó para mí. Es muy lento e ineficiente, especialmente al crear instancias de la matriz $ data y al llamar a Export-Csv en el modo de adición para cada línea $ 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?
            $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()

0
2018-02-25 19:16



Generalmente sqlcmd viene con bcp utilidad (como parte de mssql-tools) que exporta a CSV por defecto.

Uso:

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

Por ejemplo:

bcp.exe MyTable out data.csv

Para volcar todas las tablas en los archivos CSV correspondientes, aquí está el Intento guión:

#!/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

0
2018-03-29 22:29



Una respuesta anterior casi me solucionó, pero no crea correctamente un CSV analizado.

Aquí está mi versión:

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

Alguien dice eso sqlcmd está desactualizado a favor de alguna alternativa de PowerShell es olvidarse de eso sqlcmd no es solo para Windows. Estoy en Linux (y cuando en Windows evito PS de todos modos).

Habiendo dicho todo eso, sí encuentro bcp más fácil.


0
2018-01-08 18:50



Puedes hacerlo de una manera hackish. Cuidado usando el sqlcmd cortar. Si los datos tienen comillas dobles o comas, se encontrará con problemas.

Puede usar un script simple para hacerlo correctamente:

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

Fuente: Escritura de salida de SQL a CSV con VBScript.


-4