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

Articolo
05/07/2013

In questo argomento vengono illustrate le procedure per l'esecuzione di un ciclo sulle cartelle di lavoro di Excel archiviate in una directory oppure sulle tabelle incluse in una cartella di Excel mediante il contenitore Ciclo Foreach con l'enumeratore appropriato.

Per eseguire un ciclo su un gruppo di file di Excel mediante Foreach File Enumerator

Creare una variabile stringa che riceverà il percorso e il nome del file di Excel correnti a ogni iterazione del ciclo. Per evitare problemi di convalida, assegnare un percorso e un nome file di Excel validi come valore iniziale della variabile. Nell'espressione di esempio indicata di seguito in questa procedura viene utilizzata una variabile denominata ExcelFile.
Facoltativamente, creare un'altra variabile stringa che conterrà il valore dell'argomento Proprietà estese della stringa di connessione a Excel. Tale argomento contiene una serie di valori che specificano la versione di Excel e determinano se la prima riga contiene i nomi delle colonne, nonché se viene utilizzata la modalità di importazione. Nell'espressione di esempio riportata di seguito in questa procedura viene utilizzata una variabile denominata ExtProperties, con un valore iniziale "Excel 8.0;HDR=Yes".

Se non si utilizza una variabile per l'argomento Proprietà estese, è necessario aggiungerla manualmente all'espressione contenente la stringa di connessione.
Aggiungere un contenitore Ciclo Foreach alla scheda Flusso di controllo. Per informazioni sulla configurazione del contenitore Ciclo Foreach, vedere Configurazione di un contenitore Ciclo Foreach.
Nella pagina Raccolta dell'Editor ciclo Foreach selezionare Foreach File Enumerator, specificare la directory in cui si trovano le cartelle di lavoro di Excel e il filtro file (in genere *.xls).
Nella pagina Mapping variabili eseguire il mapping dell'indice 0 a una variabile stringa definita dall'utente che riceverà il percorso e il nome del file di Excel corrente a ogni iterazione del ciclo. Nell'espressione di esempio indicata di seguito in questa procedura viene utilizzata una variabile denominata ExcelFile.
Chiudere l'Editor ciclo Foreach.

Aggiungere una gestione connessione Excel al pacchetto, come descritto in Aggiunta, eliminazione o condivisione di una gestione connessione in un pacchetto. Selezionare un file di una cartella di lavoro di Excel esistente per la connessione, per evitare errori di convalida.

Importante
Per evitare errori di convalida durante la configurazione delle attività e dei componenti dei flussi di dati che utilizzano questa gestione connessione Excel, selezionare una cartella di lavoro di Excel esistente nella finestra di dialogo Editor gestione connessione Excel. Questa cartella di lavoro non verrà utilizzata dalla gestione connessione in fase di esecuzione dopo aver configurato un'espressione per la proprietà ConnectionString, come descritto nella procedura seguente. Dopo aver creato e configurato il pacchetto, è possibile cancellare il valore della proprietà ConnectionString nella finestra Proprietà. Se, tuttavia, si cancella questo valore, la proprietà relativa alla stringa di connessione della gestione connessione Excel non sarà più valida fino a quando non viene eseguito il ciclo Foreach. Per evitare errori di convalida, è pertanto necessario impostare la proprietà DelayValidation su True nelle attività in cui viene utilizzata la gestione connessione o nel pacchetto. È inoltre necessario utilizzare il valore predefinito False per la proprietà RetainSameConnection della gestione connessione Excel. Se si modifica questo valore in True, ogni iterazione del ciclo continuerà ad aprire la prima cartella di lavoro di Excel.

Per evitare errori di convalida durante la configurazione delle attività e dei componenti dei flussi di dati che utilizzano questa gestione connessione Excel, selezionare una cartella di lavoro di Excel esistente nella finestra di dialogo Editor gestione connessione Excel. Questa cartella di lavoro non verrà utilizzata dalla gestione connessione in fase di esecuzione dopo aver configurato un'espressione per la proprietà ConnectionString, come descritto nella procedura seguente. Dopo aver creato e configurato il pacchetto, è possibile cancellare il valore della proprietà ConnectionString nella finestra Proprietà. Se, tuttavia, si cancella questo valore, la proprietà relativa alla stringa di connessione della gestione connessione Excel non sarà più valida fino a quando non viene eseguito il ciclo Foreach. Per evitare errori di convalida, è pertanto necessario impostare la proprietà DelayValidation su True nelle attività in cui viene utilizzata la gestione connessione o nel pacchetto.

È inoltre necessario utilizzare il valore predefinito False per la proprietà RetainSameConnection della gestione connessione Excel. Se si modifica questo valore in True, ogni iterazione del ciclo continuerà ad aprire la prima cartella di lavoro di Excel.

Selezionare la nuova gestione connessione Excel, fare clic sulla proprietà Espressioni nella finestra Proprietà e quindi fare clic sul pulsante con i puntini di sospensione (...).
Nell'Editor espressioni di proprietà selezionare la proprietà ConnectionString e quindi fare clic sul pulsante con i puntini di sospensione (...).
In Generatore di espressioni immettere l'espressione seguente:
```
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +  @[User::ExcelFile] + ";Extended Properties=\"" + @[User::ExtProperties] + "\""
```
Si noti l'utilizzo del carattere di escape "\" per le virgolette interne necessarie per il valore dell'argomento Extended Properties.

L'argomento Proprietà estese non è facoltativo. Se non si utilizza una variabile per contenere il relativo valore, è necessario aggiungerla manualmente all'espressione, come indicato nell'esempio seguente per un file di Excel 2003:
```
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +  @[User::ExcelFile] + ";Extended Properties=Excel 8.0"
```
Creare attività nel contenitore Ciclo Foreach mediante le quali viene utilizzata la gestione connessione Excel per eseguire le stesse operazioni in ogni cartella di lavoro di Excel corrispondente allo schema e al percorso di file specificati.

Per eseguire un ciclo su un gruppo di tabelle mediante Foreach ADO.NET Schema Rowset Enumerator

Creare una gestione connessione ADO.NET che utilizzi il provider OLE DB Microsoft Jet per connettersi a una cartella di lavoro di Excel. Nella pagina Tutte della finestra di dialogo Gestione connessione specificare Excel 8.0 come valore della proprietà Extended Properties. Per ulteriori informazioni, vedere Aggiunta, eliminazione o condivisione di una gestione connessione in un pacchetto.
Creare una variabile stringa che riceverà il nome della tabella corrente a ogni iterazione del ciclo.
Aggiungere un contenitore Ciclo Foreach alla scheda Flusso di controllo. Per informazioni sulla configurazione del contenitore Ciclo Foreach, vedere Configurazione di un contenitore Ciclo Foreach.
Nella pagina Raccolta dell'Editor ciclo Foreach selezionare Foreach ADO.NET Schema Rowset Enumerator.
Selezionare la gestione connessione ADO.NET creata in precedenza come valore di Connessione.
Selezionare Tabelle come valore di Schema.

[!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, potrebbe essere necessario scrivere appositamente codice personalizzato in un'attività Script. Per ulteriori informazioni, vedere Utilizzo di file di Excel con l'attività Script.
Nella pagina Mapping variabili eseguire il mapping tra l'indice 2 e la variabile stringa creata in precedenza in modo che contenga il nome della tabella corrente.
Chiudere l'Editor ciclo Foreach.
Creare attività nel contenitore Ciclo Foreach che utilizzano la gestione connessione Excel per eseguire le stesse operazioni in ogni tabella di Excel inclusa nella cartella di lavoro specificata. Se per esaminare il nome della tabella enumerata o per lavorare con ogni tabella si utilizza un'attività Script, ricordarsi di aggiungere la variabile stringa alla proprietà ReadOnlyVariables dell'attività Script.