Copiar resultado do SSMS no Excel sem quebra


Já copiou o resultado de determinada consulta no SSMS (SQL Server Management Studio), colou em uma planilha do Excel e se surpreendeu com a bagunça?
O que era pra ser uma solução rápida e prática acabou virando uma zona?

Eis o cenário:

Consulta exemplo
Gerando um resultset pra teste
Pense na cópia mal sucedida
Copiando o resultset no Management Studio e copiando no Excel. Olha a bagunça!

O intuito deste post é explicar o que causa o problema e como resolver.

Problema

O problema acontece quando o resultado copiado possui caracteres como quebra de linha, tab, etc. Estes caracteres são interpretados no Excel, se copiados diretamente, como comandos e acabam por desconfigurar no momento de colar.
Vamos simular o problema:

1) Criando uma massa pra testes. Existe um propósito em manter os textos no INSERT com espaço. Deixe-os desta forma caso use o código abaixo.

USE TEMPDB
GO

CREATE TABLE dbo.Posts
(
	ID INT IDENTITY (1,1) NOT NULL,
	DATA DATE,
	RESUMO VARCHAR(500)
);


INSERT INTO DBO.POSTS (DATA, RESUMO)
VALUES ('2014-01-05','O intuito do post é relacionar:

O reino animal, o perigo da extinção e o mercado de TI.
');

INSERT INTO DBO.POSTS (DATA, RESUMO)
VALUES ('2014-01-12','O intuito do post é relacionar:

SQL Server e seus diferentes tipos de buffer
');

INSERT INTO DBO.POSTS (DATA, RESUMO) 
VALUES ('2014-01-19', 'O intuito do post é simplesmente:

Compartilhar uma novidade de uma feature que existe aqui só na minha máquina

');

2) Consultando a tabela recém-criada, gerando um resultset com caracteres de controle:

Gerando um resultset problemático de novo
Mesmo caso do exemplo que abriu o post.

3)  Copie o resultset (de preferência com o cabeçalho, botão direito no resultado da consulta, opção “Copy with Headers“, e cole no Excel:

Formatação quebrada
Olha a bagunça!

 Solução

Alguns usuários do SQL Server já abriram chamado na Microsoft para relatar o problema, que aparentemente não possui soluções definitivas, mas existe um workaround (aqui no Brasil usa-se o termo solução de contorno, alguns dizem gambiarra, etc).

Sabendo que o problema acontece quando o resultset possui caracteres como quebra de linha, tab, dentre outros (conhecidos como caracteres de controle), que são interpretados como comandos no Excel, podemos converter esses caracteres em espaço, podendo assim sacrificar a formatação original do campo em prol da realização da tarefa.

Na tabela ASCII, vamos identificar quais caracteres devem ser primariamente localizados:

Codes

Abaixo, segue dentro dos parênteses os códigos ASCII do que pretendemos remover:

  • CHAR(9) = Tab
  • CHAR(10) = Line Feed (LF)
  • CHAR(13) = Carriage Return (CR)

Tab é tab é dispensa explicações. Já Carriage Return e Line Feed são dois caracteres de controle que com frequência caminham de mãos dadas quando uma quebra de linha acontece. Em outras palavras, um ENTER, por exemplo, faz a quebra utilizando estes dois caracteres.

Pra facilitar o entendimento:

NaPratica

Podemos usar os códigos destes caracteres de controle dentro da função REPLACE e substituí-los por espaço, de modo que nossa cópia não fique desconfigurada. Note que você pode fazer o mesmo sem passar espaço, pode passar entre aspas vazias também.

SELECT 
id, 
data, 
REPLACE(REPLACE(REPLACE(CAST(Resumo AS CHAR(150)), CHAR(10), ''), CHAR(13), ''),CHAR(9),'')as [Resumo]

FROM dbo.Posts

Agora, vamos colar o resultado sem quebrar o resultset:

Gerando agora um resultset com o workaround
Gerando agora um resultset com o workaround

Copiando e colando no excel:

Agora foi!
Cópia bem sucedida.

O workaround, assim como mais informações a respeito do problema, foram postados nos dois links abaixo no connect:

https://connect.microsoft.com/SQLServer/feedback/details/788463/copy-and-paste-from-sql-2012-to-excel-breaks-rows-if-an-address-is-included

Conclusão

A solução apresentada permite a cópia sem maiores problemas sacrificando a formatação original de textos que possuem caracteres de controle. Caso seja realmente necessário manter a formatação, é possível realizar a importação de dados para o Excel de forma direta utilizando outros métodos, que não estão descritos neste post.

[]’s

Referências

  1. CHAR Function – http://msdn.microsoft.com/pt-br/library/ms187323.aspx
  2. REPLACE Function  – http://msdn.microsoft.com/pt-br/library/ms186862.aspx
  3. Carriage Return (CR)- http://pt.wikipedia.org/wiki/Carriage_return
  4. Line Feed (LF) – http://en.wikipedia.org/wiki/Newline
  5. Tabela ASCII – http://www.theasciicode.com.ar/ascii-control-characters/horizontal-tab-ascii-code-9.html

 


21 responses to “Copiar resultado do SSMS no Excel sem quebra”

  1. CARA, muito bacana esse seu post.
    Fiquei umas horas até encontrar e finalmente conseguir resolver meu problema.

    Bug miserável!

    Muito obrigada. 🙂

    • Que bom que tenha gostado e que principalmente, ajudou a resolver um problema.
      Eu que agradeço o comentário e a sua leitura.
      []’s

  2. Show de bola…me ajudou muito, bem simples e bem explicado, mas nem tinha pensado nessa solução haha…vlw!

  3. Renato, boa tarde!
    Suas dicas me ajudaram a solucionar o meu problema!
    Muito obrigada por disponibilizar este post 🙂

  4. Resolveu meu problema também, passava horas e horas corrigindo planilhas.

    Deus o abençoe, por compartilhar o bem, kkkk

  5. Valeu pela ajuda, salvou um colega de trabalho aqui do meu lado que tava se quebrando a manhã toda pra remover as quebras de linha.

Leave a Reply to Walisson Cancel reply

Your email address will not be published. Required fields are marked *