Uso di file di Excel con l'attività Script
Si applica a:
SQL Server Integration Runtime SSIS in Azure Data Factory
In Integration Services sono disponibili la gestione connessione, l'origine e la destinazione Excel per l'utilizzo di dati archiviati in fogli di calcolo nel formato di file di Microsoft Excel. Per le tecniche descritte in questo argomento si utilizza l'attività Script per ottenere informazioni sui database (file di cartelle di lavoro) e sulle tabelle (fogli di lavoro e intervalli denominati) di Excel disponibili.
Importante
Per informazioni dettagliate sulla connessione ai file di Excel e sulle limitazioni e i problemi noti per il caricamento di dati da o a file di Excel, vedere Caricare i dati da o in Excel con SQL Server Integration Services (SSIS).
Suggerimento
Se si desidera creare un'attività da riusare con più pacchetti, è possibile usare il codice di questo esempio di attività Script come punto iniziale per un'attività personalizzata. Per altre informazioni, vedere Sviluppo di un'attività personalizzata.
Configurazione di un pacchetto per testare gli esempi
È possibile configurare un singolo pacchetto per testare tutti gli esempi riportati in questo argomento. Negli esempi vengono usate molte variabili del pacchetto e classi di .NET Framework.
Per configurare un pacchetto per l'utilizzo con gli esempi di questo argomento
Creare un nuovo progetto di Integration Services in SQL Server Data Tools (SSDT) e aprire il pacchetto predefinito per la modifica.
Variabili. Aprire la finestra Variabili e definire le variabili seguenti:
ExcelFile, del tipo String. Immettere il percorso completo e il nome del file di una cartella di lavoro di Excel esistente.ExcelTable, del tipo String. Immettere il nome di un foglio di lavoro o di un intervallo denominato esistente nella cartella di lavoro specificata nel valore della variabileExcelFile. Per questo valore viene applicata la distinzione tra maiuscole e minuscole.ExcelFileExistsdel tipoBoolean.ExcelTableExistsdel tipoBoolean.ExcelFolder, del tipo String. Immettere il percorso completo di una cartella che contiene almeno una cartella di lavoro di Excel.ExcelFiles, del tipo Object.ExcelTables, del tipo Object.
Istruzioni Imports. Per la maggior parte degli esempi di codice, è necessario importare uno o entrambi i seguenti spazi dei nomi di .NET Framework all'inizio del file script:
System.IO, per le operazioni di file system.
System.Data.OleDb, per aprire i file di Excel come origini dati.
Riferimenti. Gli esempi di codice che leggono informazioni sullo schema da file di Excel richiedono un riferimento aggiuntivo allo spazio dei nomi System.Xml nel progetto di script.
Impostare il linguaggio di scripting predefinito per il componente Script usando l'opzione Linguaggio di scripting nella pagina Generale della finestra di dialogo Opzioni. Per ulteriori informazioni, vedere General Page.
Descrizione dell'esempio 1: verificare l'esistenza di un file di Excel
In questo esempio viene determinato se il file della cartella di lavoro di Excel specificato nella variabile ExcelFile esiste, quindi il valore booleano della variabile ExcelFileExists viene impostato sul risultato. È possibile utilizzare questo valore booleano per la diramazione nel flusso di lavoro del pacchetto.
Per configurare l'esempio di attività Script
Aggiungere una nuova attività Script al pacchetto e impostarne il nome su ExcelFileExists.
Nella scheda Script di Editor attività Script fare clic su ReadOnlyVariables e immettere il valore della proprietà usando uno dei metodi seguenti:
Digitare ExcelFile.
-oppure-
Fare clic sul pulsante con i puntini di sospensione ( ... ) accanto al campo della proprietà e selezionare la variabile ExcelFile nella finestra di dialogo Seleziona variabili.
Fare clic su ReadWriteVariables e immettere il valore della proprietà usando uno dei metodi seguenti:
Digitare ExcelFileExists.
-oppure-
Fare clic sul pulsante con i puntini di sospensione (...) accanto al campo della proprietà e selezionare la variabile ExcelFileExists nella finestra di dialogo Seleziona variabili.
Fare clic su Modifica script per aprire l'editor di script.
Aggiungere un'istruzione Imports per lo spazio dei nomi System.IO all'inizio del file di script.
Aggiungere il codice seguente.
Codice dell'esempio 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;
}
}
Descrizione dell'esempio 2: verificare l'esistenza di una tabella di Excel
In questo esempio viene determinato se il foglio di lavoro o l'intervallo denominato di Excel specificato nella variabile ExcelTable esiste nel file della cartella di lavoro di Excel specificato nella variabile ExcelFile, quindi il valore booleano della variabile ExcelTableExists viene impostato sul risultato. È possibile utilizzare questo valore booleano per la diramazione nel flusso di lavoro del pacchetto.
Per configurare l'esempio di attività Script
Aggiungere una nuova attività Script al pacchetto e impostarne il nome su ExcelTableExists.
Nella scheda Script di Editor attività Script fare clic su ReadOnlyVariables e immettere il valore della proprietà usando uno dei metodi seguenti:
Digitare ExcelTable e ExcelFile separati da virgole.
-oppure-
Fare clic sul pulsante con i puntini di sospensione (...) accanto al campo della proprietà e selezionare le variabili ExcelTable e ExcelFile nella finestra di dialogo Seleziona variabili.
Fare clic su ReadWriteVariables e immettere il valore della proprietà usando uno dei metodi seguenti:
Digitare ExcelTableExists.
-oppure-
Fare clic sul pulsante con i puntini di sospensione (...) accanto al campo della proprietà e selezionare la variabile ExcelTableExists nella finestra di dialogo Seleziona variabili.
Fare clic su Modifica script per aprire l'editor di script.
Aggiungere un riferimento all'assembly System.Xml nel progetto di script.
Aggiungere istruzioni Imports per gli spazi dei nomi System.IO e System.Data.OleDb all'inizio del file di script.
Aggiungere il codice seguente.
Codice dell'esempio 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;
}
}
Descrizione dell'esempio 3: ottenere un elenco di file di Excel in una cartella
In questo esempio l'elenco dei file di Excel trovati nella cartella specificata nel valore della variabile ExcelFolder viene inserito in una matrice, che viene quindi copiata nella variabile ExcelFiles. È possibile utilizzare l'enumeratore Foreach From Variable per scorrere i file nella matrice.
Per configurare l'esempio di attività Script
Aggiungere una nuova attività Script al pacchetto e impostarne il nome su GetExcelFiles.
Nella scheda Script di Editor attività Script fare clic su ReadOnlyVariables e immettere il valore della proprietà usando uno dei metodi seguenti:
Digitare ExcelFolder
-oppure-
Fare clic sul pulsante con i puntini di sospensione ( ... ) accanto al campo della proprietà e selezionare la variabile ExcelFolder nella finestra di dialogo Seleziona variabili.
Fare clic su ReadWriteVariables e immettere il valore della proprietà usando uno dei metodi seguenti:
Digitare ExcelFiles.
-oppure-
Fare clic sul pulsante con i puntini di sospensione ( ... ) accanto al campo della proprietà e selezionare la variabile ExcelFiles nella finestra di dialogo Seleziona variabili.
Fare clic su Modifica script per aprire l'editor di script.
Aggiungere un'istruzione Imports per lo spazio dei nomi System.IO all'inizio del file di script.
Aggiungere il codice seguente.
Codice dell'esempio 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;
}
}
Soluzione alternativa
Anziché utilizzare un'attività Script per raccogliere un elenco di file di Excel in una matrice, è anche possibile utilizzare l'enumeratore ForEach File per scorrere tutti i file di Excel presenti in una cartella. Per altre informazioni, vedere Esecuzione di un ciclo su file e tabelle di Excel usando un contenitore Ciclo Foreach.
Descrizione dell'esempio 4: ottenere un elenco di tabelle in un file di Excel
In questo esempio l'elenco dei fogli di lavoro e degli intervalli denominati trovati nel file della cartella di lavoro di Excel specificata dal valore della variabile ExcelFile viene inserito in una matrice, che viene quindi copiata nella variabile ExcelTables. È possibile utilizzare l'enumeratore Foreach From Variable per scorrere le tabelle nella matrice.
Nota
Nell'elenco di tabelle di una cartella di Excel sono inclusi sia i fogli di lavoro (con suffisso $) sia gli intervalli denominati. Se è necessario applicare un filtro all'elenco per individuare solo fogli di lavoro o solo intervalli denominati, può essere necessario scrivere codice aggiuntivo a tale scopo.
Per configurare l'esempio di attività Script
Aggiungere una nuova attività Script al pacchetto e impostarne il nome su GetExcelTables.
Nella scheda Script di Editor attività Script fare clic su ReadOnlyVariables e immettere il valore della proprietà usando uno dei metodi seguenti:
Digitare ExcelFile.
-oppure-
Fare clic sul pulsante con i puntini di sospensione ( ... ) accanto al campo della proprietà e selezionare la variabile ExcelFile nella finestra di dialogo Seleziona variabili.
Fare clic su ReadWriteVariables e immettere il valore della proprietà usando uno dei metodi seguenti:
Digitare ExcelTables.
-oppure-
Fare clic sul pulsante con i puntini di sospensione ( ... ) accanto al campo della proprietà e selezionare la variabile ExcelTables nella finestra di dialogo Seleziona variabili.
Fare clic su Modifica script per aprire l'editor di script.
Aggiungere un riferimento allo spazio dei nomi System.Xml nel progetto di script.
Aggiungere un'istruzione Imports per lo spazio dei nomi System.Data.OleDb all'inizio del file di script.
Aggiungere il codice seguente.
Codice dell'esempio 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.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;
}
}
Soluzione alternativa
Anziché utilizzare un'attività Script per raccogliere un elenco delle tabelle di Excel in una matrice, è anche possibile utilizzare ForEach ADO.NET Schema Rowset Enumerator per scorrere tutte le tabelle, ovvero fogli di lavoro e intervalli denominati, nel file di una cartella di lavoro di Excel. Per altre informazioni, vedere Esecuzione di un ciclo su file e tabelle di Excel usando un contenitore Ciclo Foreach.
Visualizzazione dei risultati degli esempi
Se tutti gli esempi di questo argomento sono stati configurati nello stesso pacchetto, è possibile connettere tutte le attività Script a un'attività Script aggiuntiva che visualizza l'output di tutti gli esempi.
Per configurare un'attività Script per visualizzare l'output degli esempi di questo argomento
Aggiungere una nuova attività Script al pacchetto e impostarne il nome su DisplayResults.
Connettere i quattro esempi di attività Script l'uno all'altro, in modo che ogni attività venga eseguita al completamento di quella precedente, quindi connettere il quarto esempio di attività all'attività DisplayResults.
Aprire l'attività DisplayResults in Editor attività Script.
Nella scheda Script fare clic su ReadOnlyVariables e usare uno dei metodi seguenti per aggiungere tutte le sette variabili elencate in Configurazione di un pacchetto per testare gli esempi:
Digitare i nomi di ogni variabile separati da virgole.
-oppure-
Fare clic sul pulsante con i puntini di sospensione ( ... ) accanto al campo della proprietà e selezionare le variabili nella finestra di dialogo Seleziona variabili.
Fare clic su Modifica script per aprire l'editor di script.
Aggiungere istruzioni Imports per gli spazi dei nomi Microsoft.VisualBasic e System.Windows.Forms all'inizio del file di script.
Aggiungere il codice seguente.
Eseguire il pacchetto ed esaminare i risultati visualizzati in una finestra di messaggio.
Codice per visualizzare i risultati
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;
}
}
Vedere anche
Caricare dati da o in Excel con SQL Server Integration Services (SSIS)
Esecuzione di un ciclo su file e tabelle di Excel usando un contenitore Ciclo Foreach
Commenti e suggerimenti
Presto disponibile: Nel corso del 2024 verranno gradualmente disattivati i problemi di GitHub come meccanismo di feedback per il contenuto e ciò verrà sostituito con un nuovo sistema di feedback. Per altre informazioni, vedere https://aka.ms/ContentUserFeedback.
Invia e visualizza il feedback per