Rotina para exportar dados para o formato texto via BCP
Tempo de leitura: 7 minutosFala Galerinha!
Espero que estejam todos bem!!
No post de hoje vou compartilhar com vocês uma procedure muito útil que serviu de base para resolver uma necessidade de um cliente.
O cenário era o seguinte. O Cliente tinha algumas tabelas no seu banco de dados e precisava exportar esses dados diariamente para arquivos .txt de maneira automática uma vez por dia.
Existem algumas maneiras de se fazer isso, inclusive nativamente no SQL Server por meio do assistente de “Import/Export”. No entanto, utilizando esse assistente o processo fica manual.
Era necessário todos os dias atualizar esses dados em um diretório de saída.
Vamos fazer uma breve demonstração de como exportar uma tabela para .txt via assistente “Import/Export Data” manualmente e depois demonstrar a solução que pode ser automatizada.
Clique com o botão direito do mouse em cima do banco de dados no seu “SQL Server Management Studio”, depois vá em “tasks” ou (tarefas) e selecione a opção “Export Data”.
Siga os passos a seguir:
Clique em Next >.
Nas opções de Origem selecione a fonte de dados ao qual deseja exportar os seus dados. Nesse caso a origem dos nossos dados é uma tabela do banco “Teste_Blog” que está dentro do próprio SQL Server. Então selecionamos a opção SQL Server Native Client 11.0.
Feito isso, informe suas credenciais de acesso ao banco, selecione sua base de dados de origem e clique em Next >.
Agora vamos selecionar a etapa de destino, ou seja, para qual fonte de dados desejamos exportar os nossos dados. Neste exemplo, vamos selecionar a opção “Flat File Destination” para realizar a exportação para um arquivo de texto.
Em “Browse” selecione o local e o arquivo que deseja exportar os seus dados, você também pode selecionar um delimitador para exportar os seus arquivos, neste exemplo no campo “Text qualifier”, utilizamos um ponto e vírgula, para separar as colunas. Outra opção bastante utilizada é a flag “Column names in the first data row” que sinaliza que a primeira linha se refere ao cabeçalho da sua tabela.
Clique em Next >.
Nessa etapa, você pode selecionar uma tabela ou escrever uma query para exportar os seus dados. Bem legal né? ?
Neste caso vamos manter a primeira opção, clique em Next >.
Selecione a tabela que deseja exportar, neste exemplo, minha tabela chama-se “Usuário”. Clique em Next >.
Marque a opção “Run Immediately” e clique em Next >.
Pronto! Os dados da tabela usuário foram exportados com sucesso, agora vamos conferir o nosso arquivo de saída. ?
Show de bola. mas esse processo foi feito todo manualmente.
Agora vamos para a solução que pode ser automatizada que comentei no início do post.
Primeiramente é importante ponderar que essa procedure utiliza o conceito de BCP para exportar os arquivos, ou seja, o BCP (Bulk Copy) é uma ferramenta de linha de comandos que usa a API do BCP (Programa de cópia em massa).
Para utilizá-la no SQL Server é necessário habilitar o “xp_cmdshell”. (Existem certos riscos relacionados a segurança em deixar habilitado esse assistente de linhas de comando dentro do SQL Server, visto que isso em mãos erradas pode fazer um estrago muito grande no seu sistema operacional. Porém, isso é pauta para outras abordagens).
Para maiores informações acesse o link abaixo :
Para habilitar o “xp_cmdshell” execute os comandos abaixo:
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | -- To allow advanced options to be changed. EXEC sp_configure 'show advanced options', 1; GO -- To update the currently configured value for advanced options. RECONFIGURE; GO -- To enable the feature. EXEC sp_configure 'xp_cmdshell', 1; GO -- To update the currently configured value for this feature. RECONFIGURE; |
Seguindo, com o nosso processo, essa procedure eu encontrei lá no blog do SQL Server Central, link abaixo.
https://www.sqlservercentral.com/scripts/dynamic-sql-for-convert-table-to-flat-file-using-bcp
Faça a criação da procedure [usp_bcpTableUnload] na sua base de dados.
Segue o script:
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 | CREATE PROCEDURE dbo.usp_bcpTableUnload ( @path NVARCHAR(900) , @serverName SYSNAME = @@SERVERNAME , @databaseName SYSNAME , @schemaName SYSNAME , @tableName SYSNAME , @fieldTerminator NVARCHAR(10) = '|' , @fileExtension NVARCHAR(10) = 'txt' , @codePage NVARCHAR(10) = 'C1251' , @excludeColumns NVARCHAR(MAX) = '' , @orderByColumns NVARCHAR(MAX) = '' , @outputColumnHeaders BIT = 1 , @debug BIT = 0 ) AS /* bcp docs: https://msdn.microsoft.com/ru-ru/library/ms162802.aspx -- To allow advanced options to be changed. EXEC sp_configure 'show advanced options', 1; GO -- To update the currently configured value for advanced options. RECONFIGURE; GO -- To enable the feature. EXEC sp_configure 'xp_cmdshell', 1; GO -- To update the currently configured value for this feature. RECONFIGURE; GO EXECUTE [dbo].[usp_bcpTableUnload] @path = 'd:\' , @databaseName = 'DatabaseName' , @schemaName = 'dbo' , @tableName = 'TableName' , @fieldTerminator = '|' , @fileExtension = 'txt' , @excludeColumns = '[CreatedDate],[ModifiedDate],[UserID]' , @orderByColumns = 'TableNameID' , @outputColumnHeaders = 1 , @debug = 0; */ BEGIN BEGIN TRY IF @debug = 0 SET NOCOUNT ON; DECLARE @tsqlCommand NVARCHAR(MAX) = ''; DECLARE @cmdCommand VARCHAR(8000) = ''; DECLARE @ParmDefinition NVARCHAR(500) = '@object_idIN INTEGER, @ColumnsOUT VARCHAR(MAX) OUTPUT'; DECLARE @tableFullName NVARCHAR(500) = QUOTENAME(@databaseName) + '.' + QUOTENAME(@schemaName) + '.' + QUOTENAME(@tableName); DECLARE @object_id INTEGER = OBJECT_ID(@tableFullName); DECLARE @Columns NVARCHAR(MAX) = ''; DECLARE @filePath NVARCHAR(900) = @path + @tableFullName + '.' + @fileExtension; DECLARE @crlf NVARCHAR(10) = CHAR(13); DECLARE @TROW50000 NVARCHAR(MAX) = '' IF @debug = 1 PRINT ISNULL('/******* Start Debug' + @crlf + '@tableFullName = {' + CAST(@tableFullName AS NVARCHAR) + '}', '@tableFullName = {Null}'); IF @debug = 1 PRINT ISNULL('@object_id = {' + CAST(@object_id AS NVARCHAR) + '}', '@object_id = {Null}'); SET @TROW50000 = 'Table ' + @tableFullName + ' is not exists in database ' + QUOTENAME(@databaseName) + '!!!'; IF @OBJECT_ID IS NULL THROW 50000, @TROW50000, 1; SET @tsqlCommand = N'USE ' + @databaseName + ';' + @crlf + N'SELECT @ColumnsOUT = @ColumnsOUT + QUOTENAME(Name) + '',''' + @crlf + N'FROM sys.columns sac ' + @crlf + N'WHERE sac.object_id = @object_idIN' + @crlf + N' AND QUOTENAME(Name) NOT IN (''' + REPLACE(@excludeColumns, ',', ''',''') + ''')' + @crlf + N'ORDER BY Name;'; IF @debug = 1 PRINT ISNULL(N'@tsqlCommand = {' + @crlf + @tsqlCommand + @crlf + N'}', N'@tsqlCommand = {Null}'); EXECUTE sp_executesql @tsqlCommand, @ParmDefinition, @object_idIN = @object_id, @ColumnsOUT = @Columns OUTPUT SELECT @Columns; IF @debug = 1 PRINT ISNULL('@Columns = {' + @crlf + @Columns + @crlf + '}', '@Columns = {Null}'); SET @Columns = CASE WHEN LEN(@Columns) > 0 THEN LEFT(@Columns, LEN(@Columns) - 1) END; IF @debug = 1 PRINT CAST(ISNULL('@Columns = {' + @Columns + '}', '@Columns = {Null}') AS TEXT); SET @tsqlCommand = 'EXECUTE xp_cmdshell ' + '''bcp "SELECT ' + @Columns + ' FROM ' + @tableFullName + ' ORDER BY ' + @orderByColumns + '" queryout "' + @filePath + '" -T -S ' + @serverName +' -c -' + @codePage + ' -t"' + @fieldTerminator + '"''' + @crlf; IF @debug = 1 PRINT CAST(ISNULL('@tsqlCommand = {' + @crlf + @tsqlCommand + @crlf + '}', '@tsqlCommand = {Null}' + @crlf) AS TEXT); ELSE EXECUTE sp_executesql @tsqlCommand; IF @outputColumnHeaders = 1 BEGIN SET @tsqlCommand = 'EXECUTE xp_cmdshell ' + '''bcp "SELECT ''''' + REPLACE(@Columns, ',', @fieldTerminator) + '''''" queryout "' + @path + @tableFullName + '_headers.txt' + '" -T -S ' + @serverName + ' -c -' + @codePage + ' -t"' + @fieldTerminator + '"''' + @crlf; IF @debug = 1 PRINT CAST(ISNULL('@tsqlCommand = {' + @crlf + @tsqlCommand + @crlf + '}', '@tsqlCommand = {Null}' + @crlf) AS TEXT); ELSE EXECUTE sp_executesql @tsqlCommand; SET @cmdCommand = 'copy /b ' + @path + @tableFullName + '_headers.' + @fileExtension + ' + ' + @filePath + ' ' + @path + @tableFullName + '_headers.' + @fileExtension; IF @debug = 1 PRINT CAST(ISNULL('@cmdCommand = {' + @crlf + @cmdCommand + @crlf + '}', '@cmdCommand = {Null}' + @crlf) AS TEXT) ELSE EXECUTE xp_cmdshell @cmdCommand; SET @cmdCommand = 'del ' + @filePath; IF @debug = 1 PRINT CAST(ISNULL('@cmdCommand = {' + @crlf + @cmdCommand + @crlf + '}', '@cmdCommand = {Null}' + @crlf) AS TEXT) ELSE EXECUTE xp_cmdshell @cmdCommand; END IF @debug = 1 PRINT '--End Deubg*********/'; ELSE SET NOCOUNT OFF; END TRY BEGIN CATCH -- EXECUTE dbo.usp_LogError; -- EXECUTE dbo.usp_PrintError; END CATCH END; |
Para exportar os dados através da procedure, preencha os parâmetros de acordo com a sua necessidade, segue um exemplo:
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | EXECUTE [dbo].[usp_bcpTableUnload] @path = 'd:\teste\' , @databaseName = 'teste_blog' , @schemaName = 'dbo' , @tableName = 'USUARIO' , @fieldTerminator = ';' , @fileExtension = 'txt' , @orderByColumns = 'ID' |
Ao consultar o nosso arquivo de saída, teremos as informações exportadas.
Feito!!
Agora para automatizar, basta colocar essa procedure dentro de um Job e agendar de acordo com a sua necessidade.
Tem um artigo muito interessante do mestre Dirceu Resende, demonstrando algumas maneiras também de realizar exportações para texto.
Não deixem de conferir!!
E aí, gostou?
Qualquer dúvida deixe nos comentários.
Abraços.
Gustavo Larocca
Consultor SQL Server
8 comentários
Post muito bem escrito, estruturado de uma forma excelente, com imagens do passo a passo, scripts e links para uma leitura complementar. Parabéns!
Post muito útil! Mais um pra salvar aqui e que deve me ajudar no futuro também =)
Abraço,
Luiz Vitor
Ja me salvou uma vez com este script! Bem personalizavel, top demais 🙂
Esse vai para os favoritos.
Excelente matéria. Com certeza irá ajudar muita gente.
Já está nos favoritos.
Show de bola professor.
Bom ida!
Na etapa “Configure flat file destination” da exportação manual, não aparece nenhuma tabela para selecionar. Você sabe me dizer porque isso ocorre?
As etapas anteriores fiz da mesma forma como descrito.
Opa, Fala Marlon, tudo beleza?
Acredito que seja pq o “Configure Flat File Destination” exija uma fonte de dados que precisa ser um arquivo texto (.Txt) dentro do seu disco rigido e não uma tabela..
Tentou criar um arquivo “.txt” vazio na sua máquina e tentar exportar os dados do seu data source para ele?
Muito bom o material, parabéns Gustavo.
Raro encontrar pessoas com disposição para ensinar e passar conhecimento.
Show!!