Query SQL variabile in un foglio Excel

Query SQL variabile in un foglio Excel: in questo articolo vediamo come impostare un foglio Excel affinché ci mosti i risultati di una query SQL.
I dati verranno mostrati in una tabella già formattata e con tutti i filtri.

Operazione molto utile per dare accesso a dati contenuti in un database anche ad utenti non pratici di SQL.
Nella seconda parte mostriamo come aggiungere una variabile alla query da eseguire

---

Query SQL in un foglio Excel

Partendo da un nuovo foglio Excel la prima cosa da fare è quella di creare una connessione ad un db.
Andiamo nella scheda DATI. Selezionare: Recupera dati quindi Da altre origini e successivamente Da Microsoft Query.

A questo punto viene mostrata una finestra che ci permette di selezionare l’origine dati alla quale connettersi.
Per creare una nuova origine dati selezioniamo <Nuova origine dati>

Selezione <Nuova origine dati> quindi click su OK
Appare una nuova finestra chiamata Crea Origine dati

Compilare i vari campi in questo modo:

1. Etichetta da dare all’origine dati: nel caso in esempio DATIDASERVERSQL
   È possibile scegliere il nome che più vi aggrada.
   Consiglio di dare un nome facilmente riconducibile all’origine dati con la quale state lavorando, ad Esempio: AnagraficaFornitoriDittaXXX oppure SqlDittaYYY
2. Selezione driver per il db: per connettersi ad un SQL Server selezionare SQL Server
3. Click “Connetti…” -> si apre una piccola finestra: compilare i dati di riferimento al vostro db: indirizzo, username, password
   NB: per la configurazione iniziale e la visione dei db è necessario autenticarsi con un account con diritti elevati.
   Siccome Excel non ha sistemi di cifratura della password consiglio spassionatamente, per la connessione, di utilizzare un utente che abbia accesso al db in sola lettura.
   In questo modo evitiamo che credenziali con diritti di scrittura o modifica del db vengano divulgate. Più avanti vediamo come modificare la connection string.

Dopo aver inserito i parametri di connessione Click su Opzioni se si vuole indicare ad esempio un db di default da utilizzare nella connessione.
Click su OK per chiudere la finestra della Connessione a SQL Server.

Si torna nuovamente alla finestra: Crea origine dati.
A questo punto click su OK.

Creazione guidata Query.

Ora viene mostrata la Creazione guidata Query.
Seguiamo questo passaggio implementando una query banale, dopo di che potremo agire direttamente su codice SQL per implementare quey più complesse.

Selezionare il nome della tabella desiderata, espandere la selezione e tramite il pulsante a freccia destra indicare le colonne da mostrare nella query.
In immagine sotto un esempio in qui seleziono la tabella ANAGRAFICA ARTICOLI ed esporto solo le colonne: CODICE e DESCRIZIONE.

Click su Avanti 3 o 4 volte, oppure impostare le opzioni di ordinamento e altri parametri secondari.
Nell’ultima schermata selezionare Restituisci dati a Microsoft Excel quindi click su Fine

Esportare i dati nel foglio Excel

Ora appare la finesta Importa dati, qui possiamo selezionare come e dove visualizzare i dati.
Nell’esempio seleziono di vedere i dati in tabella nella cella A1

Click su OK e in pochi secondi nel foglio Excel apparirà il risultato della query precedentemente impostata.

A questo punto abbiamo impostato una Query SQL in un foglio Excel.

È possibile aggiornare i dati della quey facendo click sul pulsante Aggiorna tutti presente nella scheda Dati.

---

Modificare i parametri di una query in un foglio Excel

Ora che il nostro foglio Excel è impostato per mostrare i risultati di una Query SQL possiamo andare a modificare alcuni parametri e comportamenti di questa connessione.

Selezioniamo la scheda Dati quindi Query e connessioni.
Nella parte sinistra di Excel si apre una colonna dal titolo Query e connessioni, qui selezionare la query di nostro interesse.
Click col tasto destro del mouse, quindi click su Proprietà.

Aggiornamento automatico

Ora si apre una finestra denominata: Proprietà di connessione.
Selezionare la prima scheda: Utilizzo

Mettere la spunta sulla voce Aggiorna dati all’apertura del file per permettere l’esecuzione automatica della query nel momento in cui viene aperto il file.
Con questa opzione selezionata siamo sicuri di avere i dati aggiornati all’apertura del file.

Modifica connection string e comando SQL

Sempre nella finestra: Proprietà di connessione selezioniamo la scheda Definizione per modificare la Connection String e la Query SQL.

Subito vi lascio 2 consigli:

1. Mettere la spunta su Salva Password: in questo modo Excel non chiederà la password dell’utente SQL ogni qual volta aggiornate la query.
   Mettendo questa spunta Excel mostra un messaggio in cui vi ricorda che la chiave sarà salvata in chiaro. Da questo nasce il consiglio numero 2.
2. Modificate la connection string indicando un utente che ha diritti di sola lettura nel Database.

Per modificare la connection string sostituire:

UID=andrea.paris;

con l’autenticazione di SQL dell’utente di sola lettura:

UID=ReadSqlUser;PWD=sonolapassword;

In questo modo la password è in chiaro, ma di un utente con i diritti di sola consultazione del db.
Il vantaggio è che ad ogni aggiornamento della Query in Excel non dovremo inserire la password.

---

Sempre nella finestra Proprietà di connessione, scheda Definizione: nella sezione Testo comando possiamo modificare direttamente la query con il linguaggio SQL.

---

Query SQL variabile in un foglio Excel

Arriviamo finalmente ad aggiungere una variabile direttamente nel foglio Excel che possa condizionare la nostra query.

Ora collegheremo una cella del foglio Excel come condizione della clausola “where” per rendere la query variabile senza intervenire sul codice SQL, rendendo il tutto più user friendly.

Come visto prima andiamo nelle proprietà della connessione: selezioniamo la scheda Dati quindi Query e connessioni.
Nella parte sinistra di Excel si apre una colonna dal titolo Query e connessioni, qui selezionare la query di nostro interesse.
Click col tasto destro del mouse, quindi click su Proprietà.

Selezionare la scheda Definizione: nella sezione Testo comando modifichiamo il codice SQL.

Alla fine della query eliminiamo la riga del “WHERE” ( se presente) ed andiamo ad aggiungere:
WHERE  ( TABELLA.COLONNA =? )
La Query in esempio diventerà:

SELECT CODICE, DESCRIZIONE
FROM ANAGRAFICAARTICOLI
WHERE CODICE = ?

A fine modifica fare click su OK, immediatamente si apre una finestra dal titolo: Immettere valore parametro.
Selezionare la cella che desideriamo riservare per il dato da cercare.
Spuntare entrambi i campi per eseguire l’aggiornamento della Query al variare del valore della cella.

A questo punto la nostra query funzionerà condizionata al valore della cella selezionata e si aggiornerà in automatico al variare del valore di questa.

Per modificare il parametro, cioè la cella a cui punta il punto di domanda nella condizione WHERE
tornare sulle proprietà di connessione.
Click su “Proprietà” -> scheda “Definizione”
Click sul pulsante “Parametri…” a fondo scheda. Si aprirà una finestra con la quale possiamo modificare il riferimento del nostro Parametro1

Modifiche dei parametri

Questa modalità funziona con tutti gli operatori SQL: like, = , > , <

È possibile impostare più condizioni legate a celle esterne, avremo una clausola where del tipo:

WHERE CODICE = ? and DESCRIZIONE like = ?

Per ogni parametro andrà selezionata la cella da collegare, il gioco è fatto.