Compartilhar via

  • Artigo

Trabalhando com arquivos do Excel com a tarefa Script

O Integration Services oferece o gerenciador de conexões do Excel, a origem do Excel e o destino do Excel para trabalhar com dados armazenados em planilhas no formato de arquivo do Excel Microsoft. As técnicas descritas neste tópico utilizam a tarefa Script para obter informações sobre bancos de dados (arquivos de pasta de trabalho) e tabelas (planilhas e intervalos nomeados) do Excel disponíveis. Esses exemplos podem ser facilmente modificados para funcionar com quaisquer das outras fontes de dados com base em arquivo suportadas pelo Provedor OLE DB Microsoft Jet.

Configurando um pacote para testar os exemplos

Exemplo 1: Verificar se existe um arquivo do Excel

Exemplo 2: Verificar se existe uma tabela do Excel

Exemplo 3: Obter uma lista de arquivos do Excel em uma pasta

Exemplo 4: Obter uma lista de tabelas em um arquivo do Excel

Exibindo os resultados dos exemplos

ObservaçãoObservação

Se desejar criar uma tarefa mais fácil de ser reutilizada em vários pacotes, procure utilizar o código desse exemplo de tarefa Script como o ponto inicial de uma tarefa personalizada. Para obter mais informações, consulte Desenvolvendo uma tarefa personalizada.

Configurando um pacote para testar os exemplos

Você pode configurar um único pacote para testar todos os exemplos neste tópico. Os exemplos usam muitas das mesmas variáveis de pacote e as mesmas classes .NET Framework.

Para configurar um pacote para uso com os exemplos neste tópico

  1. Crie um projeto do Integration Services novo em Business Intelligence Development Studio e abra o pacote padrão para editar.

  2. Variáveis. Abra a janela Variáveis e defina as seguintes variáveis:

    • ExcelFile, de tipo String. Digite o caminho completo e o nome do arquivo em uma pasta de trabalho do Excel existente.

    • ExcelTable, de tipo String. Digite o nome de uma planilha existente ou intervalo nomeado na pasta de trabalho nomeada no valor da variável ExcelFile. Esse valor diferencia maiúsculas de minúsculas.

    • ExcelFileExists, de tipo Boolean.

    • ExcelTableExists, de tipo Boolean.

    • ExcelFolder, de tipo String. Digite o caminho completo de uma pasta que contenha pelo menos uma pasta de trabalho do Excel.

    • ExcelFiles, de tipo Object.

    • ExcelTables, de tipo Object.

  3. Importa instruções. A maioria dos exemplos de código requer que você importe um ou ambos namespaces .NET Framework seguintes no topo de seu arquivo de script:

    • System.IO, para operações do sistema de arquivos.

    • System.Data.OleDb, para abrir arquivos do Excel como fontes de dados.

  4. Referências. Os exemplos de código que leem informações de esquema de arquivos do Excel requerem uma referência no projeto de script para o namespace System.Xml.

  5. Defina a linguagem de scripts padrão para o componente Script usando a opção Linguagem de scripts na página Geral da caixa de diálogo Opções. Para obter mais informações, consulte Página Geral.

Exemplo 1 Descrição: Verificar se existe um arquivo do Excel

Esse exemplo determina se o arquivo da pasta de trabalho do Excel especificado na variável ExcelFile existe, e define o valor Booleano da variável ExcelFileExists para o resultado. Você pode usar esse valor Booleano para ramificar no fluxo de trabalho do pacote.

Para configurar esse exemplo de tarefa Script

  1. Acrescente uma tarefa Script nova ao pacote e altere seu nome para ExcelFileExists.

  2. No Editor da Tarefa Script, na guia Script, clique em ReadOnlyVariables e digite o valor da propriedade com um dos métodos seguintes:

    • Digite ExcelFile.

      - ou -

    • Clique no botão de reticências (...) próximo ao campo de propriedade e, na caixa de diálogo Selecionar variáveis, selecione a variável ExcelFile.

  3. Clique em ReadWriteVariables e digite o valor da propriedade com um dos métodos seguintes:

    • Digite ExcelFileExists.

      - ou -

    • Clique no botão de reticências (...) próximo ao campo de propriedade e, na caixa de diálogo Selecionar variáveis, selecione a variável ExcelFileExists.

  4. Clique em Editar Script para abrir o editor de script.

  5. Adicione uma instrução Imports para o namespace System.IO no topo do arquivo de script.

  6. Adicione o código seguinte:

Código de exemplo 1

Public Class ScriptMain
  Public Sub Main()
    Dim fileToTest As String

    fileToTest = Dts.Variables("ExcelFile").Value.ToString
    If File.Exists(fileToTest) Then
      Dts.Variables("ExcelFileExists").Value = True
    Else
      Dts.Variables("ExcelFileExists").Value = False
    End If

    Dts.TaskResult = ScriptResults.Success
  End Sub
End Class

public class ScriptMain
{
  public void Main()
  {
    string fileToTest;

    fileToTest = Dts.Variables["ExcelFile"].Value.ToString();
    if (File.Exists(fileToTest))
    {
      Dts.Variables["ExcelFileExists"].Value = true;
    }
    else
    {
      Dts.Variables["ExcelFileExists"].Value = false;
    }

    Dts.TaskResult = (int)ScriptResults.Success;
  }
}

Exemplo 2 Descrição: Verificar se existe uma tabela do Excel

Esse exemplo determina se a planilha ou intervalo nomeado do Excel especificado na variável ExcelTable existe no arquivo da pasta de trabalho do Excel especificado na variável ExcelFile, e define o valor Booleano da variável ExcelTableExists para o resultado. Você pode usar esse valor Booleano para ramificar no fluxo de trabalho do pacote.

Para configurar esse exemplo de tarefa Script

  1. Acrescente uma tarefa Script nova ao pacote e altere seu nome para ExcelTableExists.

  2. No Editor da Tarefa Script, na guia Script, clique em ReadOnlyVariables e digite o valor da propriedade com um dos métodos seguintes:

    • Digite ExcelTable e ExcelFile separados por vírgulas.

      - ou -

    • Clique no botão de reticências (...) próximo ao campo de propriedade e, na caixa de diálogo Selecionar variáveis, selecione as variáveis ExcelTable e ExcelFile.

  3. Clique em ReadWriteVariables e digite o valor da propriedade com um dos métodos seguintes:

    • Digite ExcelTableExists.

      - ou -

    • Clique no botão de reticências (...) próximo ao campo de propriedade e, na caixa de diálogo Selecionar variáveis, selecione a variável ExcelTableExists.

  4. Clique em Editar Script para abrir o editor de script.

  5. Adicione uma referência ao assembly System.Xml no projeto de script.

  6. Adicione instruções Imports para os namespaces System.IO e System.Data.OleDb no topo do arquivo de script.

  7. Adicione o código seguinte:

Código de exemplo 2

Public Class ScriptMain
  Public Sub Main()
    Dim fileToTest As String
    Dim tableToTest As String
    Dim connectionString As String
    Dim excelConnection As OleDbConnection
    Dim excelTables As DataTable
    Dim excelTable As DataRow
    Dim currentTable As String

    fileToTest = Dts.Variables("ExcelFile").Value.ToString
    tableToTest = Dts.Variables("ExcelTable").Value.ToString

    Dts.Variables("ExcelTableExists").Value = False
    If File.Exists(fileToTest) Then
      connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & fileToTest & _
        ";Extended Properties=Excel 8.0"
      excelConnection = New OleDbConnection(connectionString)
      excelConnection.Open()
      excelTables = excelConnection.GetSchema("Tables")
      For Each excelTable In excelTables.Rows
        currentTable = excelTable.Item("TABLE_NAME").ToString
        If currentTable = tableToTest Then
          Dts.Variables("ExcelTableExists").Value = True
        End If
      Next
    End If

    Dts.TaskResult = ScriptResults.Success
  End Sub
End Class

public class ScriptMain
{
    public void Main()
        {
            string fileToTest;
            string tableToTest;
            string connectionString;
            OleDbConnection excelConnection;
            DataTable excelTables;
            string currentTable;

            fileToTest = Dts.Variables["ExcelFile"].Value.ToString();
            tableToTest = Dts.Variables["ExcelTable"].Value.ToString();

            Dts.Variables["ExcelTableExists"].Value = false;
            if (File.Exists(fileToTest))
            {
                connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                "Data Source=" + fileToTest + ";Extended Properties=Excel 8.0";
                excelConnection = new OleDbConnection(connectionString);
                excelConnection.Open();
                excelTables = excelConnection.GetSchema("Tables");
                foreach (DataRow excelTable in excelTables.Rows)
                {
                    currentTable = excelTable["TABLE_NAME"].ToString();
                    if (currentTable == tableToTest)
                    {
                        Dts.Variables["ExcelTableExists"].Value = true;
                    }
                }
            }

            Dts.TaskResult = (int)ScriptResults.Success;

        }
}

Exemplo 3 Descrição: Obter uma lista de arquivos do Excel em uma pasta

Esse exemplo preenche uma matriz com a lista de arquivos do Excel encontrada na pasta especificada no valor da variável ExcelFolder, e copia a matriz para a variável ExcelFiles. Você pode usar o Enumerador Foreach de Variável para repetir nos arquivos da matriz.

Para configurar esse exemplo de tarefa Script

  1. Acrescente uma tarefa Script nova ao pacote e altere seu nome para GetExcelFiles.

  2. Abra o Editor da Tarefa Script, na guia Script, clique em ReadOnlyVariables e digite o valor da propriedade com um dos métodos seguintes:

    • Digite ExcelFolder

      - ou -

    • Clique no botão de reticências (...) próximo ao campo de propriedade e, na caixa de diálogo Selecionar variáveis, selecione a variável ExcelFolder.

  3. Clique em ReadWriteVariables e digite o valor da propriedade com um dos métodos seguintes:

    • Digite ExcelFiles.

      - ou -

    • Clique no botão de reticências (...) próximo ao campo de propriedade e, na caixa de diálogo Selecionar variáveis, selecione a variável ExcelFiles.

  4. Clique em Editar Script para abrir o editor de script.

  5. Adicione uma instrução Imports para o namespace System.IO no topo do arquivo de script.

  6. Adicione o código seguinte:

Exemplo 3 Código

Public Class ScriptMain
  Public Sub Main()
    Const FILE_PATTERN As String = "*.xls"

    Dim excelFolder As String
    Dim excelFiles As String()

    excelFolder = Dts.Variables("ExcelFolder").Value.ToString
    excelFiles = Directory.GetFiles(excelFolder, FILE_PATTERN)

    Dts.Variables("ExcelFiles").Value = excelFiles

    Dts.TaskResult = ScriptResults.Success
  End Sub
End Class

public class ScriptMain
{
  public void Main()
  {
    const string FILE_PATTERN = "*.xls";

    string excelFolder;
    string[] excelFiles;

    excelFolder = Dts.Variables["ExcelFolder"].Value.ToString();
    excelFiles = Directory.GetFiles(excelFolder, FILE_PATTERN);

    Dts.Variables["ExcelFiles"].Value = excelFiles;

    Dts.TaskResult = (int)ScriptResults.Success;
  }
}

Solução alternada

Em vez de usar uma tarefa Script para reunir uma lista de arquivos do Excel em uma matriz, você também pode usar o Enumerador de Arquivo Foreach para repetir em todos os arquivos do Excel em uma pasta. Para obter mais informações, consulte Como fazer loop por meio de arquivos e tabelas do Excel usando um contêiner Loop Foreach.

Exemplo 4 Descrição: Obter uma lista de tabelas em um arquivo do Excel

Esse exemplo preenche uma matriz com a lista de planilhas e intervalos nomeados encontrados no arquivo da pasta de trabalho especificado pelo valor da variável ExcelFile, e copia a matriz para a variável ExcelTables. Você pode usar o Enumerador Foreach de Variável para repetir nas tabelas da matriz.

ObservaçãoObservação

A lista de tabelas em uma pasta de trabalho do Excel inclui planilhas (que têm o sufixo $) e intervalos nomeados. Se você tiver que filtrar a lista para apenas planilhas ou apenas intervalos nomeados, talvez seja necessário acrescentar um código adicional para esse propósito.

Para configurar esse exemplo de tarefa Script

  1. Acrescente uma tarefa Script nova ao pacote e altere seu nome para GetExcelTables.

  2. Abra o Editor da Tarefa Script, na guia Script, clique em ReadOnlyVariables e digite o valor da propriedade com um dos métodos seguintes:

    • Digite ExcelFile.

      - ou -

    • Clique no botão de reticências (...) próximo ao campo de propriedade e, na caixa de diálogo Selecionar variáveis, selecione a variável ExcelFile.

  3. Clique em ReadWriteVariables e digite o valor da propriedade com um dos métodos seguintes:

    • Digite ExcelTables.

      - ou -

    • Clique no botão de reticências (...) próximo ao campo de propriedade e, na caixa de diálogo Selecionar variáveis, selecione a variável ExcelFiles.

  4. Clique em Editar Script para abrir o editor de script.

  5. Acrescente uma referência ao namespace System.Xml no projeto de script.

  6. Adicione uma instrução Imports para o namespace System.Data.OleDb no topo do arquivo de script.

  7. Adicione o código seguinte:

Exemplo 4 Código

Public Class ScriptMain
  Public Sub Main()
    Dim excelFile As String
    Dim connectionString As String
    Dim excelConnection As OleDbConnection
    Dim tablesInFile As DataTable
    Dim tableCount As Integer = 0
    Dim tableInFile As DataRow
    Dim currentTable As String
    Dim tableIndex As Integer = 0

    Dim excelTables As String()

    excelFile = Dts.Variables("ExcelFile").Value.ToString
    connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & excelFile & _
        ";Extended Properties=Excel 8.0"
    excelConnection = New OleDbConnection(connectionString)
    excelConnection.Open()
    tablesInFile = excelConnection.GetSchema("Tables")
    tableCount = tablesInFile.Rows.Count
    ReDim excelTables(tableCount - 1)
    For Each tableInFile In tablesInFile.Rows
      currentTable = tableInFile.Item("TABLE_NAME").ToString
      excelTables(tableIndex) = currentTable
      tableIndex += 1
    Next

    Dts.Variables("ExcelTables").Value = excelTables

    Dts.TaskResult = ScriptResults.Success
  End Sub
End Class

public class ScriptMain
{
  public void Main()
        {
            string excelFile;
            string connectionString;
            OleDbConnection excelConnection;
            DataTable tablesInFile;
            int tableCount = 0;
            string currentTable;
            int tableIndex = 0;

            string[] excelTables = new string[5];

            excelFile = Dts.Variables["ExcelFile"].Value.ToString();
            connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                "Data Source=" + excelFile + ";Extended Properties=Excel 8.0";
            excelConnection = new OleDbConnection(connectionString);
            excelConnection.Open();
            tablesInFile = excelConnection.GetSchema("Tables");
            tableCount = tablesInFile.Rows.Count;

            foreach (DataRow tableInFile in tablesInFile.Rows)
            {
                currentTable = tableInFile["TABLE_NAME"].ToString();
                excelTables[tableIndex] = currentTable;
                tableIndex += 1;
            }

            Dts.Variables["ExcelTables"].Value = excelTables;

            Dts.TaskResult = (int)ScriptResults.Success;
        }
}

Solução alternada

Em vez de usar uma tarefa Script para reunir uma lista de tabelas do Excel em uma matriz, você também pode usar o Enumerador de Conjunto de Linhas de Esquema ADO.NET Foreach para repetir em todos as tabelas (planinhas e intervalos nomeados) em um arquivo de pasta de trabalho do Excel. Para obter mais informações, consulte Como fazer loop por meio de arquivos e tabelas do Excel usando um contêiner Loop Foreach.

Exibindo os resultados dos exemplos

Se você configurou cada um dos exemplos deste tópico no mesmo pacote, você pode conectar todas as tarefas Script a uma tarefa Script adicional que exibe a saída de todos os exemplos.

Para configurar uma tarefa Script para exibir a saída dos exemplos neste tópico

  1. Acrescente uma tarefa Script nova ao pacote e altere seu nome para DisplayResults.

  2. Conecte cada uma das tarefas Script dos quatro exemplos uma a outra, de forma que cada tarefa seja executada depois que a anterior for concluída com êxito, e conecte a tarefa do quarto exemplo à tarefa DisplayResults.

  3. Abra a tarefa DisplayResults no Editor da Tarefa Script.

  4. Na guia Script, clique em ReadOnlyVariables e use um dos métodos seguintes para adicionar todas as sete variáveis listadas em Configurando um pacote para testar os exemplos:

    • Digite o nome de cada variável separado por vírgulas.

      - ou -

    • Clique no botão de reticências (...) próximo ao campo de propriedade e, na caixa de diálogo Selecionar variáveis, selecione as variáveis.

  5. Clique em Editar Script para abrir o editor de script.

  6. Adicione instruções Imports para os namespaces Microsoft.VisualBasic e System.Windows.Forms no topo do arquivo de script.

  7. Adicione o código seguinte:

  8. Execute o pacote e examine os resultados exibidos em uma caixa de mensagem.

Codifique para exibir o resultados

Public Class ScriptMain
  Public Sub Main()
    Const EOL As String = ControlChars.CrLf

    Dim results As String
    Dim filesInFolder As String()
    Dim fileInFolder As String
    Dim tablesInFile As String()
    Dim tableInFile As String

    results = _
      "Final values of variables:" & EOL & _
      "ExcelFile: " & Dts.Variables("ExcelFile").Value.ToString & EOL & _
      "ExcelFileExists: " & Dts.Variables("ExcelFileExists").Value.ToString & EOL & _
      "ExcelTable: " & Dts.Variables("ExcelTable").Value.ToString & EOL & _
      "ExcelTableExists: " & Dts.Variables("ExcelTableExists").Value.ToString & EOL & _
      "ExcelFolder: " & Dts.Variables("ExcelFolder").Value.ToString & EOL & _
      EOL

    results &= "Excel files in folder: " & EOL
    filesInFolder = DirectCast(Dts.Variables("ExcelFiles").Value, String())
    For Each fileInFolder In filesInFolder
      results &= " " & fileInFolder & EOL
    Next
    results &= EOL

    results &= "Excel tables in file: " & EOL
    tablesInFile = DirectCast(Dts.Variables("ExcelTables").Value, String())
    For Each tableInFile In tablesInFile
      results &= " " & tableInFile & EOL
    Next

    MessageBox.Show(results, "Results", MessageBoxButtons.OK, MessageBoxIcon.Information)

    Dts.TaskResult = ScriptResults.Success
  End Sub
End Class

public class ScriptMain
{
  public void Main()
        {
            const string EOL = "\r";

            string results;
            string[] filesInFolder;
            //string fileInFolder;
            string[] tablesInFile;
            //string tableInFile;

            results = "Final values of variables:" + EOL + "ExcelFile: " + Dts.Variables["ExcelFile"].Value.ToString() + EOL + "ExcelFileExists: " + Dts.Variables["ExcelFileExists"].Value.ToString() + EOL + "ExcelTable: " + Dts.Variables["ExcelTable"].Value.ToString() + EOL + "ExcelTableExists: " + Dts.Variables["ExcelTableExists"].Value.ToString() + EOL + "ExcelFolder: " + Dts.Variables["ExcelFolder"].Value.ToString() + EOL + EOL;

            results += "Excel files in folder: " + EOL;
            filesInFolder = (string[])(Dts.Variables["ExcelFiles"].Value);
            foreach (string fileInFolder in filesInFolder)
            {
                results += " " + fileInFolder + EOL;
            }
            results += EOL;

            results += "Excel tables in file: " + EOL;
            tablesInFile = (string[])(Dts.Variables["ExcelTables"].Value);
            foreach (string tableInFile in tablesInFile)
            {
                results += " " + tableInFile + EOL;
            }

            MessageBox.Show(results, "Results", MessageBoxButtons.OK, MessageBoxIcon.Information);

            Dts.TaskResult = (int)ScriptResults.Success;
        }
}
Ícone do Integration Services (pequeno) Fique atualizado com o Integration Services

Para obter os mais recentes downloads, artigos, exemplos e vídeos da Microsoft, bem como soluções selecionadas da comunidade, visite a página do Integration Services no MSDN ou TechNet:

Para receber uma notificação automática das atualizações, assine os feeds RSS disponíveis na página.