Condividi tramite

Utilizzo di file di Excel con l'attività Script

  • Articolo

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. Questi esempi possono essere facilmente modificati per utilizzare una delle altre origini dati basate su file supportate dal provider OLE DB di Microsoft Jet.

Configurazione di un pacchetto per testare gli esempi

Esempio 1: verificare l'esistenza di un file di Excel

Esempio 2: verificare l'esistenza di una tabella di Excel

Esempio 3: ottenere un elenco di file di Excel in una cartella

Esempio 4: ottenere un elenco di tabelle in un file di Excel

Visualizzazione dei risultati degli esempi

[!NOTA]

Se si desidera creare un'attività da riutilizzare più facilmente con più pacchetti, è possibile utilizzare il codice di questo esempio di attività Script come punto iniziale per un'attività personalizzata. Per ulteriori 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 utilizzate molte variabili del pacchetto e classi di .NET Framework.

Per configurare un pacchetto per l'utilizzo con gli esempi di questo argomento

  1. Creare un nuovo progetto di Integration Services in SQL Server Data Tools (SSDT) e aprire il pacchetto predefinito per la modifica.

  2. Variabili. Aprire la finestra Variabili e definire le variabili seguenti:

    • ExcelFile, di tipo String. Immettere il percorso completo e il nome del file di una cartella di lavoro di Excel esistente.

    • ExcelTable, di tipo String. Immettere il nome di un foglio di lavoro o di un intervallo denominato esistente nella cartella di lavoro specificata nel valore della variabile ExcelFile. Per questo valore viene applicata la distinzione tra maiuscole e minuscole.

    • ExcelFileExists, di tipo Boolean.

    • ExcelTableExists, di tipo Boolean.

    • ExcelFolder, di tipo String. Immettere il percorso completo di una cartella che contiene almeno una cartella di lavoro di Excel.

    • ExcelFiles, di tipo Object.

    • ExcelTables, di tipo Object.

  3. 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 del file system.

    • System.Data.OleDb, per aprire i file di Excel come origini dati.

  4. 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.

  5. Impostare il linguaggio di scripting predefinito per il componente script utilizzando l'opzione Linguaggio di scripting nella pagina Generale della finestra di dialogo Opzioni. Per ulteriori informazioni, vedere Pagina Generale.

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

  1. Aggiungere una nuova attività Script al pacchetto e impostarne il nome su ExcelFileExists.

  2. Nella scheda Script di Editor attività Script fare clic su ReadOnlyVariables, quindi immettere il valore della proprietà utilizzando uno dei metodi seguenti:

    • Digitare ExcelFile.

      -oppure-

    • Fare clic sul pulsante con i puntini di sospensione (…) accanto al campo della proprietà, quindi selezionare la variabile ExcelFile nella finestra di dialogo Seleziona variabili.

  3. Fare clic su ReadWriteVariables, quindi immettere il valore della proprietà utilizzando uno dei metodi seguenti:

    • Digitare ExcelFileExists.

      -oppure-

    • Fare clic sul pulsante con i puntini di sospensione (…) accanto al campo della proprietà, quindi selezionare la variabile ExcelFileExists nella finestra di dialogo Seleziona variabili.

  4. Fare clic su Modifica script per aprire l'editor di script.

  5. Aggiungere un'istruzione Imports per lo spazio dei nomi System.IO all'inizio del file script.

  6. 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

  1. Aggiungere una nuova attività Script al pacchetto e impostarne il nome su ExcelTableExists.

  2. Nella scheda Script di Editor attività Script fare clic su ReadOnlyVariables, quindi immettere il valore della proprietà utilizzando 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à, quindi selezionare le variabili ExcelTable e ExcelFile nella finestra di dialogo Seleziona variabili.

  3. Fare clic su ReadWriteVariables, quindi immettere il valore della proprietà utilizzando uno dei metodi seguenti:

    • Digitare ExcelTableExists.

      -oppure-

    • Fare clic sul pulsante con i puntini di sospensione (…) accanto al campo della proprietà, quindi selezionare la variabile ExcelTableExists nella finestra di dialogo Seleziona variabili.

  4. Fare clic su Modifica script per aprire l'editor di script.

  5. Aggiungere un riferimento all'assembly System.Xml nel progetto di script.

  6. Aggiungere le istruzioni Imports per gli spazi dei nomi System.IO e System.Data.OleDb all'inizio del file script.

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

        }
}

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

  1. Aggiungere una nuova attività Script al pacchetto e impostarne il nome su GetExcelFiles.

  2. Nella scheda Script di Editor attività Script fare clic su ReadOnlyVariables, quindi immettere il valore della proprietà utilizzando uno dei metodi seguenti:

    • Digitare ExcelFolder

      -oppure-

    • Fare clic sul pulsante con i puntini di sospensione (…) accanto al campo della proprietà, quindi selezionare la variabile ExcelFolder nella finestra di dialogo Seleziona variabili.

  3. Fare clic su ReadWriteVariables, quindi immettere il valore della proprietà utilizzando uno dei metodi seguenti:

    • Digitare ExcelFiles.

      -oppure-

    • Fare clic sul pulsante con i puntini di sospensione (…) accanto al campo della proprietà, quindi selezionare la variabile ExcelFiles nella finestra di dialogo Seleziona variabili.

  4. Fare clic su Modifica script per aprire l'editor di script.

  5. Aggiungere un'istruzione Imports per lo spazio dei nomi System.IO all'inizio del file script.

  6. Aggiungere il codice seguente.

Codice dell'esempio 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;
  }
}

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 ulteriori informazioni, vedere Esecuzione di un ciclo su file e tabelle di Excel utilizzando 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 lavoro 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

  1. Aggiungere una nuova attività Script al pacchetto e impostarne il nome su GetExcelTables.

  2. Nella scheda Script di Editor attività Script fare clic su ReadOnlyVariables, quindi immettere il valore della proprietà utilizzando uno dei metodi seguenti:

    • Digitare ExcelFile.

      -oppure-

    • Fare clic sul pulsante con i puntini di sospensione (…) accanto al campo della proprietà, quindi selezionare la variabile ExcelFile nella finestra di dialogo Seleziona variabili.

  3. Fare clic su ReadWriteVariables, quindi immettere il valore della proprietà utilizzando uno dei metodi seguenti:

    • Digitare ExcelTables.

      -oppure-

    • Fare clic sul pulsante con i puntini di sospensione (…) accanto al campo della proprietà, quindi selezionare la variabile ExcelTablesnella finestra di dialogo Seleziona variabili.

  4. Fare clic su Modifica script per aprire l'editor di script.

  5. Aggiungere un riferimento allo spazio dei nomi System.Xml nel progetto di script.

  6. Aggiungere un'istruzione Imports per lo spazio dei nomi System.Data.OleDb all'inizio del file script.

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

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 ulteriori informazioni, vedere Esecuzione di un ciclo su file e tabelle di Excel utilizzando 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

  1. Aggiungere una nuova attività Script al pacchetto e impostarne il nome su DisplayResults.

  2. 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.

  3. Aprire l'attività DisplayResults in Editor attività Script.

  4. Nella scheda Script fare clic su ReadOnlyVariables e utilizzare 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à, quindi selezionare le variabili nella finestra di dialogo Seleziona variabili.

  5. Fare clic su Modifica script per aprire l'editor di script.

  6. Aggiungere le istruzioni Imports per gli spazi dei nomi Microsoft.VisualBasic e System.Windows.Forms all'inizio del file script.

  7. Aggiungere il codice seguente.

  8. 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;
        }
}
Icona di Integration Services (piccola) Rimanere aggiornati con Integration Services

Per i download, gli articoli, gli esempi e i video Microsoft più recenti, oltre alle soluzioni selezionate dalla community, visitare la pagina Integration Services sul sito MSDN:


Per ricevere notifica automatica su questi aggiornamenti, sottoscrivere i feed RSS disponibili nella pagina.

Vedere anche

Attivitá

Esecuzione di un ciclo su file e tabelle di Excel utilizzando un contenitore Ciclo Foreach

Concetti

Gestione connessione Excel