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

 

Impressões sobre o exame 70-463: Implementing a Data Warehouse with Microsoft SQL Server 2012

Nova seção de scripts!

Boa noite,

Fechando a trilogia MCSA, posto aqui minhas impressões sobre o assunto:

Opinião geral

Consegui aprovação no último exame da trilogia MCSA SQL Server 2012, que é a prova de “BI” , onde seus conhecimentos sobre ETL (principalmente) e Data Quality serão avaliados. A impressão que tive do exame, é que a MS testou um profissional que trabalha ostensivamente com SSIS, usando boa parte dos recursos disponíveis na ferramenta Data Tools. Um pouco de MDS  e DQS também, mas SSIS principalmente.

Sinceramente… Eu já li em vários lugares sobre a decisão da MS de incluir um exame de BI pra MCSA e na teoria é interessante, o profissional fica mais generalista no sentido de lidar com quase toda a plataforma SQL Server principalmente o BI da Microsoft que sofre um pouco de preconceito, mas….na prática, eu atualmente não mexo com SSIS e foi legal aprender uns truques novos (e reforçar uns velhos) mas, na boa, achei bola fora por parte da MS tirar o foco dos tios que trabalham com DEV e ADM, pra estudar como se depura um pacote, loga execuções ou configurar o DQS. O assunto é legal, mas definitivamente não devia ser obrigatório.

Opinião sobre o exame

Foram 79 questões, muitas delas cansativas pois falar de SSIS de modo textual. Teve uma quantidade boa de questões de clicar em interface, de arrastar a task correta pra criar um ETL, tais questões foram bem legais, e acredito que poderiam ter sido aplicado em maior quantidade.

Passei dificuldades por não fazer deploy de pacotes e nem logging ao longo da vida de ETL e sofri bastante com várias destas questões textuais.

Achei a prova difícil, pois o que sei de BI/ETL foi algum tempo trabalhando com o BIDS e um pouco de estudo. Inclusive passei por pouco =p

Pra material de estudo, usei o Training Kit (70-463) cobre muito bem a prova e o Introducing SQL Server 2012, a parte que fala  de DQS e de SSIS é sensacional. Esse livro me ajudou mais que o próprio Training, já que DQS é uma novidade do 2012  e tem um capítulo só pra isso e a parte de deploy de SSIS é bem explicada neste livro. Sem contar que o livro é Free, você pode conferi-lo aqui. Aliás, recomendo esse livro também pra 70-462 (cobre muito bem as novidades).

Sobre o exame

Falar um pouco sobre o que lembro (e posso passar por aqui):

Design and implement a data warehouse

As questões mais legais da prova e a única parte que fui bem, hehe:

A visão que a MS tem sobre BI é importante em várias questões de design;

Columnstore Index: Saber quantos você pode criar por tabela, suas restrições (no 2012!!!) e como criar da melhor forma (ocupando menos espaço…);

– Conceitos de Slow Changing Dimension voltando ao banco (estrutura) com SSIS (Tipos 1, 2 e 3)

– Conceito de FK na tabela Fato é cobrada em algumas questões;

Extract and transform data +  (Load Data)

Tasks que caem na prova (control e data flow): Fuzzy Lookup, Derived Column, Merge, Merge Join, Union All, Sort (atenção), Script Tasks e Expressions Tasks. Dê uma olhada que cai na certa.

– Paralelismo ao rodar pacote de carga se baseando em um modelo Snow Flake (essa questão é bem legal);

– Fluxos de erro e event handler são de importância extrema;

– Task de Container aqui vale ouro. Dê uma olhada no que ela realmente pode fazer em critérios de organização e seu uso + EventHandler;

– Surpresinha: Cai uma questão de MERGE no T-SQL. Cai também de tasks Merge, Merge ALL e Union ALL. Mas eu tô dizendo é sobre o MERGE em T-SQL mesmo.

Configure and deploy SSIS Solutions

A maioria das questões é drag in drop ou de múltipla resposta:

– Estude logging, com carinho. Todas as configurações, todas as formas possíveis de log. Inclusive usando SSIS catalog;

– O conceito de SSIS Catalog, variáveis e parâmetros é fundamental e isso é essencial pra passar na prova;

– Falando em SSIS Catalog, dê uma olhada nas views mais importantes;

– Saiba onde os pacotes .dtsx podem ser implantados (TODOS os meios de armazenamento, desde file system até SSISDB);

Build Data Quality Solutions

Parte surpresa da prova! Caiu muito sobre MDS, inclusive plugins de Excel, assim como caiu algumas questões sobre como configura-se o Data Quality Server e Client (saber os pré-requisitos é fundamental em várias questões). Saber sobre as roles do DQS é importante também.

—-

Conclusão + Offtopic

É isso. Feliz por ter passado no exame porque queria estudar ADM de novo e o ETL não tava deixando. Pensa na felicidade!  Agora, vou estudar o que gosto (e quero) e blogar um pouco mais que o normal (pois, finalmente, não estou focado em um exame em específico). Uma felicidade, também particular, foi de saber que o tempo dedicado aos estudos (e uns finais de semana com ssms) deu certo! Aprendi muita coisa desde que comecei a estudar pra certificações, e, de novo, recomendo que seu principal foco seja para aprendizado.

Next Step: MCSE, os dois exames (Design e Developing). Mas até lá, tenho outros objetivos. Tem uns livros de SQL Server aí no meio, além de muito estudo e laboratório (fora uns projetos meio doidos que eu tô tendo, talvez dê certo, só o futuro dirá). Enfim, me preparar por mais tempo o/