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.

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.

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

 

16 thoughts on “Copiar resultado do SSMS no Excel sem quebra

  1. francis

    obrigada, este erro estava perdendo muito tempo para corrigir no excel

    1. Renato Siqueira Post author

      Que bom Francis! A ideia era essa. Também já sofri muito com isso. []’s

    1. Renato Siqueira Post author

      Bom saber. Valeu pelo comentário

  2. Rosângela

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

    Bug miserável!

    Muito obrigada. 🙂

    1. Renato Siqueira Post author

      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

  3. Antonio Carlos

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

  4. Cinthia

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

    1. Renato Siqueira Post author

      Feliz que te ajudou! Disponha

  5. Walisson

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

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

    1. Renato Siqueira Post author

      Opa, feliz em ter contribuído. Abs

  6. Toni Souza

    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.

    1. Renato Siqueira Post author

      Que bom que foi útil pra mais alguém.
      Vlw por comentar Toni.
      []’s

Leave a reply

required