Data Integration – Kettle – Importando Planilha do Excel para tabela do banco de dados PostgreSQL

A idéia desse post é demonstrar como criar uma rotina de importação de uma tabela de excel para uma tabela do banco de dados postgreSQL, onde essa tabela pode ser uma dimensão do Data Ware House.

1º Criamos uma planilha no Excel, salvamos essa planilha com o nome “cadClientes”, na planilha deve existir as colunas: código,Nome, Idade, Telefone, e também inserir dados nessa planilha, ficando assim:

2º Criamos um banco de dados e depois criamos uma tabela(que pode ser uma dimensão em um projeto DW, por exemplo), para criar a tabela utilize o comando:

CREATE TABLE dime_cadcli

(

  pk_cliente integer NOT NULL,

  clie_nome character varying(50) NOT NULL,

  clie_idade integer NOT NULL,

  clie_telefone character varying(25) NOT NULL,

  CONSTRAINT pk_clientes PRIMARY KEY (pk_cliente)

)

WITH (

  OIDS=FALSE

);

ALTER TABLE dime_cadcli

  OWNER TO postgres;

3º Vamos executar o Spoon:

- Se Windows c:\opt\pentaho\biclient\data-integration\spoon.bat;

- Se Linux /opt/pentaho/biclient/data-integration/spoon.sh;

- Ao executar o Spoon abrirá a janela inicial:

4º Vamos no menu: File => Novo => Transformação;

5º Vamos no menu File => Save, e salvamos a transformação como “Dime_CadClientes”;

6º Vamos na aba “Design” escolhemos Input => Microsoft Excel Input;

7º Dois cliques em cima do “Microsoft Excel Input”, abrirá as configurações, primeiramente vamos definir o nome desse “Step” no campo nome do Spep como “Excel_Dime_CadClientes”, e logo abaixo no quadro “select files” coluna “file/directory” vamos apontar o local que se encontra nossa planilha do excel para importação dos dados, no meu caso é “cadClientes.xls”, assim:

8º na aba Sheets, coluna Sheet name: coloque Plan1, na coluna Start now: 0, na coluna Start column:0;

9º Na coluna Content, marque a opção “Header”, marque a opção “No empty rows”, desmarque a opção “Stop on empty row”, campo Limit: 0, Encoding: Windows-1250, Spread sheet type (engine): Excel 97-2003 XLS (JXL), marque a opção add filenames to result, ficando assim:

10º Aba Error Handling pode deixar como vem padrão do Step;

11º Na Aba Fields, no campo "Name" colocamos o nome da coluna da planilha do excel, e no campo Type definimos o tipo da entrada do dado, então na coluna Name colocamos: Codigo, Nome, Idade, Telefone, na coluna  Type colocamos: Integer, String, Integer, String, conforme imagem abaixo:

12º Depois podemos clicar no botão Ok, fechando o Step;

13º Vamos novamente na Aba “Design” => Transform => e selecionamos o Step “Select Values”;

14º clicamos em cima do Step  Excel_Dime_CadClientes e araste hop até o Step “Select Values”, ficando assim:

15º Dois cliques no Step “Select Values”, na aba “Select & Alter”, na coluna FieldName, inserimos exatamente os nomes das colunas do excel(na mesma sequência: Codigo, Nome, Idade, Telefone, ficando assim:

16º Na Aba "Remove", não informamos nada, pois não precisamos remover nenhuma variável;

17º Na aba “Meta-data”:
- Na coluna Fieldname informamos os nomes dos campos informados no “Select & Alter” que são: Codigo, Nome, Idade, Telefone;
- Na coluna “Rename to” renomeamos cada campo para o nome da variável da tabela do banco de dados, que no exemplo é: pk_cliente, clie_nome, clie_idade, clie_telefone;
- Na coluna Type informamos o tipo de cada variável, que no caso é: Integer, String, Integer, String;
- Depois clicamos no botão “OK”, ficando assim:

18º Voltando a Tela inicial, vamos novamente na aba Design, selecionamos o “Table Output”, depois clicamos no Step “Select values” e arrastamos o “hop” (setinha) até o “Table output”, ficando assim:

19º Dois Cliques em cima do Step “Table Output”, no campo Nome do Step, vamos escrever “Grava Clientes”, Ao lado do campo Conection exite o botão “Edit”, clicamos no botão edite, e aparecerá a tela “Database Conection”:

20º  Na Janela “Database Conection”,

- no campo “Connection Name”: digitamos bancoteste(é apenas o nome da conexão);

-  na opção “Connection Type”: selecionamos a opção PostgreSQL;

-  na opção “Access”: selectionamos Native(JDBC);

-  no campo “Host Name”: o IP do banco de dados, no caso é localhost;

- no campo “Database Name”: o nome do banco de dados, no caso é teste;

- no campo “Port Number”: a portão de conexão com o banco de dados, no caso 5432;

- no campo “User Name”: usuário do banco de dados, no caso postgres;

- no campo “Password”: a senha do usuário no banco de dados, no caso postgres;

- Pode ser usuado o botão “Test” para verificar se está tudo certo com a conexão, depois clicamos no botão “OK” para salvar, a conexão ficará assim:

21º Voltando a Janela do “Table Output”:

- Vamos no campo “Target table” e informamos a tabela do banco de dados a ser inseridas as informações, no caso “dime_cadcli”;

- No campo “Commit size” informamos o número 1000(quantidades de registros para commit);

- Marcamos a opção “Truncate table”, para sempre limpar a tabela e importar tudo do zero novamente;

- Marcamos a opção “Use Batch Update for Inserts”;

- Marcamos a opção “Store the tablename field”;

- Na aba “Database Fields” não é preciso nesse caso informar nenhuma configuração;

A configuração do Step “Table Output” ficou assim:

Clicamos no botão “OK” para sair do Step;

22º Voltando para a Tela principal, agora basta executar a transformação: Action => Rum, se todos os passos foram seguidos dos os Steps apareceram com um “V” na cor verde, simbolizando que a transformação foi bem sucedida:

23º Para conferir se os dados realmente foram importados para a tabela do banco de dados podemos executar uma consulta no banco de dados através do PgAdmin, assim:

select * from dime_cadcli

O Resultado apresentado precisa ser: