Como exportar dados como formato CSV do SQL Server usando sqlcmd?

Eu posso facilmente despejar dados em um arquivo de texto como:

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

No entanto, observei os arquivos de ajuda do SQLCMD mas não vi uma opção específica para o CSV.

Existe uma maneira de despejar dados de uma tabela em um arquivo de texto CSV usando o SQLCMD ?

Você pode executar algo assim:

 sqlcmd -S MyServer -d myDB -E -Q "select col1, col2, col3 from SomeTable" -o "MyData.csv" -h-1 -s"," -w 700 
  • -h-1 remove headers de nome de coluna do resultado
  • -s"," define o separador da coluna como
  • -w 700 define a largura da linha para 700 caracteres (isso precisará ser tão grande quanto a linha mais longa ou será encerrado na próxima linha)
 sqlcmd -S myServer -d myDB -E -o "MyData.txt" ^ -Q "select bar from foo" ^ -W -w 999 -s"," 

A última linha contém opções específicas de CSV.

  • -W remove espaços à direita de cada campo individual
  • -s"," define o separador da coluna para a vírgula (,)
  • -w 999 define a largura da linha para 999 caracteres

A resposta do scottm é muito parecida com a que eu uso, mas eu acho que o -W é uma adição muito boa: eu não preciso cortar o espaço em branco quando eu consumir o CSV em outro lugar.

Veja também a referência sqlcmd do MSDN . Coloca o /? saída da opção para vergonha.

Isso não é bcp foi feito para?

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

Execute isso na sua linha de comando para verificar a syntax.

 bcp /? 

Por exemplo:

 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] 

Por favor, note que o bcp não pode produzir headers de coluna.

Veja: Página de documentação do utilitário bcp .

Exemplo da página acima:

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

Com o PowerShell, você pode resolver o problema ordenadamente, conectando o Invoke-Sqlcmd ao Export-Csv.

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

O SQL Server 2016 inclui o módulo SqlServer , que contém o cmdlet Invoke-Sqlcmd , que você terá mesmo que você instale o SSMS 2016. Antes disso, o SQL Server 2012 incluía o antigo módulo SQLPS , que alteraria o diretório atual para SQLSERVER:\ quando o módulo foi usado pela primeira vez (entre outros bugs), então para isso, você precisará alterar a linha #Requires acima para:

 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 o exemplo do SQL Server 2008 e 2008 R2, remova a linha #Requires completamente e use o utilitário sqlps.exe em vez do host padrão do PowerShell.

Invoke-Sqlcmd é o equivalente ao PowerShell do sqlcmd.exe. Em vez de texto, ele gera objects System.Data.DataRow .

O parâmetro -Query funciona como o parâmetro -Q do sqlcmd.exe. Passe uma consulta SQL que descreva os dados que você deseja exportar.

O parâmetro -Database funciona como o parâmetro -d do sqlcmd.exe. Passe o nome do database que contém os dados a serem exportados.

O parâmetro -Server funciona como o parâmetro -S do sqlcmd.exe. Passe o nome do servidor que contém os dados a serem exportados.

Export-CSV é um cmdlet do PowerShell que serializa objects genéricos para CSV. Ele vem com o PowerShell.

O parâmetro -NoTypeInformation suprime a saída extra que não faz parte do formato CSV. Por padrão, o cmdlet grava um header com informações de tipo. Ele permite que você saiba o tipo do object quando você desserializa-lo posteriormente com Import-Csv , mas confunde ferramentas que esperam CSV padrão.

O parâmetro -Path funciona como o parâmetro -o do sqlcmd.exe. Um caminho completo para este valor é mais seguro se você estiver preso usando o módulo SQLPS antigo.

O parâmetro -Encoding funciona como os parâmetros -f ou -u do sqlcmd.exe. Por padrão, Export-Csv exibe somente caracteres ASCII e substitui todos os outros por pontos de interrogação. Use UTF8 para preservar todos os caracteres e permanecer compatível com a maioria das outras ferramentas.

A principal vantagem desta solução sobre sqlcmd.exe ou bcp.exe é que você não precisa hackear o comando para gerar CSV válido. O cmdlet Export-Csv manipula tudo para você.

A principal desvantagem é que o Invoke-Sqlcmd lê todo o conjunto de resultados antes de transmiti-lo ao longo do pipeline. Certifique-se de ter memory suficiente para todo o conjunto de resultados que deseja exportar.

Pode não funcionar suavemente para bilhões de linhas. Se isso é um problema, você poderia tentar as outras ferramentas, ou rolar sua própria versão eficiente do Invoke-Sqlcmd usando a class System.Data.SqlClient.SqlDataReader .

Uma nota para quem quer fazer isso, mas também tem os headers de coluna, esta é a solução que eu usei um arquivo de lote:

 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 

Isso gera os resultados iniciais (incluindo os separadores —-, —- entre os headers e os dados) em um arquivo temporário e, em seguida, remove essa linha, filtrando-a através de findstr. Note que não é perfeito, já que está sendo filtrado -,- não funcionará se houver apenas uma coluna na saída, e também filtrará as linhas legítimas que contêm essa string.

Opção alternativa com o BCP:

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

Esta resposta baseia-se na solução do @ iain-elder, que funciona bem, exceto no caso do grande database (como apontado em sua solução). A tabela inteira precisa caber na memory do seu sistema e, para mim, isso não era uma opção. Eu suspeito que a melhor solução seria usar o System.Data.SqlClient.SqlDataReader e um serializador CSV personalizado ( veja aqui para um exemplo ) ou outro idioma com um driver MS SQL e serialização CSV. No espírito da pergunta original, que provavelmente estava procurando uma solução sem dependência, o código do PowerShell abaixo funcionou para mim. É muito lento e ineficiente, especialmente ao instanciar o array $ data e chamar Export-Csv no modo append para cada linha $ chunk_size.

 $chunk_size = 10000 $command = New-Object System.Data.SqlClient.SqlCommand $command.CommandText = "SELECT * FROM " $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() 

Normalmente o sqlcmd vem com o utilitário bcp (como parte do mssql-tools ) que exporta para o CSV por padrão.

Uso:

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

Por exemplo:

 bcp.exe MyTable out data.csv 

Para despejar todas as tabelas em arquivos CSV correspondentes, aqui está o 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 $( $table.desc && \ bcp $table out $table.csv -S $server -U $user -P $pass -d $dbname -c done 

Uma resposta acima quase resolveu isso para mim, mas não cria corretamente um CSV analisado.

Aqui está minha versão:

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

Alguém dizendo que o sqlcmd está desatualizado em favor de alguma alternativa do PowerShell está esquecendo que o sqlcmd não é apenas para o Windows. Estou no Linux (e quando no Windows eu evito PS de qualquer maneira).

Tendo dito tudo isso, eu acho o bcp mais fácil.

Você pode fazer isso de uma maneira estranha. Cuidado com o uso do sqlcmd hack. Se os dados tiverem aspas duplas ou vírgulas, você terá problemas.

Você pode usar um script simples para fazer isso corretamente:

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

Fonte: Escrevendo a saída SQL para CSV com VBScript .