Há várias maneiras de Exportar/Importar dados do SQL Server para um ficheiro Excel. Neste artigo, serão explicadas algumas formas de importar e exportar dados do SQL Server para um ficheiro Excel:
- Exportar dados do SQL Server para um ficheiro Excel usando
- o Assistente de Importação e Exportação do SQL Server
- T-Código SQL
- Exportar para Excel a partir do ApexSQL Completo
- Importar dados do SQL Server para Excel usando o Assistente de Ligação de Dados
Exportar dados do SQL Server para um ficheiro Excel usando o Assistente de Importação e Exportação do SQL Server
No SQL Server Management Studio (SSMS), há uma característica que prevê a exportação de dados de uma fonte de dados para outra fonte de dados; neste caso, os dados do SQL Server serão copiados para um ficheiro Excel através do Export Wizard.
Para começar a utilizar esta funcionalidade, vá ao Object Explorer, clique com o botão direito do rato em qualquer base de dados (por exemplo AdventureworksDW2016CTP3), sob as Tarefas, escolha Export Data command:
Isto abrirá a janela do Assistente de Importação e Exportação do SQL Server:
Para prosseguir com a exportação de dados do SQL Server para um ficheiro Excel, clique no botão Next. Isto abrirá a janela Escolher uma Fonte de Dados. Nesta janela, uma fonte da qual se deseja copiar dados pode ser escolhida:
A partir da caixa pendente Data source, escolher o item SQL Server Native Client 11.0. Sob a caixa pendente Nome do servidor, escolher a instância do SQL Server da qual os dados precisam de ser copiados. Na secção Autenticação, escolher o modo de autenticação para a ligação da fonte de dados e na caixa pendente Base de dados, escolher a base de dados a partir da qual os dados serão copiados. Depois de todas estas opções estarem definidas, clique no botão Next para prosseguir.
Isto abrirá a janela Choose a Destination, na qual pode ser especificado onde copiar dados da fonte de dados:
Na caixa drop down Destination, escolha o item Microsoft Excel. Sob o caminho do ficheiro Excel, escolher o destino dos dados copiados da fonte de dados do SQL Server (SQL Data.xlsx). Na caixa pendente da versão do Excel, escolher a versão da folha de trabalho do Microsoft Excel. Depois de tudo isto estar definido, prima o botão Next para continuar. Mas após premir o botão Next, pode aparecer a seguinte mensagem de aviso:
A operação não pôde ser concluída.
INFORMAÇÃO ADICIONAL:
O fornecedor ‘Microsoft.ACE.OLEDB.12.0’ não está registado na máquina local. (System.Data)
Isto acontece porque o SSMS é uma aplicação de 32-bit e quando se lança o Export Wizard via SSMS, lança uma versão de 32-bit do Export Wizard. Por outro lado, a máquina (sistema operativo) em que o SSMS está instalado é uma versão de 64 bits e o SQL Server instalado é uma versão de 64 bits.
Para resolver este problema, feche o SSMS, vá ao menu Iniciar e encontre a versão de 64-bit do SQL Server Import and Export Data:
Depois de lançar este Assistente de Importação e Exportação do SQL Server, ele será solicitado com a mesma janela que é utilizada via SSMS. Após definir todas as opções anteriores, clique no botão Next (Seguinte). A janela Especificar cópia de tabela ou consulta aparecerá:
Nesta janela, se copiar dados de uma ou mais tabelas e vistas ou copiar os resultados de uma consulta pode ser especificado.
Seleccionar o botão Copiar dados de uma ou mais tabelas ou vistas e clicar no botão Próximo. A janela Seleccionar Tabela Fonte e Vistas permite escolher uma ou mais tabelas e vistas a partir das quais pretende exportar dados do SQL Server para um ficheiro Excel clicando na caixa de verificação ao lado do nome da tabela/vista:
Como se pode notar, assim que a caixa de verificação ao lado de uma tabela/vista for marcada, o nome da tabela/vista será copiado sob a coluna Destino. Este nome representa o nome da folha onde os dados da tabela serão colocados, este nome pode ser alterado para o que quiser, mas por agora, será deixado como está:
Para visualizar quais os dados que serão gerados para um ficheiro Excel, clique no botão Preview:
Depois de escolher que tabelas/visões os dados serão copiados para um ficheiro Excel, clique no botão Next (Seguinte):
Na janela Guardar como Pacote de Execução, marcar a caixa de verificação Executar imediatamente e clicar no botão Seguinte:
A janela Completar o Assistente mostra todas as opções que são escolhidas para as operações de exportação. Para finalizar o processo de exportação de dados do SQL Server para um ficheiro Excel, clique no botão Finish (Concluir). A última janela do Assistente de Importação e Exportação do SQL Server mostra o estado dos dados foram exportados com sucesso ou ocorreram alguns erros durante a exportação de dados:
No nosso caso, os dados foram gerados com sucesso para os Dados SQL.xlsx na folha DimScenario:
Exportar dados do SQL Server para um ficheiro Excel usando código T-SQL
O OPENROWSET Transact-SQL pode ser usado para exportar dados do SQL Server para um ficheiro Excel via SSMS. Num editor de consulta digitar e executar o seguinte código:
INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;
Database=C:\Users\Zivko\Desktop\SQL Data.xlsx;','SELECT * FROM ')
SELECT * FROM dbo.DimScenario
Mas, ao executar o código acima, pode ocorrer o seguinte erro:
Msg 15281, Level 16, State 1, Line 1
SQL Server blocked access to STATEMENT ‘OpenRowset/OpenDatasource’ of component ‘Ad Hoc Distributed Queries’ because this component is closed as part of the security configuration for this server. Um administrador de sistema pode permitir a utilização de ‘Ad Hoc Distributed Queries’ utilizando sp_configure. Para mais informações sobre a activação de ‘Ad Hoc Distributed Queries’, procure por ‘Ad Hoc Distributed Queries’ em SQL Server Books Online.
Para resolver este erro, a opção ‘Ad Hoc Distributed Queries’ deve ser activada. Isto pode ser feito usando o procedimento sp_configure e executando o seguinte código SQL num editor de consultas:
EXEC sp_configure 'show advanced options', 1RECONFIGUREEXEC sp_configure 'Ad Hoc Distributed Queries', 1RECONFIGURE
Após executar o código acima, aparecerá a seguinte mensagem que indica que as opções mostrar opções avançadas e as opções Ad Hoc Distributed Queries estão activadas:
Opção de configuração ‘mostrar opções avançadas’ alterada de 0 para 1. Execute a declaração RECONFIGURA para instalar.
A opção de configuração ‘Ad Hoc Distributed Queries’ foi alterada de 0 para 1. Execute a instrução RECONFIGURE para install.
Agora, quando novamente o código for executado:
INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;
Database=C:\Users\Zivko\Desktop\SQL Data.xlsx;','SELECT * FROM ')
SELECT * FROM dbo.DimScenario
O seguinte erro pode aparecer:
OLE DB provider “Microsoft.ACE.OLEDB.12.0” para servidor ligado “(null)” mensagem devolvida “O motor de base de dados Microsoft Access não pode abrir ou escrever no ficheiro “. Já está aberto exclusivamente por outro utilizador, ou necessita de permissão para ver e escrever os seus dados”.
Msg 7303, Nível 16, Estado 1, Linha 1
Não pode inicializar o objecto da fonte de dados do fornecedor OLE DB “Microsoft.ACE.OLEDB.12.0” para servidor ligado “(null)
Isto geralmente acontece devido a permissões inadequadas.
Mais sobre como resolver estes problemas pode ser encontrado na página Como consultar dados do Excel usando servidores ligados ao SQL Server.
Uma maneira de resolver este problema é abrir o SSMS como administrador e executar o código novamente. Mas desta vez, pode aparecer outro erro:
Msg 213, Nível 16, Estado 1, Linha 1
Nome da coluna ou número de valores fornecidos não corresponde à definição da tabela.
Para resolver este problema, abrir ficheiro excel (por exemplo, Dados SQL.xlsx) para o qual está previsto armazenar os dados da tabela do SQL Server (por exemplo dbo.DimScenario) e introduzir os nomes das colunas que representarão os nomes das colunas da tabela DimScenario:
Fechar os Dados SQL.xlsx e mais uma vez executar o código:
Agora, aparecerá a seguinte mensagem:
(3 linhas afectadas)
Finalmente, os dados da tabela do SQL Server são copiados para o ficheiro Excel:
Importar dados do SQL Server para o Excel usando o diálogo do Assistente de Ligação de Dados
Outra forma de copiar dados da tabela do SQL Server para um ficheiro Excel é usar o diálogo do Assistente de Ligação de Dados do Excel.
Para o conseguir, abrir um ficheiro Excel (por exemplo, SQL Data.xlsx) no qual se pretende importar dados. No separador Dados, sob o sub-menu De Outra Fonte, escolha o comando De SQL Server:
Isto abrirá a caixa de diálogo do Assistente de Ligação de Dados. No nome do Servidor, introduza um nome da instância do SQL Server da qual os dados precisam de ser copiados. Na secção de Log on credentials, escolha o modo de autenticação para a ligação da fonte de dados e clique no botão Next:
A partir da caixa Select the database that contains the data you want drop down, escolha a base de dados a partir da qual os dados serão copiados. Na grelha, todas as tabelas e vistas disponíveis serão listadas. Escolha uma tabela/vista desejada (por exemplo DimScenario) e clique no botão Next (Seguinte) para prosseguir.
Nesta janela, deixe tudo como está e prima o botão Finish:
No diálogo Importar Dados, escolha o botão de rádio Tabela, debaixo do botão Onde quer colocar os dados? escolha o botão de rádio Folha de trabalho existente e prima o botão OK:
Após premir o botão OK, os dados da tabela DimScenario serão copiados para os Dados SQL.xslx file:
Exportar dados do SQL Server para um ficheiro Excel usando Export to Excel a partir do ApexSQL Complete
Usando a funcionalidade Export to Excel a partir do ApexSQL Complete, add-in para SSMS e Visual Studio, a exportação de dados da tabela do SQL Server para um ficheiro Excel pode ser feita com um clique.
Num editor de consultas, digite e execute o seguinte código:
SELECT * FROM dbo.DimScenario ds
Da grelha de Resultados, seleccione os dados que deseja exportar para um ficheiro Excel, clique com o botão direito do rato na grelha de Resultados e, no menu de contexto, escolha a opção Exportar para Excel:
br>Isto abrirá uma nova pasta de trabalho Excel e os dados seleccionados da grelha de Resultados serão copiados na primeira folha de trabalho:
Mais sobre a exportação para Excel via ApexSQL Complete pode ser encontrada na página Exportar para Excel.
Ver também:
- Tutorial: Importar Dados para o Excel, e Criar um Modelo de Dados
- Começar com este simples exemplo do Assistente de Importação e Exportação
- Como consultar dados do Excel usando servidores ligados ao SQL Server
- Exportar para o Excel