Importar e Exportar resultado PowerShell para tabela SQL Server

Publicado: 25 de março de 2016 em PowerShell, Virtual PASS BR
Tags:

aOlá pessoal, a dica de hoje surgiu através de uma comversa com o amigo Fabrício Lima em como exportar resultados de um Pipeline PowerShell para uma tabela no SQL Server. Como o assunto "gravar em tabela" é muito solicitado eu resolvi criar um artigo explicando algumas formas.

Existem diversas formas de importar e exportar dados em tabelas através do PowerShell, porém, a grande maioria não grava diretamente do Pipeline. Nos exemplos abaixo vou citar os mais conhecidos e citar o uso de cada um.

É possível criar pacotes de funções de diversas maneiras e, por questões de simplicidade, deixarei todas as funções em um arquivo chamado Funcoes.ps1. Para os exemplos abaixo o arquivo está em c:\temp\Funcoes.ps1, você pode baixar o arquivo aqui. Este arquivo servirá como nosso reporitório de Funções, logo, todas as funçõe ficarão nele e faremos apenas a referência ao mesmo dentro da console PowerShell. Para referenciar o arquivo basta colar o código abaixo no PowerGui, PowerShell Console ou PowerShell ISE. A idéia é bem parecida com o import do C# e Java. Basta colocar o código abaixo no início de qualquer chamada:

. c:\temp\Funcoes.ps1 #Atenção que o "." É obrigatório e com espaço.

Para os exemplos abaixo eu usei o PowerGUI que pode ser obtido gratuitamente aqui.

Invoke-SqlCmd2

http://gallery.technet.microsoft.com/ScriptCenter/en-us/7985b7ef-ed89-4dfd-b02a-433cc4e30894

A partir do SQL Server 2008 a Microsoft disponibilida um cmdlet chamado Invoke-SqlCmd, porém, apenas nos PCs onde o sqlps está instalado. Para não criar dependências de instalação ( ou referências de snap-ins ) aconselho o uso da Invoke-SqlCmd2.

Esta função permite exportar dados do SQL Server. Ela pode retornar um DataSet, DataTable ou DataRow.

Vejamos um exemplo do seu uso:

#Dentro do PowerGUI execute o todo o código abaixo de uma vez

cls
#Código necessário para importar o arquivo de funções
. c:\temp\Funcoes.ps1

#Neste primeiro exemplo nós realizamos uma consulta na tabela clientes da base Northwind-PTBR.
#O resultado é retornado como um array de DataRows
#É possível usar um foreach para ler todos os dados
#Para nosso exemplo simplesmente estou mostrando a primeira linha $dt[0]
$dt = Invoke-Sqlcmd2 -ServerInstance "palestras2014" -Database "Northwind-PTBR" -Query "Select * from clientes"
$dt[0]

#No segundo exemplo realizaremos a exportação dos dados para o formato CSV usando o cmdlet Export-Csv, que é nativo do PowerShell
Invoke-Sqlcmd2 -ServerInstance "palestras2014" -Database "Northwind-PTBR" -Query "Select * from clientes" | Export-Csv -LiteralPath "c:\temp\clientes_invoke.txt"

Veja o arquivo de saída abaixo:



Write-DataTable

https://gallery.technet.microsoft.com/scriptcenter/2fdeaf8d-b164-411c-9483-99413d6053ae

Como o próprio nome diz, esta function usa a classe Data.SqlClient.SqlBulkCopy para importar ( salvar ) um DataTable in memory para uma tabela no SQL Server, logo, será preciso passar um DataTable para a funcão. Sendo assim, ele não funciona no pipeline.

Vejamos um exemplo de uso:

Iremos importar dados de um TXT/CSV que possui a seguinte estrutura:

O arquivo que será usado na importação está em c:\temp\clientes.txt

Como o Write-DataTable não cria a tabela então, precisamos criá-la antes de executar o script PowerShell. Para isso, execute o código abaixo para criar a tabela de destino no SQL Server Management Studio:

use tempdb --Troque pela base de destino desejada
go
CREATE TABLE clientes_writedatatable(
nome varchar(20),
idade char(2),
sexo char(1)
)

O código abaixo realiza a importação dos dados do CSV para o SQL Server:

#Dentro do PowerGUI execute o todo o código abaixo de uma vez

cls
#Código necessário para importar o arquivo de funções
. c:\temp\Funcoes.ps1

#Neste exemplo nós iremos mostrar como importar dados de um arquivo texto para dentro de uma tabela do SQL Server
#O comando abaixo importa os dados de um CSV e os transforma em um Array. O parâmetro header indica o nome das colunas que você deseja definir.
#A função Write-DataTable recebe um DataTable como parâmetro, por isso usamos a função Out-DataTable para transformar o Array do Import-CSV em um DataTable

$dt = Import-Csv c:\temp\clientes.txt -Header nome,idade,sexo -Delimiter "," | Out-DataTable

#Por fim, executamos o Write-DataTable passando o DataTable como parâmetro para ser salvo na tabela criada anteriormente

Write-DataTable -ServerInstance "palestras2014" -Database "tempdb" -TableName "clientes_writedatatable" -Data $dt

Abaixo é possível visualizar os dados importados:



Out-SQL

http://blogs.technet.com/b/sqlthoughts/archive/2008/10/03/out-sql-powershell-function-export-pipeline-to-a-new-sql-server-table.aspx

Esta função, criado por Alexey, permite salvar dados do Pipeline diretamente para o SQL Server. Eu fiz algumas mudanças na função para que a mesma possa dar suporte a cadeias de texto longas e nomes de colunas com espaço e acentos.

Basicamente você usa esta função no pipeline de qualquer cmdlet e direciona a saída do pipeline para uma tabela no SQL Server. A tabela é criada de forma automática. Segue exemplo abaixo:

cls
#Dentro do PowerGUI execute o todo o código abaixo de uma vez

cls
#Código necessário para importar o arquivo de funções
. c:\temp\Funcoes.ps1

#get-eventlog lê o log de eventos do Windows, neste caso apenas o application
#out-sql direciona a saída para uma tabela no SQL Server. Caso a tabela não exista o comando cria automaticamente.
#O parâmetro $RowId permite especificar um nome de coluna que será criado como Identity(1,1)
get-eventlog application -after (get-date).addDays(-1) | where-object {$_.EntryType -eq "error" -and $_.source -ne "DOCM"} |
out-sql -sqlserver "palestras2014" -database "tempdb" -table "tbl_EventLog" -dropexisting $true

E o resultado:

Bom pessoal, com isso finalizamos nosso artigo. Espero que seja útil.

Ficou interessado e quer aprender mais sobre PowerShell?

Entre em contato para informações sobre os cursos que ministramos abaixo:

1 – Administrando o Windows com Powershell

2 – Administrando o SQL Server com Powershell

demetrioi@hotmail.com

Abraço,

Demétrio Silva

Anúncios
comentários

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s