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

ejecutar bcp desde sql server

Question

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 usando SQLCMD ?

Answer 1

¿No es esto para lo que fue diseñado el bcp ?

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 las columnas.

Ver: página de documentación de utilidad de bcp .

Ejemplo de la página de arriba:

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

Answer 2

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 módulo SqlServer , que contiene el cmdlet Invoke-Sqlcmd , que tendrá incluso si solo instala SSMS 2016. Antes de eso, SQL Server 2012 incluía el antiguo módulo SQLPS , que cambiaría el directorio actual a SQLSERVER:/ cuando el módulo se usó por primera vez (entre otros errores) por lo que para ello, tendrá que cambiar la línea #Requires 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 por completo la línea #Requires y use la utilidad sqlps.exe en lugar del host de PowerShell estándar.

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

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

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

El parámetro -Server funciona como el parámetro -S 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.

El parámetro -NoTypeInformation 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 adelante con Import-Csv , pero confunde herramientas que esperan CSV estándar.

El parámetro -Path funciona como el parámetro -o de sqlcmd.exe. Una ruta completa para este valor es más segura si está atrapado utilizando el antiguo módulo SQLPS .

El parámetro -Encoding funciona como los parámetros -f o -u 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 canalización. 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 la clase System.Data.SqlClient.SqlDataReader .

Answer 3

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 System.Data.SqlClient.SqlDataReader y un serializador CSV personalizado ( ver aquí un ejemplo ) u otro lenguaje 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()

Answer 4

Opción alternativa con BCP:

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

Answer 5

Por sqlcmd general, sqlcmd viene con la utilidad bcp (como parte de mssql-tools ) que exporta a CSV de forma predeterminada.

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 script 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

Answer 6

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 encabezados de nombre de columna del resultado
  
• -s"," establece el separador de columna a,
  
• -w 700 establece el ancho de fila a 700 caracteres (esto tendrá que ser tan ancho como la fila más larga o se ajustará a la siguiente línea)

Answer 7

Puedes hacerlo de una manera hackish. Cuidado al usar el truco sqlcmd . 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 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!")

^{Fuente: escritura de salida de SQL a CSV con VBScript .}

Answer 8

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

Answer 9

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 que dice que sqlcmd está desactualizado a favor de alguna alternativa de PowerShell es olvidar que sqlcmd no es solo para Windows. Estoy en Linux (y cuando en Windows evito PS de todos modos).

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

Answer 10

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 es muy similar a la que uso, pero creo que la -W es una adición realmente agradable: no necesito recortar el espacio en blanco cuando consumo el CSV en otro lugar.

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