Trabalhando com arquivos do Excel com a tarefa Script
Aplica-se a:
SQL Server SSIS Integration Runtime no Azure Data Factory
O Integration Services fornece 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 Microsoft Excel. 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.
Importante
Para obter informações detalhadas sobre como se conectar a arquivos do Excel, e sobre limitações e problemas conhecidos para carregar dados de ou para arquivos do Excel, consulte Carregar dados do ou para o Excel com o SSIS (SQL Server Integration Services).
Dica
Se desejar criar uma tarefa que possa 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
Crie um projeto do Integration Services novo em SQL Server Data Tools (SSDT) e abra o pacote padrão para editar.
Variáveis. Abra a janela Variáveis e defina as seguintes variáveis:
ExcelFile, do tipo Cadeia de caracteres. Digite o caminho completo e o nome do arquivo em uma pasta de trabalho do Excel existente.ExcelTable, do tipo Cadeia de caracteres. Digite o nome de uma planilha existente ou intervalo nomeado na pasta de trabalho nomeada no valor da variávelExcelFile. Esse valor diferencia maiúsculas de minúsculas.ExcelFileExists, do tipo Booliano.ExcelTableExists, do tipo Booliano.ExcelFolder, do tipo Cadeia de caracteres. Digite o caminho completo de uma pasta que contenha pelo menos uma pasta de trabalho do Excel.ExcelFiles, do tipo Objeto.ExcelTables, do tipo Objeto.
Instruções Imports. 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.
Referências. Os exemplos de código que leem informações de esquema de arquivos do Excel exigem uma referência adicional no projeto de script ao namespace System.Xml.
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 General Page.
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 booliano da variável ExcelFileExists para o resultado. Você pode usar esse valor booliano para ramificar no fluxo de trabalho do pacote.
Para configurar esse exemplo de tarefa Script
Adicione uma nova tarefa Script ao pacote e altere seu nome para ExcelFileExists.
No Editor da Tarefa Script, na guia Script, clique em ReadOnlyVariables e insira o valor da propriedade usando um dos seguintes métodos:
Digite ExcelFile.
- ou -
Clique no botão de reticências ( … ) ao lado do campo de propriedade e, na caixa de diálogo Selecionar variáveis, selecione a variável ExcelFile.
Clique em ReadWriteVariables e insira o valor da propriedade usando um dos seguintes métodos:
Digite ExcelFileExists.
- ou -
Clique no botão de reticências ( … ) ao lado do campo de propriedade e, na caixa de diálogo Selecionar variáveis, selecione a variável ExcelFileExists.
Clique em Editar Script para abrir o editor de scripts.
Adicione uma instrução Imports ao namespace System.IO na parte superior do arquivo de script.
Adicione o código seguinte:
Código do 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 booliano da variável ExcelTableExists para o resultado. Você pode usar esse valor booliano para ramificar no fluxo de trabalho do pacote.
Para configurar esse exemplo de tarefa Script
Adicione uma nova tarefa Script ao pacote e altere seu nome para ExcelTableExists.
No Editor da Tarefa Script, na guia Script, clique em ReadOnlyVariables e insira o valor da propriedade usando um dos seguintes métodos:
Digite ExcelTable e ExcelFile separados por vírgula.
- ou -
Clique no botão de reticências ( … ) ao lado do campo de propriedade e, na caixa de diálogo Selecionar variáveis, selecione as variáveis ExcelTable e ExcelFile.
Clique em ReadWriteVariables e insira o valor da propriedade usando um dos seguintes métodos:
Digite ExcelTableExists.
- ou -
Clique no botão de reticências ( … ) ao lado do campo de propriedade e, na caixa de diálogo Selecionar variáveis, selecione a variável ExcelTableExists.
Clique em Editar Script para abrir o editor de scripts.
Adicione uma referência ao assembly System.Xml no projeto de script.
Adicione instruções Imports aos namespaces System.IO e System.Data.OleDb na parte superior do arquivo de script.
Adicione o código seguinte:
Código do 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.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: 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
Adicione uma nova tarefa Script ao pacote e altere seu nome para GetExcelFiles.
Abra o Editor da Tarefa Script, na guia Script, clique em ReadOnlyVariables e insira o valor da propriedade usando um dos seguintes métodos:
Digite ExcelFolder
- ou -
Clique no botão de reticências ( … ) ao lado do campo de propriedade e, na caixa de diálogo Selecionar variáveis, selecione a variável ExcelFolder.
Clique em ReadWriteVariables e insira o valor da propriedade usando um dos seguintes métodos:
Digite ExcelFiles.
- ou -
Clique no botão de reticências ( … ) ao lado do campo de propriedade e, na caixa de diálogo Selecionar variáveis, selecione a variável ExcelFiles.
Clique em Editar Script para abrir o editor de scripts.
Adicione uma instrução Imports ao namespace System.IO na parte superior do arquivo de script.
Adicione o código seguinte:
Código do exemplo 3
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 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 Executar um loop por meio de arquivos e tabelas do Excel usando um contêiner do 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çã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
Adicione uma nova tarefa Script ao pacote e altere seu nome para GetExcelTables.
Abra o Editor da Tarefa Script, na guia Script, clique em ReadOnlyVariables e insira o valor da propriedade usando um dos seguintes métodos:
Digite ExcelFile.
- ou -
Clique no botão de reticências ( … ) ao lado do campo de propriedade e, na caixa de diálogo Selecionar variáveis, selecione a variável ExcelFile.
Clique em ReadWriteVariables e insira o valor da propriedade usando um dos seguintes métodos:
Digite ExcelTables.
- ou -
Clique no botão de reticências ( … ) ao lado do campo de propriedade e, na caixa de diálogo Selecionar variáveis, selecione a variável ExcelTables.
Clique em Editar Script para abrir o editor de scripts.
Adicione uma referência ao namespace System.Xml no projeto de script.
Adicione uma instrução Imports ao namespace System.Data.OleDb na parte superior do arquivo de script.
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.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 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 Executar um loop por meio de arquivos e tabelas do Excel usando um contêiner do 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
Adicione uma nova tarefa Script ao pacote e altere seu nome para DisplayResults.
Conecte cada uma das quatro tarefas Script de exemplo uma a outra, de forma que cada tarefa seja executada depois que a anterior for concluída com êxito e conecte a quarta tarefa de exemplo à tarefa DisplayResults.
Abra a tarefa DisplayResults no Editor da Tarefa Script.
Na guia Script, clique em ReadOnlyVariables e use um dos seguintes métodos para adicionar todas as sete variáveis listadas em Configurando um pacote para testar as amostras:
Digite o nome de cada variável separado por vírgulas.
- ou -
Clique no botão de reticências ( … ) ao lado do campo de propriedade e, na caixa de diálogo Selecionar variáveis, selecione as variáveis.
Clique em Editar Script para abrir o editor de scripts.
Adicione instruções Imports aos namespaces Microsoft.VisualBasic e System.Windows.Forms na parte superior do arquivo de script.
Adicione o código seguinte:
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;
}
}
Consulte Também
Carregar dados do ou para o Excel com o SSIS (SQL Server Integration Services)
Loop através de arquivos e tabelas do Excel por meio de um contêiner do Loop Foreach
Comentários
Em breve: Ao longo de 2024, eliminaremos os problemas do GitHub como o mecanismo de comentários para conteúdo e o substituiremos por um novo sistema de comentários. Para obter mais informações, consulte https://aka.ms/ContentUserFeedback.
Enviar e exibir comentários de