Exemplos de extração de dados de arquivos do Microsoft Excel (DataStage® )

É possível construir tarefas de amostra que extraem dados de arquivos do Microsoft Excel. Neste exemplo, o estágio Sequential File é usado como estágio de saída. É possível usar qualquer outro estágio de saída para criação da saída.

Para obter os arquivos para os exemplos, extraia o arquivo IS_install\Clients\Samples\Connectors\UnstructuredData_Samples.zip.

Exemplo 1: Extração de dados de um intervalo em um arquivo do Microsoft Excel

Crie um trabalho que use o estágio do Excel para recuperar dados de um intervalo em uma planilha do Microsoft Excel.

Este exemplo usa o arquivo de amostra do Microsoft Excel Employee1.xls, que contém detalhes dos funcionários que trabalham em uma organização. Esse arquivo de amostra tem três planilhas: Sheet1, Sheet2 e Sheet3.Sheet1 contém informações sobre os funcionários de cada departamento da organização. Sheet2 e Sheet3 estão em branco. Neste exemplo, você extrai informações de negócios sobre somente os funcionários que trabalham para o departamento B01.

Para extrair dados de um intervalo em um arquivo Excel:
  1. Crie um trabalho que inclua um estágio de Excel e um estágio de Sequential File.
  2. Clique duas vezes no estágio do Excel.
  3. Na janela Configuration, especifique o caminho completo do arquivo de entrada do Microsoft Excel Employee1.xls.
  4. Na lista Opção de intervalo, selecione Especificar todo o intervalo de dados para extrair os dados em um intervalo específico.
  5. No Expressão de intervalo campo, especifiqueSheet1!A16:K28 .
  6. No campo Cabeçalho da coluna, selecione a Primeira linha de intervalos de dados. Quando a Primeira linha de intervalos de dados é selecionada, a primeira linha é considerada como o cabeçalho e o estágio do Excel começa a extrair a partir da segunda linha.
  7. Desmarque a caixa de seleção ao lado das colunas E (PHONE NO) e I (BIRTH DATE) do Microsoft Excel.
  8. Clique em Importar e OK. Quando você clica em Importar, o painel Mapa no canto inferior direito da janela Configuração é atualizado.
  9. Na página Saída > Coluna, altere o tipo da coluna EMP_NO para Inteiro e, em seguida, clique em OK.
  10. Dê um clique duplo no estágio Sequential File. No Propriedades página, especifique o caminho onde deseja que o arquivo de saída seja criado, seguido pelo nome do arquivoOutputOfExample1.txt e clique em OK.
  11. Salvar a tarefa. Agora, você pode compilar e executar o trabalho.

A tabela a seguir exibe as informações em um arquivo de entrada do Microsoft Excel que contém as informações do funcionário para diferentes departamentos.

Tabela 1. Arquivo do Microsoft Excel de amostra com detalhes do funcionário
EMP NO PRIMEIRO NOME MID INIT SOBRENOME PHONE NO HIRE DATE CARGO SEX BIRTH DATE
Funcionários em DEPT_A00
22 CHRISTINE I HAAS 3978 1/1/1995 PRES F 8/24/1963
20 MICHAEL L THOMSON 3476 10/10/2003 GERENTE M 2/2/1976
30 SALLY A KWAN 4738 4/5/2005 GERENTE F 5/11/1971
50 JOHN B GEYER 6789 8/17/1979 GERENTE M 9/15/1955
Funcionários em DEPT_B01
60 IRVING F STERN 6423 9/14/2003 GERENTE M 7/7/1975
65 EVA D PULASKI 7831 9/30/2003 GERENTE F 5/26/2003
90 EILEEN W HENDERSON 5498 8/15/2000 GERENTE F 5/15/1971
100 THEODORE Q SPENSER 742 6/19/2000 GERENTE M 12/18/1980
110 VINCENZO G LUCCHESSI 3490 5/16/1988 REPRES. DE VENDAS M 11/5/1958
120 SEAN   O'CONNELL 2167 12/51993 CLERK M 10/18/1972
130 DELORES M QUINTANA 4578 7/28/2001 Analista F 9/15/1955
140 HEATHER A NICHOLLS 1793 12/15/2006 Analista F 1/19/1976
150 BRUCE   ADAMSON 4510 2/12/2002 DESIGNER M 5/17/1972
160 ELIZABETH R PIANKA 3782 10/11/2006 DESIGNER F 4/12/1980
1770 MASATOSHI J YOSHIMURA 2890 9/15/1999 DESIGNER M 1/5/1981
180 MARILYN S SCOUTTEN 1682 7/7/2003 DESIGNER F 2/21/1978

Depois que o trabalho for executado, você poderá abrir o arquivoOutputOfExample1.txt arquivo para visualizar o resultado.

O arquivo OutputOfExample1.txt exibe os dados de DEPT_B01, exceto as colunas PHONE NO e BIRTH DATE:
"60","IRVING","F","STERN","2003-09-14","MANAGER ","M","72250","500"
"70","EVA","D","PULASKI","2005-09-30","MANAGER ","F","96170","700"
"90","EILEEN","W","HENDERSON","2000-08-15","MANAGER ","F","89750","600"
"100","THEODORE","Q","SPENSER","2000-06-19","MANAGER ","M","86150","500"
"110","VINCENZO","G","LUCCHESSI","1988-05-16","SALESREP","M","66500","900"
"120","SEAN"," ","O'CONNELL","1993-12-05","CLERK   ","M","49250","600"
"130","DELORES","M","QUINTANA","2001-07-28","ANALYST ","F","73800","500"
"140","HEATHER","A","NICHOLLS","2006-12-15","ANALYST ","F","68420","600"
"150","BRUCE"," ","ADAMSON","2002-02-12","DESIGNER","M","55280","500"
"160","ELIZABETH","R","PIANKA","2006-10-11","DESIGNER","F","62250","400"
"170","MASATOSHI","J","YOSHIMURA","1999-09-15","DESIGNER","M","44680","500"
"180","MARILYN","S","SCOUTTEN","2003-07-07","DESIGNER","F","51340","500"

Exemplo 2: extraindo dados de várias planilhas do Microsoft Excel

Este exemplo usa o arquivo de amostra do Microsoft Excel Employee2.xls. Esse arquivo de amostra tem as planilhas a seguir: DEPT A00, DEPT B01, DEPT C01 e DEPT D01. Cada planilha contém informações sobre os funcionários do departamento.

A estrutura de dados de cada planilha é semelhante. Cada planilha tem as colunas EMP NO, FIRST NAME, MID INIT, LAST NAME, PHONE NO, HIRE DATE, JOB e ADDRESS e a terceira linha é o cabeçalho. Mas cada planilha tem um número diferente de linhas.

Para extrair dados de vários arquivos:
  1. Na janela Configuration, especifique o caminho completo do arquivo de entrada do Microsoft Excel Employee2.xls.
  2. Na Opção de intervalo, selecione Especificar a linha de início.
  3. No campo Expressão de intervalo, especifique A3:H3. Quando o estágio é executado com a opção Especificar a primeira linha e nenhum nome de planilha específico é especificado na expressão de intervalo, o trabalho localiza a última linha e extrai as linhas até a última linha em um tempo de execução.
  4. No Cabeçalho da coluna, selecione a Primeira linha de intervalos de dados.
  5. No Propriedade guia, marque a caixa de seleção ao lado da propriedade para extrair o valor da propriedade. Neste exemplo, selecione o Sheetname como a propriedade.
  6. Clique em Importar. O estágio gera os mapeamentos de coluna.
  7. Para tornar o SheetNname coluna a primeira coluna da lista, selecione a SheetName coluna e clique Acima até o SheetName coluna é a primeira coluna da lista.
  8. Na tabela de mapeamento, insira uma linha para a coluna ADDRESS no arquivo de entrada que contém o hiperlink.
    1. Clique Inserir
    2. Na opção Item do Excel, selecione Coluna ADDRESS.
    3. Na célula da opção Importar na nova linha, selecione o Endereço do hiperlink.
    4. Especifique o nome da coluna EMAIL_ADDRESS do DataStage para a nova linha. Em seguida, clique em OK.
  9. Clique na guia Saída > Coluna para alterar o tipo de dados ou outros atributos.
  10. Mude o tipo de coluna EMP_NO para Número inteiro. Clique em OK.
  11. Na página Propriedades, especifique o caminho em que você deseja que o arquivo de saída seja criado, seguido pelo nome do arquivo OutputOfExample2.txt. Clique em OK.
  12. Salvar a tarefa. Agora, você pode compilar e executar o trabalho.

Um exemplo são os arquivos de entrada do Microsoft Excel que contêm as informações dos funcionários de cada departamento em diferentes planilhas. Os extratos de trabalho dos dados dos funcionários de todas as planilhas são exibidos na forma das tabelas a seguir:

Tabela 2. Informações de funcionários em DEPT_A00
EMP NO PRIMEIRO NOME MID INIT SOBRENOME PHONE NO HIRE DATE CARGO SEX BIRTH DATE
22 CHRISTINE I HAAS 3978 1/1/1995 PRES F 8/24/1963
20 MICHAEL L THOMSON 3476 10/10/2003 GERENTE M 2/2/1976
30 SALLY A KWAN 4738 4/5/2005 GERENTE F 5/11/1971
50 JOHN B GEYER 6789 8/17/1979 GERENTE M 9/15/1955
Tabela 3. Detalhes dos funcionários em Funcionários em DEPT_B01
EMP NO PRIMEIRO NOME MIDI NIT SOBRENOME PHONE NO HIRE DATE CARGO SEX BIRTH DATE
60 IRVING F STERN 6423 9/14/2003 GERENTE M 7/7/1975
65 EVA D PULASKI 7831 9/30/2003 GERENTE F 5/26/2003
90 EILEEN W HENDERSON 5498 8/15/2000 GERENTE F 5/15/1971
100 THEODORE Q SPENSER 742 6/19/2000 GERENTE M 12/18/1980
110 VINCENZO G LUCCHESSI 3490 5/16/1988 REPRES. DE VENDAS M 11/5/1958
120 SEAN   O'CONNELL 2167 12/51993 CLERK M 10/18/1972
130 DELORES M QUINTANA 4578 7/28/2001 Analista F 9/15/1955
140 HEATHER A NICHOLLS 1793 12/15/2006 Analista F 1/19/1976
150 BRUCE   ADAMSON 4510 2/12/2002 DESIGNER M 5/17/1972
160 ELIZABETH R PIANKA 3782 10/11/2006 DESIGNER F 4/12/1980
1770 MASATOSHI J YOSHIMURA 2890 9/15/1999 DESIGNER M 1/5/1981
180 MARILYN S SCOUTTEN 1682 7/7/2003 DESIGNER F 2/21/1978

Depois que o trabalho for executado, você poderá abrir o arquivo OutputOfExample2.txt que contém o seguinte resultado.

"DEPT A00","10","CHRISTINE","I","HAAS","3978","1995-01-01","PRES    ","CHRISTINE HAAS","mailto:CHRISTINE%20HAAS@abc.com"
"DEPT A00","20","MICHAEL","L","THOMPSON","3476","2003-10-10","MANAGER ","MICHAEL THOMPSON","mailto:MICHAEL%20THOMPSON@abc.com"
"DEPT A00","30","SALLY","A","KWAN","4738","2005-04-05","MANAGER ","SALLY KWAN","mailto:SALLY%20KWAN@abc.com"
"DEPT A00","50","JOHN","B","GEYER","6789","1979-08-17","MANAGER ","JOHN GEYER","mailto:JOHN%20GEYER@abc.com"
"DEPT B01","60","IRVING","F","STERN","6423","2003-09-14","MANAGER ","IRVING STERN","mailto:IRVING%20STERN@abc.com"
"DEPT B01","70","EVA","D","PULASKI","7831","2005-09-30","MANAGER ","EVA PULASKI","mailto:EVA%20PULASKI@abc.com"
"DEPT B01","90","EILEEN","W","HENDERSON","5498","2000-08-15","MANAGER ","EILEEN HENDERSON","mailto:EILEEN%20HENDERSON@abc.com"
"DEPT B01","100","THEODORE","Q","SPENSER","972","2000-06-19","MANAGER ","THEODORE SPENSER","mailto:THEODORE%20SPENSER@abc.com"
"DEPT B01","110","VINCENZO","G","LUCCHESSI","3490","1988-05-16","SALESREP","VINCENZO LUCCHESSI","mailto:VINCENZO%20LUCCHESSI@abc.com"
"DEPT B01","120","SEAN"," ","O'CONNELL","2167","1993-12-05","CLERK   ","SEAN O'CONNELL","mailto:SEAN%20O'CONNELL@abc.com"
"DEPT B01","130","DELORES","M","QUINTANA","4578","2001-07-28","ANALYST ","DELORES QUINTANA","mailto:DELORES%20QUINTANA@abc.com"
"DEPT B01","140","HEATHER","A","NICHOLLS","1793","2006-12-15","ANALYST ","HEATHER NICHOLLS","mailto:HEATHER%20NICHOLLS@abc.com"
"DEPT B01","150","BRUCE"," ","ADAMSON","4510","2002-02-12","DESIGNER","BRUCE ADAMSON","mailto:BRUCE%20ADAMSON@abc.com"
"DEPT B01","160","ELIZABETH","R","PIANKA","3782","2006-10-11","DESIGNER","ELIZABETH PIANKA","mailto:ELIZABETH%20PIANKA@abc.com"
"DEPT B01","170","MASATOSHI","J","YOSHIMURA","2890","1999-09-15","DESIGNER","MASATOSHI YOSHIMURA","mailto:MASATOSHI%20YOSHIMURA@abc.com"
"DEPT B01","180","MARILYN","S","SCOUTTEN","1682","2003-07-07","DESIGNER","MARILYN SCOUTTEN","mailto:MARILYN%20SCOUTTEN@abc.com"
"DEPT C01","190","JAMES","H","WALKER","2986","2004-07-26","DESIGNER","JAMES WALKER","mailto:JAMES%20WALKER@abc.com"
"DEPT C01","200","DAVID"," ","BROWN","4501","2002-03-03","DESIGNER","DAVID BROWN","mailto:DAVID%20BROWN@abc.com"
"DEPT C01","210","WILLIAM","T","JONES","942","1998-04-11","DESIGNER","WILLIAM JONES","mailto:WILLIAM%20JONES@abc.com"
"DEPT C01","220","JENNIFER","K","LUTZ","672","1998-08-29","DESIGNER","JENNIFER LUTZ","mailto:JENNIFER%20LUTZ@abc.com"
"DEPT C01","230","JAMES","J","JEFFERSON","2094","1996-11-21","CLERK   ","JAMES JEFFERSON","mailto:JAMES%20JEFFERSON@abc.com"
"DEPT C01","240","SALVATORE","M","MARINO","3780","2004-12-05","CLERK   ","SALVATORE MARINO","mailto:SALVATORE%20MARINO@abc.com"
"DEPT C01","250","DANIEL","S","SMITH","961","1999-10-30","CLERK   ","DANIEL SMITH","mailto:DANIEL%20SMITH@abc.com"
"DEPT C01","260","SYBIL","P","JOHNSON","8953","2005-09-11","CLERK   ","SYBIL JOHNSON","mailto:SYBIL%20JOHNSON@abc.com"
"DEPT D01","270","MARIA","L","PEREZ","9001","2006-09-30","CLERK   ","MARIA PEREZ","mailto:MARIA%20PEREZ@abc.com"
"DEPT D01","280","ETHEL","R","SCHNEIDER","8997","1997-03-24","OPERATOR","ETHEL SCHNEIDER","mailto:ETHEL%20SCHNEIDER@abc.com"
"DEPT D01","290","JOHN","R","PARKER","4502","2006-05-30","OPERATOR","JOHN PARKER","mailto:JOHN%20PARKER@abc.com"
"DEPT D01","300","PHILIP","X","SMITH","2095","2002-06-19","OPERATOR","PHILIP SMITH","mailto:PHILIP%20SMITH@abc.com"
"DEPT D01","310","MAUDE","F","SETRIGHT","3332","1994-09-12","OPERATOR","MAUDE SETRIGHT","mailto:MAUDE%20SETRIGHT@abc.com"
"DEPT　D01","320","RAMLAL","V","MEHTA","9990","1995-07-07","FIELDREP","RAMLAL MEHTA","mailto:RAMLAL%20MEHTA@abc.com"
"DEPT D01","330","WING"," ","LEE","2103","2006-02-23","FIELDREP","WING LEE","mailto:WING%20LEE@abc.com"
"DEPT D01","340","JASON","R","GOUNOT","5698","1977-05-05","FIELDREP","JASON GOUNOT","mailto:JASON%20GOUNOT@abc.com"

Exemplo 3: extraindo dados de vários intervalos que têm estruturas de dados diferentes em um arquivo do Microsoft Excel

Este exemplo usa o arquivo de amostra do Microsoft Excel Employee3.xls. Este arquivo de amostra tem duas planilhas, Departments e Employees, que têm estruturas de dados diferentes.

Neste exemplo, o estágio Excel tem dois links de saída. Você extrai dados da planilha Departments para o primeiro link e da planilha Employees para o segundo link.

Para extrair diferentes estruturas de dados:
  1. Crie um trabalho que inclua um estágio de Excel e dois estágios Sequential File. Você pode renomear seus links como Departamentos e Funcionários.
  2. Clique duas vezes no estágio do Excel. Na janela Configuration, especifique o caminho completo do arquivo de entrada do Microsoft Excel Employee3.xls.
  3. Especifique os dados a serem extraídos da planilha Departments e conclua as seguintes subetapas para gerar os mapeamentos de coluna.
    1. De Link caixa de listagem, selecione Departamentos.
    2. De Opção de alcance lista, selecione Especifique todo o intervalo.
    3. No campo Expressão de intervalo, especifique Departamentos!A2:C6 .
    4. No Cabeçalho da coluna, selecione a Primeira linha de intervalos de dados.
    5. Clique em Importar e, em seguida, clique em OK.
  4. Especifique os dados a serem extraídos da planilha Funcionários e conclua as subetapas abaixo para gerar os mapeamentos de coluna.
    1. Na caixa de listagem Link, selecione Funcionários.
    2. De Opção de alcance lista, Especifique todo o intervalo.
    3. No campo Expressão de intervalo, especifique Funcionários!A2:L34.
    4. No Cabeçalho da coluna, selecione Primeira linha de intervalos de dados.
    5. Clique em Importar e, em seguida, clique em OK.
  5. Na página Saída, selecione o link Funcionários como o Nome da saída.
  6. Na página Colunas, altere o tipo de dados da coluna EMP_NO para um número inteiro e, em seguida, clique em OK.
  7. Clique duas vezes no primeiro Sequential File estágioOutput_1 e especifique o caminho para criar o arquivo de saída, seguido pelo nome do arquivoOutputOfExample3_1.txt .
  8. Clique duas vezes no segundo estágio Sequential File Output_2 e especifique o caminho para criar o arquivo de saída, seguido do nome do arquivo OutputOfExample3_2.txt
  9. Salvar a tarefa. Agora, você pode compilar e executar o trabalho.

Um exemplo de arquivo de entrada do Microsoft Excel Employee3.xls contém informações de departamento na planilha Departments e informações de funcionário na planilha Employees. A tarefa extrai dados do departamento para o arquivo OutputOfExample3_1.txt e dados do funcionário para o OutputOfExample3_2.txt.

Após a execução da tarefa, abra o arquivo OutputOfExample3_1.txt e o arquivo OutputOfExample3_2.txt. O arquivo OutputOfExample3_1.txt deve corresponder à planilha Departamentos e o arquivo OutputOfExample3_2.txt deve corresponder à planilha Funcionários do arquivo Employee.xls.