Eseguire query SQL su file Excel

Sebbene le azioni di Excel possano gestire la maggior parte degli scenari di automazione di Excel, le query SQL possono recuperare e manipolare quantità significative di dati di Excel in modo più efficiente.

Supponiamo che un flusso debba modificare solo i registri Excel che contengono un particolare valore. Per ottenere questa funzionalità senza query SQL, sono necessari cicli, condizioni e più azioni di Excel.

In alternativa, puoi implementare questa funzionalità con le query SQL usando due sole azioni, Apri connessione SQL ed Esegui istruzioni SQL.

Apertura di una connessione SQL a un file Excel

Prima di eseguire una query SQL devi aprire una connessione con il file Excel cui desideri accedere.

Per stabilire la connessione, crea una nuova variabile denominata %Excel_File_Path% e inizializzala con il percorso del file Excel. Facoltativamente, puoi ignorare questo passaggio e usare il percorso hard-coded del file più avanti nel flusso.

Ora, distribuire l'azione Apri connessione SQL e popolare la seguente stringa di connessione nelle sue proprietà.

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=%Excel_File_Path%;Extended Properties="Excel 12.0 Xml;HDR=YES";

Nota

Per utilizzare correttamente la stringa di connessione presentata, occorre scaricare e installare il motore di database di Microsoft Access 2010 ridistribuibile.

Apertura di una connessione SQL in un file Excel protetto da password

È necessario un approccio diverso negli scenari in cui si eseguono query SQL su file Excel protetti da password. L'azione Apri connessione SQL non può connettersi a file Excel protetti da password, quindi è necessario rimuovere la protezione.

Per ottenere questo risultato, avvia il file Excel usando l'azione Avvia Excel. Il file è protetto da password, quindi immetti la password appropriata nel campo Password.

Quindi, distribuire le azioni di automazione dell'interfaccia utente appropriate e selezionare File>Informazioni>Proteggi cartella di lavoro>Crittografa con password. Ulteriori informazioni sull'automazione dell'interfaccia utente e su come usare le rispettive azioni sono disponibili in Automazione delle applicazioni desktop.

Dopo aver selezionato Crittografa con password, popola una stringa vuota nella finestra di dialogo popup usando l'azione Popola campo di testo nella finestra. Per popolare una stringa vuota, usare la seguente espressione: %""%.

Per premere il pulsante OK nella finestra di dialogo e applicare le modifiche, distribuire l'azione Premi il pulsante nella finestra.

Infine, distribuisci l'azione Chiudi Excel per salvare la cartella di lavoro non protetta come nuovo file Excel.

Dopo aver salvato il file, segui le istruzioni in Apertura di una connessione SQL a un file Excel per aprire una connessione al file stresso.

Una volta completata la manipolazione del file Excel, usa l'azione Elimina file per eliminare la copia non protetta del file Excel.

Lettura del contenuto di un foglio di calcolo Excel

Sebbene l'azione Leggi da foglio di lavoro di Excel permetta di leggere il contenuto di un foglio di lavoro Excel, l'iterazione dei cicli nei dati recuperati può richiedere tempi molto lunghi.

Un modo più efficiente per recuperare valori specifici dai fogli di calcolo consiste nel trattare i file Excel come database ed eseguire query SQL su di essi. Questo approccio è più veloce e aumenta le prestazioni del flusso.

Per recuperare tutto il contenuto di un foglio di calcolo, puoi usare la seguenti query SQL nell'azione Esegui istruzioni SQL.

SELECT * FROM [SHEET$]

Nota

Per applicare questa query SQL nei flussi, sostituire il segnaposto SHEET con il nome del foglio di calcolo a cui si desidera accedere.

Per recuperare le righe che contengono un particolare valore in una colonna specifica, utilizzare la seguente query SQL:

SELECT * FROM [SHEET$] WHERE [COLUMN NAME] = 'VALUE'

Nota

Per applicare questa query SQL nei flussi, sostituire:

• SHEET con il nome del foglio di calcolo cui desideri accedere.
• COLUMN NAME con la colonna che contiene il valore che si desidera trovare. Le colonne nella prima riga del foglio di lavoro di Excel sono identificate come nomi di colonna della tabella.
• VALUE con il valore ricercato.

Eliminazione di dati da una riga di Excel

Sebbene Excel non supporti la query SQL DELETE, puoi usare la query UPDATE per aggiornare tutte le celle di una riga specifica e impostarle su null.

Più precisamente, puoi usare la seguente query SQL:

UPDATE [SHEET$] SET [COLUMN1]=NULL, [COLUMN2]=NULL WHERE [COLUMN1]='VALUE'

Quando sviluppi il flusso, devi sostituire il segnaposto SHEET con il nome del foglio di calcolo cui desideri accedere.

I segnaposto COLUMN1 e COLUMN2 rappresentano i nomi delle colonne da gestire. Questo esempio prevede due colonne, ma in uno scenario reale il numero di colonne potrebbe essere diverso. Le colonne nella prima riga del foglio di lavoro di Excel sono identificate come nomi di colonna della tabella.

La parte [COLUMN1]='VALUE' della query definisce la riga da aggiornare. Nel flusso, utilizzare il nome della colonna e il valore in base alla combinazione che descrive le righe in modo univoco.

Recuperare i dati di Excel ad eccezione di una riga specifica

In alcuni scenari, potresti aver bisogno di recuperare tutto il contenuto di un foglio di calcolo Excel ad eccezione di una riga specifica.

Un modo pratico per ottenere questo risultato consiste nell'impostare i valori della riga indesiderata su null e recuperare quindi tutti i valori ad eccezione di quelli null.

Per modificare i valori di una riga specifica nel foglio di calcolo, è possibile usare una query SQL UPDATE, come indicato in Eliminare i dati da una riga Excel:

UPDATE [SHEET$] SET [COLUMN1]=NULL, [COLUMN2]=NULL WHERE [COLUMN1]='VALUE'

Quindi, eseguire la seguente query SQL per recuperare tutte le righe del foglio di calcolo che non contengono valori null:

SELECT * FROM [SHEET$] WHERE [COLUMN1] IS NOT NULL OR [COLUMN2] IS NOT NULL

I segnaposto COLUMN1 e COLUMN2 rappresentano i nomi delle colonne da gestire. Questo esempio prevede due colonne, ma in una tabella reale il numero di colonne potrebbe essere diverso. Tutte le colonne nella prima riga del foglio di lavoro di Excel sono identificate come nomi di colonna della tabella.