Neste artigo
ToggleOlá pessoal,
Boa noite!
Depois de escrever meu post anterior, onde falei sobre Como importar arquivos de texto para o banco (OLE Automation, CLR, BCP, BULK INSERT, OPENROWSET), acabei me animando pra escrever a parte 2, que é o processo inverso, ou seja, exportar os dados do SQL Server para um arquivo de texto, que é exatamente esse post.
Muitas vezes precisamos exportar dados de forma fácil para um arquivo de texto e já vi vários desenvolvedores ou DBA’s desesperados porque não sabem como fazer isso de forma eficiente.
Vamos resolver isso de uma vez por todas.
Exportando pelo SQL Server Management StudioComo exportar dados pelo SQL Server Management Studio
Sem dúvida, a forma mais simples e fácil de exportar dados para arquivo no SQL Server é utilizando a própria interface da ferramenta, que é o Management Studio.
Na tela de Object Explorer, clique com o botão direito sobre o database que contém as tabelas que você deseja exportar e selecione a opção “Tasks” -> “Export Data…”
Nesta tela, você deve escolher a fonte de origem dos dados, configurar os dados de conexão e o database que você irá exportar
Nesta tela, você deve escolher a fonte de destino dos dados. Para arquivo, escolhi “Flat File Destination”. Podemos definir onde será gravado o arquivo com os dados, codificação dos caracteres (ISO-8859, UTF-8, etc), o formato dos dados no arquivo (delimitado por algum caractere, tamanho fixo ou alinhado à direita), o qualificador do texto (ex: se você definir o aspas como qualificador, a sua senha será algo assim: “Dirceu”;”29″;”DBA”) e definir se a primeira linha conterá o cabeçalho da tabela.
Nesta tela, você pode definir se você quer selecionar os objetos que deseja exportar (primeira opção) ou quer escrever uma query e o resultado da query será exportado para o arquivo. Para este exemplo, vou escolher a primeira opção.
Aqui você pode escolher o objeto que será exportado e definir o formato de quebra de linha (Windows = CRLF, UNIX = LF) e o caractere delimitador de colunas
Por fim, nesta tela você define se já quer exportar os dados (Run immediately) e se você desejar gerar um package do Integration Services (SSIS) caso queira automatizar essa tarefa como um Job pelo SQL Agent ou executá-la manualmente sem precisar configurar tudo de novo.
Embora isso seja simples, para exportar várias tabelas isso acaba sendo trabalhoso e nada prático. Por este motivo, eu vou explicar mais abaixo como fazer isso via T-SQL.
Como exportar dados do SQL Server para arquivo txt com BCP
Essa é uma das opções mais utilizadas pelos DBA’s, pois é simples, já vem instalada com o SQL Server e pode ser executada tanto em packages SSIS quanto em stored procedures (utilizando xp_cmdshell)
Lembrando que para utilizar o BCP, você precisará ativar o recurso xp_cmdshell. Eu particularmente não gosto de utilizar o xp_cmdshell e nem de deixar habilitado em uma instância, pois permite inúmeras vulnerabilidades e qualquer comando do Prompt do Windows pode ser executado com esse recurso habilitado.
Para ativar a feature e permitir o xp_cmdshell, execute os seguintes comandos:
1 2 3 4 5 6 7 8 |
sp_configure 'show advanced options', 1; GO RECONFIGURE; GO sp_configure 'xp_cmdshell', 1; GO RECONFIGURE; GO |
Exemplo de utilização:
1 2 3 4 5 |
-- Utilizando queryout, pode-se exportar o resultado de uma query EXEC master.dbo.xp_cmdshell 'bcp "SELECT * FROM msdb.sys.tables" queryout "C:\Temp\bcp_queryout.csv" -c -t; -T -Slocalhost\SQL2014' -- Utilizando out, pode-se exportar um objeto EXEC master.dbo.xp_cmdshell 'bcp msdb.sys.tables out "C:\Temp\bcp_out.csv" -c -t, -T -Slocalhost\SQL2014' |
Onde:
– out e queryout permitem definir a forma de exportação dos dados. OUT exporta um objeto e o QUERYOUT o resultado de uma query.
– -c define que todos os campos serão exportados como caracter (string)
– -t; permite definir o separador dos campos, não limitando-se a apenas 1 caractere como separador. No primeiro exemplo, estou utilizando o “;” como separador de coluna.
– -T serve para informar que a conexão será realizada no modo Trusted Connection (Autenticação Windows). Caso você queira utilizar autenticação SQL Server, basta utilizar -Uusuario e -Psenha.
– -S serve para informar o servidor\instância que você deseja se conectar.
Como exportar arquivos de texto para o banco com OLE Automation
Para quem não conhece esse recurso, ele permite que o DBA ou Desenvolvedor execute uma série de ações no banco de dados usando OLE DB, como leitura/escrita/movimentação/cópia/deleção de arquivos, criação de planilhas no Excel e mais uma série de coisas. A sintaxe é um pouco parecida com o VBA e utiliza a API do Windows para essas operações.
Para essa finalidade, vamos utilizar as Stored Procedures stpEscreve_Arquivo_FSO e SaveDelimitedColumns, conforme demonstrado abaixo:
Código-fonte da Procedure stpEscreve_Arquivo_FSO
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 |
CREATE PROCEDURE [dbo].[stpEscreve_Arquivo_FSO] ( @String VARCHAR(MAX), @Ds_Arquivo VARCHAR(1501) ) AS BEGIN DECLARE @objFileSystem INT, @objTextStream INT, @objErrorObject INT, @strErrorMessage VARCHAR(1000), @Command VARCHAR(1000), @hr INT SET NOCOUNT ON SELECT @strErrorMessage = 'opening the File System Object' EXECUTE @hr = sp_OACreate 'Scripting.FileSystemObject', @objFileSystem OUT IF @hr = 0 SELECT @objErrorObject = @objFileSystem, @strErrorMessage = 'Creating file "' + @Ds_Arquivo + '"' IF @hr = 0 EXECUTE @hr = sp_OAMethod @objFileSystem, 'CreateTextFile', @objTextStream OUT, @Ds_Arquivo, 2, True IF @hr = 0 SELECT @objErrorObject = @objTextStream, @strErrorMessage = 'writing to the file "' + @Ds_Arquivo + '"' IF @hr = 0 EXECUTE @hr = sp_OAMethod @objTextStream, 'Write', NULL, @String IF @hr = 0 SELECT @objErrorObject = @objTextStream, @strErrorMessage = 'closing the file "' + @Ds_Arquivo + '"' IF @hr = 0 EXECUTE @hr = sp_OAMethod @objTextStream, 'Close' IF @hr <> 0 BEGIN DECLARE @Source VARCHAR(255), @Description VARCHAR(255), @Helpfile VARCHAR(255), @HelpID INT EXECUTE sp_OAGetErrorInfo @objErrorObject, @Source OUTPUT, @Description OUTPUT, @Helpfile OUTPUT, @HelpID OUTPUT SELECT @strErrorMessage = 'Error whilst ' + COALESCE(@strErrorMessage, 'doing something') + ', ' + COALESCE(@Description, '') RAISERROR (@strErrorMessage,16,1) END EXECUTE sp_OADestroy @objTextStream EXECUTE sp_OADestroy @objTextStream END |
Código-fonte da Procedure SaveDelimitedColumns
Para visualizar o código-fonte dessa procedure, criada pelo John Buoro, acesse este link ou abaixo:
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 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 |
CREATE PROCEDURE [dbo].[SaveDelimitedColumns] @PCWrite VARCHAR(1000) = NULL, @DBFetch VARCHAR(4000), @DBWhere VARCHAR(2000) = NULL, @DBThere VARCHAR(2000) = NULL, @DBUltra BIT = 1, @Delimiter VARCHAR(100) = 'CHAR(44)', -- Default is , @TextQuote VARCHAR(100) = 'CHAR(34)', -- Default is " Use SPACE(0) for none. @Header BIT = 0, -- Output header. Default is 0. @NullQuoted BIT = 0, @DateTimeStyle TINYINT = 120 -- CONVERT Date Time Style. Default is ODBC canonical yyyy-mm-dd hh:mi:ss(24h) AS BEGIN SET NOCOUNT ON; DECLARE @Return INT; DECLARE @Retain INT; DECLARE @Status INT; SET @Status = 0; DECLARE @TPre VARCHAR(10); DECLARE @TDo3 TINYINT; DECLARE @TDo4 TINYINT; SET @TPre = ''; SET @TDo3 = LEN(@TPre); SET @TDo4 = LEN(@TPre) + 1; DECLARE @DBAE VARCHAR(40); DECLARE @Task VARCHAR(6000); DECLARE @Bank VARCHAR(4000); DECLARE @Cash VARCHAR(2000); DECLARE @Risk VARCHAR(2000); DECLARE @Next VARCHAR(8000); DECLARE @Save VARCHAR(8000); DECLARE @Work VARCHAR(8000); DECLARE @Wish VARCHAR(MAX); DECLARE @Name VARCHAR(100); DECLARE @Same VARCHAR(100); DECLARE @Rank SMALLINT; DECLARE @Kind VARCHAR(20); DECLARE @Mask BIT; DECLARE @Bond BIT; DECLARE @Size INT; DECLARE @Wide SMALLINT; DECLARE @More SMALLINT; DECLARE @DBAI VARCHAR(2000); DECLARE @DBAO VARCHAR(8000); DECLARE @DBAU VARCHAR(MAX); DECLARE @Fuse INT; DECLARE @File INT; DECLARE @HeaderString VARCHAR(8000); DECLARE @HeaderDone INT; SET @DBAE = '##SaveFile' + RIGHT(CONVERT(VARCHAR(10), @@SPID + 100000), 5); SET @Task = 'IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name = ' + CHAR(39) + @DBAE + CHAR(39) + ') DROP TABLE ' + @DBAE; EXECUTE ( @Task ); SET @Bank = @TPre + @DBFetch; IF NOT EXISTS ( SELECT * FROM sysobjects WHERE RTRIM(type) = 'U' AND name = @Bank ) BEGIN SET @Bank = CASE WHEN LEFT(LTRIM(@DBFetch), 6) = 'SELECT' THEN '(' + @DBFetch + ')' ELSE @DBFetch END; SET @Bank = REPLACE(@Bank, CHAR(94), CHAR(39)); SET @Bank = REPLACE(@Bank, CHAR(45) + CHAR(45), CHAR(32)); SET @Bank = REPLACE(@Bank, CHAR(47) + CHAR(42), CHAR(32)); END; IF @DBWhere IS NOT NULL BEGIN SET @Cash = REPLACE(@DBWhere, 'WHERE', CHAR(32)); SET @Cash = REPLACE(@Cash, CHAR(94), CHAR(39)); SET @Cash = REPLACE(@Cash, CHAR(45) + CHAR(45), CHAR(32)); SET @Cash = REPLACE(@Cash, CHAR(47) + CHAR(42), CHAR(32)); END; IF @DBThere IS NOT NULL BEGIN SET @Risk = REPLACE(@DBThere, 'ORDER BY', CHAR(32)); SET @Risk = REPLACE(@Risk, CHAR(94), CHAR(39)); SET @Risk = REPLACE(@Risk, CHAR(45) + CHAR(45), CHAR(32)); SET @Risk = REPLACE(@Risk, CHAR(47) + CHAR(42), CHAR(32)); END; SET @DBAI = ''; SET @DBAO = ''; SET @DBAU = ''; SET @Task = 'SELECT * INTO ' + @DBAE + ' FROM (' + @Bank + ') AS T WHERE 0 = 1'; IF @Status = 0 EXECUTE ( @Task ); SET @Return = @@ERROR; IF @Status = 0 SET @Status = @Return; -- For all columns (Fields) in the table. DECLARE Fields CURSOR FAST_FORWARD FOR SELECT '[' + C.name + ']', C.colid, T.name, C.isnullable, C.iscomputed, C.length, C.prec, C.scale FROM tempdb.dbo.sysobjects AS O JOIN tempdb.dbo.syscolumns AS C ON O.id = C.id JOIN tempdb.dbo.systypes AS T ON C.xusertype = T.xusertype WHERE O.name = @DBAE ORDER BY C.colid; SET @Retain = @@ERROR; IF @Status = 0 SET @Status = @Retain; OPEN Fields; SET @Retain = @@ERROR; IF @Status = 0 SET @Status = @Retain; FETCH NEXT FROM Fields INTO @Same, @Rank, @Kind, @Mask, @Bond, @Size, @Wide, @More; SET @Retain = @@ERROR; IF @Status = 0 SET @Status = @Retain; -- Convert to character for header. SET @HeaderString = ''; DECLARE @sql NVARCHAR(4000); DECLARE @cDelimiter NVARCHAR(9); DECLARE @cTextQuote NVARCHAR(9); DECLARE @TypeFound BIT; SET @sql = N'select @cDelimiter = ' + @Delimiter; EXEC sp_executesql @sql, N'@cDelimiter varchar(9) output', @cDelimiter OUTPUT; SET @sql = N'select @cTextQuote = ' + @TextQuote; EXEC sp_executesql @sql, N'@cTextQuote varchar(9) output', @cTextQuote OUTPUT; WHILE @@FETCH_STATUS = 0 AND @Status = 0 BEGIN SET @TypeFound = 0; -- Build header. IF LEN(@HeaderString) > 0 SET @HeaderString = @HeaderString + @cDelimiter + ISNULL(@cTextQuote + REPLACE(@Same, @cTextQuote, REPLICATE(@cTextQuote, 2)) + @cTextQuote, SPACE(0)); IF LEN(@HeaderString) = 0 SET @HeaderString = ISNULL(@cTextQuote + REPLACE(@Same, @cTextQuote, REPLICATE(@cTextQuote, 2)) + @cTextQuote, SPACE(0)); IF @Kind IN ( 'char', 'nchar', 'varchar', 'nvarchar', 'text', 'ntext', 'sysname', 'xml' ) BEGIN IF @NullQuoted = 0 BEGIN IF @Rank = 1 SET @DBAU = ' ISNULL(' + @TextQuote + '+REPLACE(' + @Same + ' COLLATE SQL_Latin1_General_CP1_CI_AI,' + @TextQuote + ',REPLICATE(' + @TextQuote + ',2))+' + @TextQuote + ',SPACE(0))'; IF @Rank > 1 SET @DBAU = @DBAU + '+' + @Delimiter + '+ISNULL(' + @TextQuote + '+REPLACE(' + @Same + ' COLLATE SQL_Latin1_General_CP1_CI_AI,' + @TextQuote + ',REPLICATE(' + @TextQuote + ',2))+' + @TextQuote + ',SPACE(0))'; END; IF @NullQuoted = 1 BEGIN IF @Rank = 1 SET @DBAU = ' ISNULL(' + @TextQuote + '+REPLACE(' + @Same + ' COLLATE SQL_Latin1_General_CP1_CI_AI,' + @TextQuote + ',REPLICATE(' + @TextQuote + ',2))+' + @TextQuote + ',' + @TextQuote + '+' + @TextQuote + ')'; IF @Rank > 1 SET @DBAU = @DBAU + '+' + @Delimiter + '+ISNULL(' + @TextQuote + '+REPLACE(' + @Same + ' COLLATE SQL_Latin1_General_CP1_CI_AI,' + @TextQuote + ',REPLICATE(' + @TextQuote + ',2))+' + @TextQuote + ',' + @TextQuote + '+' + @TextQuote + ')'; END; SET @TypeFound = 1; END; IF @Kind IN ( 'bit', 'tinyint', 'smallint', 'int', 'bigint' ) BEGIN IF @NullQuoted = 0 BEGIN IF @Rank = 1 SET @DBAU = ' ISNULL(CONVERT(varchar(128),' + @Same + '),SPACE(0))'; IF @Rank > 1 SET @DBAU = @DBAU + '+' + @Delimiter + '+ISNULL(CONVERT(varchar(128),' + @Same + '),SPACE(0))'; END; IF @NullQuoted = 1 BEGIN IF @Rank = 1 SET @DBAU = ' ISNULL(CONVERT(varchar(128),' + @Same + '),' + @TextQuote + '+' + @TextQuote + ')'; IF @Rank > 1 SET @DBAU = @DBAU + '+' + @Delimiter + '+ISNULL(CONVERT(varchar(128),' + @Same + '),' + @TextQuote + '+' + @TextQuote + ')'; END; SET @TypeFound = 1; END; IF @Kind IN ( 'numeric', 'decimal', 'money', 'smallmoney', 'float', 'real' ) BEGIN IF @NullQuoted = 0 BEGIN IF @Rank = 1 SET @DBAU = ' ISNULL(CONVERT(varchar(128),' + @Same + '),SPACE(0))'; IF @Rank > 1 SET @DBAU = @DBAU + '+' + @Delimiter + '+ISNULL(CONVERT(varchar(128),' + @Same + '),SPACE(0))'; END; IF @NullQuoted = 1 BEGIN IF @Rank = 1 SET @DBAU = ' ISNULL(CONVERT(varchar(128),' + @Same + '),' + @TextQuote + '+' + @TextQuote + ')'; IF @Rank > 1 SET @DBAU = @DBAU + '+' + @Delimiter + '+ISNULL(CONVERT(varchar(128),' + @Same + '),' + @TextQuote + '+' + @TextQuote + ')'; END; SET @TypeFound = 1; END; IF @Kind IN ( 'uniqueidentifier', 'geometry', 'geography' ) BEGIN IF @NullQuoted = 0 BEGIN IF @Rank = 1 SET @DBAU = ' ISNULL(' + @TextQuote + '+CONVERT(varchar(128),' + @Same + ')+' + @TextQuote + ',SPACE(0))'; IF @Rank > 1 SET @DBAU = @DBAU + '+' + @Delimiter + '+ISNULL(' + @TextQuote + '+CONVERT(varchar(128),' + @Same + ')+' + @TextQuote + ',SPACE(0))'; END; IF @NullQuoted = 1 BEGIN IF @Rank = 1 SET @DBAU = ' ISNULL(' + @TextQuote + '+CONVERT(varchar(128),' + @Same + ')+' + @TextQuote + ',' + @TextQuote + '+' + @TextQuote + ')'; IF @Rank > 1 SET @DBAU = @DBAU + '+' + @Delimiter + '+ISNULL(' + @TextQuote + '+CONVERT(varchar(128),' + @Same + ')+' + @TextQuote + ',' + @TextQuote + '+' + @TextQuote + ')'; END; SET @TypeFound = 1; END; IF @Kind IN ( 'datetime2', 'datetime', 'smalldatetime', 'time', 'date', 'datetimeoffset' ) BEGIN IF @NullQuoted = 0 BEGIN IF @Rank = 1 SET @DBAU = ' ISNULL(' + @TextQuote + '+CONVERT(varchar(128),' + @Same + ',' + CONVERT(VARCHAR(3), @DateTimeStyle) + ')+' + @TextQuote + ',SPACE(0))'; IF @Rank > 1 SET @DBAU = @DBAU + '+' + @Delimiter + '+ISNULL(' + @TextQuote + '+CONVERT(varchar(128),' + @Same + ',' + CONVERT(VARCHAR(3), @DateTimeStyle) + ')+' + @TextQuote + ',SPACE(0))'; END; IF @NullQuoted = 1 BEGIN IF @Rank = 1 SET @DBAU = ' ISNULL(' + @TextQuote + '+CONVERT(varchar(128),' + @Same + ',' + CONVERT(VARCHAR(3), @DateTimeStyle) + ')+' + @TextQuote + ',' + @TextQuote + '+' + @TextQuote + ')'; IF @Rank > 1 SET @DBAU = @DBAU + '+' + @Delimiter + '+ISNULL(' + @TextQuote + '+CONVERT(varchar(128),' + @Same + ',' + CONVERT(VARCHAR(3), @DateTimeStyle) + ')+' + @TextQuote + ',' + @TextQuote + '+' + @TextQuote + ')'; END; SET @TypeFound = 1; END; IF @TypeFound = 0 BEGIN SET @Retain = 'ERROR: Data type ' + UPPER(@Kind) + ' was used but not supported by SaveDelimitedColumns.'; SET @Status = @Retain; END; FETCH NEXT FROM Fields INTO @Same, @Rank, @Kind, @Mask, @Bond, @Size, @Wide, @More; SET @Retain = @@ERROR; IF @Status = 0 SET @Status = @Retain; END; CLOSE Fields; DEALLOCATE Fields; IF LEN(@DBAU) = 0 SET @DBAU = '*'; IF @PCWrite IS NOT NULL AND ( @DBUltra = 0 ) AND ( @Header = 1 ) BEGIN SET @HeaderString = REPLACE(@HeaderString, '"', '""'); SET @DBAI = ' SELECT ' + CHAR(39) + @HeaderString + CHAR(39) + ' UNION ALL SELECT '; END; ELSE SET @DBAI = ' SELECT '; SET @DBAO = ' FROM (' + @Bank + ') AS T' + CASE WHEN @DBWhere IS NULL THEN '' ELSE ' WHERE (' + @Cash + ') AND 0 = 0' END + CASE WHEN @DBThere IS NULL THEN '' ELSE ' ORDER BY ' + @Risk END; -- Output where @DBUltra = 0 (Uses XP_CMDSHELL \ BCP) IF @PCWrite IS NOT NULL AND @DBUltra = 0 BEGIN SET @Wish = 'USE ' + DB_NAME() + @DBAI + @DBAU + @DBAO; SET @Work = 'BCP "' + @Wish + '" QUERYOUT "' + @PCWrite + '" -w -T -S "' + @@SERVERNAME + '" '; -- PRINT @Work EXECUTE @Return = master.dbo.xp_cmdshell @Work, NO_OUTPUT; SET @Retain = @@ERROR; IF @Status = 0 SET @Status = @Retain; IF @Status = 0 SET @Status = @Return; IF @Status <> 0 GOTO ABORT; END; -- Output where @DBUltra = 1 (Uses Ole Automation) IF @PCWrite IS NOT NULL AND @DBUltra = 1 BEGIN IF @Status = 0 EXECUTE @Return = sp_OACreate 'Scripting.FileSystemObject', @Fuse OUTPUT; SET @Retain = @@ERROR; IF @Status = 0 SET @Status = @Retain; IF @Status = 0 SET @Status = @Return; IF @Status = 0 EXECUTE @Return = sp_OAMethod @Fuse, 'CreateTextFile', @File OUTPUT, @PCWrite, -1; SET @Retain = @@ERROR; IF @Status = 0 SET @Status = @Retain; IF @Status = 0 SET @Status = @Return; IF @Status <> 0 GOTO ABORT; END; SET @DBAI = 'DECLARE Records CURSOR GLOBAL FAST_FORWARD FOR' + @DBAI; IF @Status = 0 EXECUTE ( @DBAI + @DBAU + @DBAO ); SET @Return = @@ERROR; IF @Status = 0 SET @Status = @Return; OPEN Records; SET @Retain = @@ERROR; IF @Status = 0 SET @Status = @Retain; FETCH NEXT FROM Records INTO @Next; SET @Retain = @@ERROR; IF @Status = 0 SET @Status = @Retain; -- Header. SET @HeaderDone = 0; WHILE @@FETCH_STATUS = 0 AND @Status = 0 BEGIN IF @PCWrite IS NOT NULL AND @DBUltra = 1 BEGIN -- Write header (FILE). IF ( @Header = 1 ) AND ( @HeaderDone = 0 ) BEGIN SET @Save = @HeaderString + CHAR(13) + CHAR(10); IF @Status = 0 EXECUTE @Return = sp_OAMethod @File, 'Write', NULL, @Save; SET @HeaderDone = 1; END; -- Write the data (FILE). SET @Save = @Next + CHAR(13) + CHAR(10); IF @Status = 0 EXECUTE @Return = sp_OAMethod @File, 'Write', NULL, @Save; IF @Status = 0 SET @Status = @Return; END; IF @PCWrite IS NULL BEGIN -- Print header (TEXT). IF ( @Header = 1 ) AND ( @HeaderDone = 0 ) BEGIN PRINT @HeaderString + CHAR(13) + CHAR(10); SET @HeaderDone = 1; END; PRINT @Next; END; FETCH NEXT FROM Records INTO @Next; SET @Retain = @@ERROR; IF @Status = 0 SET @Status = @Retain; END; CLOSE Records; DEALLOCATE Records; -- Close output file (Ole Automation) IF @PCWrite IS NOT NULL AND @DBUltra = 1 BEGIN EXECUTE @Return = sp_OAMethod @File, 'Close', NULL; IF @Status = 0 SET @Status = @Return; EXECUTE @Return = sp_OADestroy @File; IF @Status = 0 SET @Status = @Return; EXECUTE @Return = sp_OADestroy @Fuse; IF @Status = 0 SET @Status = @Return; END; ABORT: -- This label is referenced when OLE automation fails. IF @Status = 1 OR @Status NOT BETWEEN 0 AND 50000 RAISERROR('SaveDelimitedColumns Windows Error [%d]', 16, 1, @Status); SET @Task = 'IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name = ' + CHAR(39) + @DBAE + CHAR(39) + ') DROP TABLE ' + @DBAE; EXECUTE ( @Task ); RETURN ( @Status ); END; GO |
Exemplo de utilização:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
-- Exportando uma string para arquivo com OLE Automation DECLARE @Texto VARCHAR(MAX) = 'Teste de arquivo com quebra de linhas ' EXEC dbo.stpEscreve_Arquivo_FSO @String = @Texto, -- varchar(max) @Ds_Arquivo = 'C:\Temp\Teste.txt' -- varchar(1501) -- Exportando para CSV com OLE Automation EXEC dbo.SaveDelimitedColumns @DBFetch='select * from Testes.dbo.Teste', @DBWhere='Tipo = ^Teste 2^', @PCWrite='C:\Temp\Teste.csv', @Header = 1 |
Como exportar arquivos de texto para o banco com CLR
O CLR permite que você consiga criar rotinas (stored prodecures, functions, triggers, etc) escritas em C#, F# e VB.NET, compilá-las e executá-las no banco de dados nativamente, estendendo as capacidades do SGBD, pois é possível criar uma infinidade de coisas que não seriam possíveis utilizando apenas o Transact-SQL, como manipulação de arquivos, upload e download de arquivos via FTP, funções de agregação, integração com Webservices e muito mais.
Para essa finalidade, vamos utilizar Stored Procedures stpExporta_Query_Txt e stpEscreve_Arquivo, conforme demonstrado abaixo:
Código-fonte da procedure stpExporta_Query_Txt
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 |
using System; using System.Data; using System.Data.SqlClient; using System.IO; using System.Globalization; using System.Text; public partial class StoredProcedures { [Microsoft.SqlServer.Server.SqlProcedure] public static void stpExporta_Query_Txt(string query, string separador, string caminho, int Fl_Coluna) { var fileStream = new FileStream(caminho, FileMode.Create); var sw = new StreamWriter(fileStream, Encoding.Default); try { using (var conn = new SqlConnection("context connection=true")) { var getOutput = new SqlCommand { CommandText = query, CommandType = CommandType.Text, CommandTimeout = 120, Connection = conn }; conn.Open(); var exportData = getOutput.ExecuteReader(); if (Fl_Coluna == 1) { for (var i = 0; i < exportData.FieldCount; i++) { sw.Write(exportData.GetName(i)); if (i < exportData.FieldCount - 1) sw.Write(separador); } sw.WriteLine(); } if (string.IsNullOrEmpty(separador)) { while (exportData.Read()) { for (var i = 0; i < exportData.FieldCount; i++) { sw.Write(Convert.ToString(exportData.GetValue(i), CultureInfo.GetCultureInfo("pt-BR"))); if (i < exportData.FieldCount - 1) sw.Write(separador); } sw.WriteLine(); } } else { var separadorTroca = new string(' ', separador.Length); while (exportData.Read()) { for (var i = 0; i < exportData.FieldCount; i++) { sw.Write(Convert.ToString(exportData.GetValue(i), CultureInfo.GetCultureInfo("pt-BR")).Replace(separador, separadorTroca)); if (i < exportData.FieldCount - 1) sw.Write(separador); } sw.WriteLine(); } } conn.Close(); sw.Close(); conn.Dispose(); getOutput.Dispose(); } } catch (Exception e) { sw.Close(); throw new ApplicationException("Erro : " + e.Message); } } }; |
Código-fonte da procedure stpEscreve_Arquivo
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 |
using System; using System.Data.SqlTypes; using System.IO; using System.Text; public partial class StoredProcedures { [Microsoft.SqlServer.Server.SqlProcedure] public static void stpEscreve_Arquivo(SqlString Ds_Texto, SqlString Ds_Caminho, SqlString Ds_Codificacao, SqlString Ds_Formato_Quebra_Linha, SqlBoolean Fl_Append) { if (!Ds_Texto.IsNull && !Ds_Caminho.IsNull && !Fl_Append.IsNull) { try { var dir = Path.GetDirectoryName(Ds_Caminho.Value); if (!Directory.Exists(dir)) Directory.CreateDirectory(dir); } catch (Exception e) { throw new ApplicationException("Erro : " + e.Message); } var encoding = (Ds_Codificacao.IsNull) ? "UTF-8" : Ds_Codificacao.Value; if (Ds_Codificacao.Value.Trim() == "") encoding = "UTF-8"; var sb = new StringBuilder(Ds_Texto.Value); var fileStream = new FileStream(Ds_Caminho.Value, ((Fl_Append) ? FileMode.Append : FileMode.Create)); var sw = new StreamWriter(fileStream, Encoding.GetEncoding(encoding)); switch (Ds_Formato_Quebra_Linha.Value.ToLower()) { case "unix": sw.NewLine = "\n"; sb.Replace("\r", ""); break; case "mac": sw.NewLine = "\r"; sb.Replace("\n", ""); break; default: sw.NewLine = "\r\n"; break; } try { var texto = sb.ToString(); sw.Write(texto); sw.Close(); } catch (Exception e) { sw.Close(); throw new ApplicationException("Erro : " + e.Message); } } else throw new ApplicationException("Os parâmetros de input estão vazios"); } }; |
Exemplo de utilização:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
-- Exportando uma string para arquivo texto DECLARE @Texto VARCHAR(MAX) = 'Testando Arquivo texto com quebra de linhas' EXEC CLR.dbo.stpEscreve_Arquivo @Ds_Texto = @Texto, -- nvarchar(max) @Ds_Caminho = N'C:\Temp\CLR_Texto.txt', -- nvarchar(max) @Ds_Codificacao = N'ISO-8859-1', -- nvarchar(max) @Ds_Formato_Quebra_Linha = N'windows', -- nvarchar(max) @Fl_Append = 0 -- bit -- Exportando query para arquivo texto (CSV) EXEC CLR.dbo.stpExporta_Query_Txt @query = N'SELECT * FROM sys.tables', -- nvarchar(max) @separador = N';', -- nvarchar(max) @caminho = N'C:\Temp\CLR_Teste.csv', -- nvarchar(max) @Fl_Coluna = 1 -- int |
É isso aí, pessoal!
Até o próximo post.
sql server exportar dados arquivo texto txt export data text files from database do banco de dados
sql server exportar dados arquivo texto txt export data text files from database do banco de dados
Bom dia Amigos.
Vejam se pode me ajudar. Consegui utilizar o comando bcp para converter em txt. Porem quando abro o txt so vem um registro. E se eu executar somente a procedure que criei traz mais de 200 registros. Alguem ja teve esse problema ou tem alguma dica?
Tem jeito de exportar todas as tabelas do meu banco de uma só vez? No meu banco tem mais de 40 tabelas, quero exportar todas para arquivos txt (cada tabela e um arquivo). Não é viável fazer esse processo 40x
Boa tarde.
Eu tenho uma tabela no SQL que contém dados cadastrais de funcionários.
No SQL cada linha representa um funcionário.
O que eu estou tentando fazer é exportar essa tabela para um arquivo texto, visto que o objetivo é importar em um sistema.
Quando eu faço a exportação (LARGURA FIXA), o SQL não está fazendo a quebra de linha, ou seja, as informações de um funcionário estão vindo na frente do outro, sendo que o correto era cada funcionário iniciar em uma nova linha.
Como posso proceder?
DIRCEU RESENDE
Outra coisa o comando que estou utilizando é o seguinte:
exec Pegasus.sys.xp_cmdshell ‘bcp “select * from Occurrences”” queryout “C:\Users\G\Documents\banco\Planilhas\aaaa.txt” -S localhost -n -T -t “;”‘
Se eu faço um select direto por exemplo select 123, o arquivo é gerado.
Gomes,
Experimenta colocar o caminho completo do objeto no comando BCP.
Ex: SELECT * FROM database.dbo.Occurrences
Fala galera, blz?
Tenho tido o seguinte erro ao executar o BCP
output
SQLState = S0002, NativeError = 208
Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid object name ‘Occurrences’.
SQLState = 37000, NativeError = 8180
Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Statement(s) could not be prepared.
NULL
Gomes,
Bom dia.
Pela mensagem de erro, a tabela não existe.
DIRCERU RESENDE
Quando executo o seguinte commando: SELECT * FROM Occurrences
A tabela aparece normalmente.