Trabalhando com arquivos do Excel com a tarefa de script

Aplica-se a: SQL Server SSIS Integration Runtime em Azure Data Factory

Os Serviços de Integração fornecem o gestor de conexões Excel, a fonte Excel e o destino Excel para trabalhar com dados armazenados em folhas de cálculo no formato de ficheiro Microsoft Excel. As técnicas descritas neste tópico utilizam a tarefa Script para obter informações sobre bases de dados Excel disponíveis (ficheiros de cadernos de exercícios) e tabelas (folhas de cálculo e intervalos nomeados).

Configuração de um Pacote para Testar as Amostras

Pode configurar um único pacote para testar todas as amostras deste tópico. Os exemplos utilizam muitas das mesmas variáveis de pacote e as mesmas classes do .NET Framework.

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

1. Crie um novo projeto de Serviços de Integração no SQL Server Data Tools (SSDT) e abra o pacote predefinido para edição.

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

   • ExcelFile, do tipo String. Introduza o caminho completo e o nome do ficheiro para um livro de exercícios Excel existente.

   • ExcelTable, do tipo String. Insira o nome de uma folha de exercícios existente ou de um intervalo nomeado no livro de exercícios com o valor da ExcelFile variável. Este valor faz distinção entre maiúsculas e minúsculas.

   • ExcelFileExists, do tipo Booleano.

   • ExcelTableExists, do tipo Booleano.

   • ExcelFolder, do tipo String. Introduza o percurso completo de uma pasta que contém pelo menos um livro de exercícios Excel.

   • ExcelFiles, do tipo Objeto.

   • ExcelTables, do tipo Objeto.

3. Extratos de importação. A maioria dos exemplos de código exige que importe um ou ambos os seguintes namespaces do .NET Framework no topo do seu ficheiro de script:

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

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

4. Referências. Os exemplos de código que leem informação de esquema a partir de ficheiros Excel requerem uma referência adicional no projeto de script ao namespace System.Xml.

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

Exemplo 1 Descrição: Verifique se existe um ficheiro Excel

Este exemplo determina se o ficheiro do livro de exercícios Excel especificado na ExcelFile variável existe, e depois define o valor booleano da ExcelFileExists variável ao resultado. Pode usar este valor booleano para ramificação no fluxo de trabalho do pacote.

Para configurar este exemplo de Tarefa de Script

1. Adicione uma nova tarefa Script ao pacote e mude-lhe o nome para ExcelFileExists.

2. No Editor de Tarefas de Script, no separador Script , clique em ReadOnlyVariables e introduza o valor da propriedade usando um dos seguintes métodos:

   • Escreve ExcelFile.

     -ou-

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

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

   • Escreva ExcelFileExists.

     -ou-

   • Clique no botão de elipse (...) ao lado do campo de propriedades e, na caixa de diálogo Selecionar variáveis , selecione a variável ExcelFileExists .

4. Clica em Editar Script para abrir o editor de scripts.

5. Adicione uma instrução Imports para o espaço de nomes System.IO no topo do ficheiro de script.

6. Adicione o seguinte código.

Exemplo 1 Código

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;  
  }  
}  

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

Este exemplo determina se a folha de cálculo Excel ou o intervalo nomeado especificado na ExcelTable variável existe no ficheiro do livro de trabalho Excel especificado na ExcelFile variável, e depois define o valor booleano da ExcelTableExists variável para o resultado. Pode usar este valor booleano para ramificação no fluxo de trabalho do pacote.

Para configurar este exemplo de Tarefa de Script

1. Adicione uma nova tarefa Script ao pacote e mude-lhe o nome para ExcelTableExists.

2. No Editor de Tarefas de Script, no separador Script , clique em ReadOnlyVariables e introduza o valor da propriedade usando um dos seguintes métodos:

   • Digite ExcelTable e ExcelFile separados por vírgulas.

     -ou-

   • Clique no botão de reticência (...) ao lado do campo de propriedades e, na caixa de diálogo Selecionar variáveis , selecione as variáveis ExcelTable e ExcelFile .

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

   • Escreva ExcelTableExists.

     -ou-

   • Clique no botão de reticência (...) ao lado do campo de propriedades e, na caixa de diálogo Selecionar variáveis , selecione a variável ExcelTableExists .

4. Clica em Editar Script para abrir o editor de scripts.

5. Adicione uma referência à assembly System.XML no projeto de script.

6. Adicionar instruções Imports para os espaços de nomes System.IO e System.Data.OleDb no topo do ficheiro de script.

7. Adicione o seguinte código.

Exemplo 2 Código

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.ACE.OLEDB.12.0;" & _  
        "Data Source=" & fileToTest & _  
        ";Extended Properties=Excel 12.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.ACE.OLEDB.12.0;" +  
                "Data Source=" + fileToTest + ";Extended Properties=Excel 12.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: Obtenha uma lista de ficheiros Excel numa pasta

Este exemplo preenche um array com a lista de ficheiros Excel encontrados na pasta especificada no valor da ExcelFolder variável, e depois copia o array para a ExcelFiles variável. Podes usar o Foreach from Variable enumerator para iterar sobre os ficheiros no array.

Para configurar este exemplo de Tarefa de Script

1. Adicione uma nova tarefa Script ao pacote e mude-lhe o nome para GetExcelFiles.

2. Abra o Editor de Tarefas de Script, no separador Script , clique em ReadOnlyVariables e insira o valor da propriedade usando um dos seguintes métodos:

   • Escreva ExcelFolder

     -ou-

   • Clique no botão de reticência (...) ao lado do campo de propriedades e, na caixa de diálogo Selecionar variáveis , selecione a variável ExcelPasta.

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

   • Digite ExcelFiles.

     -ou-

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

4. Clica em Editar Script para abrir o editor de scripts.

5. Adicione uma instrução Imports para o espaço de nomes System.IO no topo do ficheiro de script.

6. Adicione o seguinte código.

Exemplo 3 Código

Public Class ScriptMain  
  Public Sub Main()  
    Const FILE_PATTERN As String = "*.xlsx"  
  
    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 = "*.xlsx";  
  
    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 alternativa

Em vez de usar uma tarefa Script para reunir uma lista de ficheiros Excel num array, também pode usar o enumerador ForEach para iterar sobre todos os ficheiros Excel numa pasta. Para mais informações, consulte Loop through Excel Files and Tables using a Foreach Loop Container.

Exemplo 4 Descrição: Obter uma lista de tabelas num ficheiro Excel

Este exemplo preenche um array com a lista de folhas de cálculo e intervalos nomeados encontrados no ficheiro do livro de exercícios Excel especificado pelo valor da ExcelFile variável, e depois copia o array para a ExcelTables variável. Pode usar o Foreach do Variable Enumerator para iterar sobre as tabelas no array.

Para configurar este exemplo de Tarefa de Script

1. Adicione uma nova tarefa Script ao pacote e mude-lhe o nome para GetExcelTables.

2. Abra o Editor de Tarefas de Script, no separador Script , clique em ReadOnlyVariables e insira o valor da propriedade usando um dos seguintes métodos:

   • Escreve ExcelFile.

     -ou-

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

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

   • Digite ExcelTables.

     -ou-

   • Clique no botão de reticência (...) ao lado do campo de propriedades e, na caixa de diálogo Selecionar variáveis , selecione a variável ExcelTables.

4. Clica em Editar Script para abrir o editor de scripts.

5. Adicione uma referência ao namespace System.XML no projeto de script.

6. Adicione uma instrução Imports para o espaço de nomes System.Data.OleDb no topo do ficheiro de script.

7. Adicione o seguinte código.

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.ACE.OLEDB.12.0;" & _  
        "Data Source=" & excelFile & _  
        ";Extended Properties=Excel 12.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.ACE.OLEDB.12.0;" +  
                "Data Source=" + excelFile + ";Extended Properties=Excel 12.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 alternativa

Em vez de usar uma tarefa Script para reunir uma lista de tabelas Excel num array, também pode usar o ForEach ADO.NET Schema Rowset Enumerator para iterar sobre todas as tabelas (ou seja, folhas de cálculo e intervalos nomeados) num ficheiro de livro de trabalho Excel. Para mais informações, consulte Loop through Excel Files and Tables using a Foreach Loop Container.

Exibição dos resultados das amostras

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

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

1. Adicione uma nova tarefa Script ao pacote e mude-lhe o nome para DisplayResults.

2. Ligue cada uma das quatro tarefas Script de exemplo entre si, de modo a que cada tarefa seja executada após a conclusão bem-sucedida da tarefa anterior, e ligue a quarta tarefa de exemplo à tarefa DisplayResults .

3. Abra a tarefa DisplayResults no Editor de Tarefas de Script.

4. No separador Script , clique em ReadOnlyVariables e utilize um dos seguintes métodos para adicionar as sete variáveis listadas em Configurar um Pacote para Testar os Exemplos:

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

     -ou-

   • Clique no botão de reticência (...) ao lado do campo de propriedade e, na caixa de diálogo Selecionar variáveis , selecione as variáveis.

5. Clica em Editar Script para abrir o editor de scripts.

6. Adicionar extrações de importação para a Microsoft. VisualBasic e System.Windows. Namespaces de formulários no topo do ficheiro de script.

7. Adicione o seguinte código.

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

Código para Exibir os 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;  
        }  
}  

Conteúdo relacionado

• Importar dados do Excel ou exportar dados para o Excel com o SQL Server Integration Services (SSIS)
• Percorrer arquivos e tabelas do Excel com um contêiner de loop Foreach