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.

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

 

8 Comments

  1. francis

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

    Reply
    1. Renato Siqueira (Post author)

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

      Reply
  2. Kener

    Renato, valeu por compartilhar, salvou mesmo!

    Reply
    1. Renato Siqueira (Post author)

      Massa!

      Reply
  3. Thiago

    Boa Manolo, isso salvou meu dia

    Reply
    1. Renato Siqueira (Post author)

      Bom saber. Valeu pelo comentário

      Reply
  4. Rosângela

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

    Bug miserável!

    Muito obrigada. 🙂

    Reply
    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

      Reply

Leave a Comment

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