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:
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:
- 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:
