Trabajar con archivos de Excel con la tarea Script
Integration Services proporciona el administrador de conexiones de Excel, el origen de Excel y el destino de Excel para trabajar con datos almacenados en hojas de cálculo en el formato de archivo de Microsoft Excel . Las técnicas descritas en este tema usan la tarea Script para obtener información acerca de las bases de datos de Excel disponibles (archivos de libro) y tablas (hojas de cálculo y rangos con nombre). Estos ejemplos se pueden modificar con facilidad para trabajar con cualquiera de los demás orígenes de datos basados en archivos que admite el proveedor OLE DB para Microsoft Jet.
Configurar un paquete para probar los ejemplos
Ejemplo 1: comprobar si existe un archivo de Excel
Ejemplo 2: comprobar si existe una tabla de Excel
Ejemplo 3: obtener una lista de archivos de Excel en una carpeta
Ejemplo 4: obtener una lista de tablas en un archivo de Excel
Mostrar los resultados de los ejemplos
[!NOTA]
Si desea crear una tarea que pueda reutilizar más fácilmente en varios paquetes, considere la posibilidad de utilizar el código de este ejemplo de tarea Script como punto inicial de una tarea personalizada. Para obtener más información, vea Desarrollar una tarea personalizada.
Configurar un paquete para probar los ejemplos
Puede configurar un paquete único para probar todos los ejemplos de este tema. En los ejemplos se usan muchas de las mismas variables de paquete y las mismas clases de .NET Framework.
Para configurar un paquete y utilizarlo con los ejemplos en este tema
Cree un nuevo proyecto de Integration Services en SQL Server Data Tools (SSDT) y abra el paquete predeterminado para editar.
Variables. Abra la ventana Variables y defina las variables siguientes:
ExcelFile, de tipo String. Escriba la ruta de acceso completa y nombre de archivo a un libro de Excel existente.
ExcelTable, de tipo String. Escriba el nombre de una hoja de cálculo existente o rango con nombre en el libro denominado en el valor de la variable ExcelFile. Este valor distingue mayúsculas de minúsculas.
ExcelFileExists, de tipo Boolean.
ExcelTableExists, de tipo Boolean.
ExcelFolder, de tipo String. Escriba la ruta de acceso completa de una carpeta que contiene por lo menos un libro de Excel.
ExcelFiles, de tipo Object.
ExcelTables, de tipo Object.
instrucciones Imports. La mayoría de los ejemplos de código le exigen que importe uno o ambos de los espacios de nombres de .NET Framework siguientes en la parte superior de su archivo de script:
System.IO, para operaciones del sistema de archivos.
System.Data.OleDb, para abrir los archivos de Excel como orígenes de datos.
Referencias. Los ejemplos de código que leen información de esquema de los archivos de Excel requieren una referencia adicional en el proyecto de script al espacio de nombres System.Xml.
Establezca el lenguaje de scripting predeterminado para el componente de script mediante la opción Lenguaje de scripting de la página General del cuadro de diálogo Opciones. Para obtener más información, vea Página General.
Descripción de ejemplo 1: comprobar si existe un archivo de Excel
En este ejemplo se determina si existe el archivo de libro de Excel especificado en la variable ExcelFile y, a continuación, se establece el valor booleano de la variable ExcelFileExists en el resultado. Puede usar este valor booleano para la bifurcación en el flujo de trabajo del paquete.
Para configurar este ejemplo de tarea Script
Agregue una nueva tarea Script al paquete y cambie su nombre a ExcelFileExists.
En el Editor de la tarea Script, en la pestaña Script, haga clic en ReadOnlyVariables y escriba el valor de propiedad mediante uno de los métodos siguientes:
Escriba ExcelFile.
O bien
Haga clic en el botón de puntos suspensivos (...) que aparece al lado del campo de propiedades y, en el cuadro de diálogo Seleccionar variables, seleccione la variable ExcelFile.
Haga clic en ReadWriteVariables y escriba el valor de propiedad mediante uno de los métodos siguientes:
Escriba ExcelFileExists.
O bien
Haga clic en el botón de puntos suspensivos (...) que aparece al lado del campo de propiedades y, en el cuadro de diálogo Seleccionar variables, seleccione la variable ExcelFileExists.
Haga clic en Modificar script para abrir el editor de script.
Agregue una instrucción Imports para el espacio de nombres System.IO en la parte superior del archivo de script.
Agregue el código siguiente:
Código de ejemplo 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;
}
}
Descripción de ejemplo 2: comprobar si existe una tabla de Excel
En este ejemplo se determina si existe la hoja de cálculo de Excel o el rango con nombre especificado en la variable ExcelTable en el archivo de libro de Excel especificado en la variable ExcelFile y, a continuación, se establece el valor booleano de la variable ExcelTableExists en el resultado. Puede usar este valor booleano para la bifurcación en el flujo de trabajo del paquete.
Para configurar este ejemplo de tarea Script
Agregue una nueva tarea Script al paquete y cambie su nombre a ExcelTableExists.
En el Editor de la tarea Script, en la pestaña Script, haga clic en ReadOnlyVariables y escriba el valor de propiedad mediante uno de los métodos siguientes:
Escriba ExcelTable y ExcelFile separado por comas.
O bien
Haga clic en el botón de puntos suspensivos (...) que aparece al lado del campo de propiedades y, en el cuadro de diálogo Seleccionar variables, seleccione las variables ExcelTable y ExcelFile.
Haga clic en ReadWriteVariables y escriba el valor de propiedad mediante uno de los métodos siguientes:
Escriba ExcelTableExists.
O bien
Haga clic en el botón de puntos suspensivos (...) que aparece al lado del campo de propiedades y, en el cuadro de diálogo Seleccionar variables, seleccione la variable ExcelTableExists.
Haga clic en Modificar script para abrir el editor de script.
Agregue una referencia al ensamblado System.Xml en el proyecto de script.
Agregue instrucciones Imports para los espacios de nombres System.IO y System.Data.OleDb en la parte superior del archivo de script.
Agregue el código siguiente:
Código de ejemplo 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;
}
}
Descripción de ejemplo 3: obtener una lista de archivos de Excel en una carpeta
En este ejemplo se llena una matriz con la lista de archivos de Excel ubicados en la carpeta especificada del valor de la variable ExcelFolder y, a continuación, se copia la matriz en la variable ExcelFiles. Puede usar el enumerador de variable para Foreach para iterar sobre los archivos en la matriz.
Para configurar este ejemplo de tarea Script
Agregue una nueva tarea Script al paquete y cambie su nombre a GetExcelFiles.
Abra el Editor de la tarea Script, en la pestaña Script, haga clic en ReadOnlyVariables y escriba el valor de propiedad mediante uno de los métodos siguientes:
Escriba ExcelFolder
O bien
Haga clic en el botón de puntos suspensivos (...) que aparece al lado del campo de propiedades y, en el cuadro de diálogo Seleccionar variables, seleccione la variable ExcelFolder.
Haga clic en ReadWriteVariables y escriba el valor de propiedad mediante uno de los métodos siguientes:
Escriba ExcelFiles.
O bien
Haga clic en el botón de puntos suspensivos (...) que aparece al lado del campo de propiedades y, en el cuadro de diálogo Seleccionar variables, seleccione la variable ExcelFiles.
Haga clic en Modificar script para abrir el editor de script.
Agregue una instrucción Imports para el espacio de nombres System.IO en la parte superior del archivo de script.
Agregue el código siguiente:
Código de ejemplo 3
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;
}
}
Solución alternativa
En lugar de usar una tarea Script para recopilar una lista de archivos de Excel en una matriz, puede usar también el enumerador de archivos para ForEach para iterar sobre todos los archivos de Excel de una carpeta. Para obtener más información, vea Crear bucles entre archivos y tablas de Excel usando un contenedor de bucles Foreach.
Descripción de ejemplo 4: obtener una lista de tablas en un archivo de Excel
En este ejemplo se llena una matriz con la lista de hojas de cálculo y rangos con nombre ubicados en el archivo de libro de Excel especificado por el valor de la variable ExcelFile y, a continuación, se copia la matriz en la variable ExcelTables. Puede usar el enumerador de variable para Foreach para iterar sobre las tablas en la matriz.
[!NOTA]
La lista de tablas de un libro de Excel incluye tanto hojas (que tienen el sufijo $) como rangos con nombre. Si tiene que filtrar la lista para obtener solo las hojas o solo los rangos con nombre, agregue código adicional para este fin.
Para configurar este ejemplo de tarea Script
Agregue una nueva tarea Script al paquete y cambie su nombre a GetExcelTables.
Abra el Editor de la tarea Script, en la pestaña Script, haga clic en ReadOnlyVariables y escriba el valor de propiedad mediante uno de los métodos siguientes:
Escriba ExcelFile.
O bien
Haga clic en el botón de puntos suspensivos (...) que aparece al lado del campo de propiedades y, en el cuadro de diálogo Seleccionar variables, seleccione la variable ExcelFile.
Haga clic en ReadWriteVariables y escriba el valor de propiedad mediante uno de los métodos siguientes:
Escriba ExcelTables.
O bien
Haga clic en el botón de puntos suspensivos (...) que aparece al lado del campo de propiedades y, en el cuadro de diálogo Seleccionar variables, seleccione la variableExcelTables.
Haga clic en Modificar script para abrir el editor de script.
Agregue una referencia al espacio de nombres System.Xml en el proyecto de script.
Agregue una instrucción Imports para el espacio de nombres System.Data.OleDb en la parte superior del archivo de script.
Agregue el código siguiente:
Código de ejemplo 4
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;
}
}
Solución alternativa
En lugar de usar una tarea Script para recopilar una lista de tablas Excel en una matriz, puede usar también el enumerador de conjunto de filas de esquema para Foreach de ADO.NET para iterar sobre todas las tablas (es decir, hojas de cálculo y rangos con nombre) en un archivo de libro de Excel. Para obtener más información, vea Crear bucles entre archivos y tablas de Excel usando un contenedor de bucles Foreach.
Mostrar los resultados de los ejemplos
Si ha configurado cada uno de los ejemplos de este tema en el mismo paquete, puede conectar todas las tareas Script a una tarea Script adicional que muestra la salida de todos los ejemplos.
Para configurar una tarea Script para mostrar la salida de los ejemplos de este tema
Agregue una nueva tarea Script al paquete y cambie su nombre a DisplayResults.
Conecte entre sí cada una de las tareas Script de los cuatro ejemplos, de manera que cada tarea se ejecute después de que la tarea anterior se complete correctamente, y conecte la tarea del ejemplo cuatro a la tarea DisplayResults.
Abra la tarea DisplayResults del Editor de la tarea Script.
En la pestaña Script, haga clic en ReadOnlyVariables y use uno de los métodos siguientes para agregar las siete variables enumeradas en Configurar un paquete para probar los ejemplos:
Escriba el nombre de cada variable separado por comas.
O bien
Haga clic en el botón de puntos suspensivos (...) que aparece al lado del campo de propiedades y, en el cuadro de diálogo Seleccionar variables, seleccione las variables.
Haga clic en Modificar script para abrir el editor de script.
Agregue instrucciones Imports para los espacios de nombres Microsoft.VisualBasic y System.Windows.Forms en la parte superior del archivo de script.
Agregue el código siguiente:
Ejecute el paquete y examine los resultados que aparecen en un cuadro de mensaje.
Código para mostrar los 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;
}
}
|
.gif "Icono de Integration Services (pequeño)")
Manténgase al día con Integration ServicesVea también
Tasks
Crear bucles entre archivos y tablas de Excel usando un contenedor de bucles Foreach