Esportazione dei dati: Dalla query SQL al foglio di calcolo

Come si esportano i dati da un database SQL a Excel o a un altro foglio di calcolo? In questo articolo, esamineremo diversi metodi e condividerò alcuni suggerimenti per i casi in cui le cose vanno male.

SQL è il metodo più utilizzato per estrarre i dati da un database. Tuttavia, non è l'unico strumento nella cassetta degli attrezzi di un analista. Una volta estratte le informazioni, spesso si desidera importarle in un foglio di calcolo, magari per effettuare ulteriori analisi o semplicemente per renderle più belle da presentare alla direzione. L'SQL estrae i dati come testo semplice, quindi come si fa a inserirli in un foglio di calcolo?

Un analista di dati deve possedere competenze sia nel foglio di calcolo che nell'SQL. I fogli di calcolo sono ampiamente utilizzati e insegnati nelle scuole, quindi presumo che siate già in grado di usarli.

Se non siete ancora esperti di SQL, potreste leggere Il modo migliore per imparare SQL: Una guida completa per i principianti, che vi darà alcune idee su come iniziare. Consiglio anche il corso SQL per l'analisi dei dati di LearnSQL.it. Appositamente progettato per gli analisti di dati, questo percorso consiste in quattro corsi che vi porteranno da un livello principiante a un livello di competenza nell'analisi dei dati con SQL. Richiede circa 48 ore per essere completato e vi insegnerà a risolvere il tipo di problemi che incontrerete nel lavoro di analisi.

Se conoscete già un po' di SQL ma volete un po' di aiuto, ecco unfoglio informativo gratuito su SQL per l'analisi dei dati.

Reporting SQL con Excel

In questo articolo mi concentrerò principalmente su come esportare i dati SQL in fogli di calcolo, ma vorrei prima dedicare qualche minuto a capire perché si dovrebbe farlo. Vediamo alcuni scenari:

Lavorate nel marketing

Avete usato l'SQL per estrarre i dati demografici e scoprire che tipo di prodotti sono stati acquistati dai clienti in diverse aree e gruppi di età negli ultimi due anni. Ora dovete fare una presentazione con grafici e tabelle per giustificare la campagna che avete in mente per il prossimo anno.

Per creare questi grafici e tabelle e dare loro un aspetto professionale, potete utilizzare un foglio di calcolo. I fogli di calcolo dispongono di una serie di strumenti utili per gli analisti di dati, tra cui:

• Grafici e diagrammi.
• Tabelle pivot.
• Mappe di potenza.
• Ordinamento e filtraggio
• Funzioni che eseguono calcoli complessi.

Lavorate in una banca

Sospettate che ci sia una frode in corso. Avete usato SQL per estrarre i dettagli delle transazioni sospette, ma dovete sperimentare il filtraggio e l'ordinamento delle transazioni e confrontarle con altri eventi del periodo. Probabilmente utilizzerete un foglio di calcolo per farlo, perché semplifica l'esame dei dati in molti modi diversi.

Dovete presentare un rapporto al vostro manager

Ogni mese si deve creare un rapporto che mostri il totale delle vendite effettuate a ciascun cliente. La query SQL produce questo risultato:

Tuttavia, vorreste formattarlo in un report attraente per il vostro manager, come questo:

Le vostre competenze saranno sempre richieste se saprete lavorare con i dati di un foglio di calcolo Excel, importare dati da SQL e produrre report interessanti e significativi.

Esportazione di dati SQL in fogli di calcolo

Ok, passiamo al punto dell'articolo: come trasferire i dati da una query SQL a un foglio di calcolo. Esistono molti fornitori di database, ognuno con un ambiente di lavoro leggermente diverso, e molti tipi diversi di fogli di calcolo. Per questo motivo è difficile scrivere una guida completa all'esportazione di query SQL, ma vi mostrerò come utilizzare tre database popolari ...

• Google BigQuery,
• Microsoft SQL Server e
• MySQL

... con tre diversi fogli di calcolo:

• Excel,
• Libre Calc della suite Libre Office e
• Google Sheets.

Presumo inoltre che abbiate un PC Windows. Gli utenti di Linux e Mac dovranno utilizzare gli equivalenti del clic con il tasto destro del mouse e le scorciatoie da tastiera Crtl+.

Esaminerò diversi metodi e vedrò come possono essere utilizzati con i software citati. Imparerete abbastanza per esportare e importare dati ovunque, dato che la maggior parte dei database e dei fogli di calcolo sono molto simili.

Mostrerò esempi utilizzando i dati pubblici di BigQuery, il database AdventureWorks di Microsoft e il database Employee di MySQL.

Inizierò con il metodo più semplice.

Metodo 1: Copia e incolla

È il metodo più rapido per esportare una query SQL in un foglio di calcolo e lo uso il 99% delle volte. Ovviamente, è necessario eseguire prima una query SQL per fornire i dati.

Non si dovrebbe usare questo metodo per insiemi di dati molto grandi, perché deve memorizzare i dati degli appunti. È possibile che il computer si blocchi se il set di dati è troppo grande. Più avanti in questo articolo, analizzerò alcuni dei problemi che si possono incontrare con questo metodo e come risolverli.

Nell'immagine qui sotto, ho scritto una query per estrarre tutti i clienti che compiono gli anni a gennaio. L'ufficio relazioni con i clienti può utilizzarla per inviare loro e-mail di auguri. Sto usando SQL Server Management Studio per eseguire la query e questo è il risultato:

È possibile copiare i dati in due modi. Innanzitutto, si evidenziano i dati. Poi si può fare clic su Modifica → Copia dal menu, oppure si può usare la scorciatoia da tastiera premendo CTRL+C.

In questo modo si copiano negli appunti le righe evidenziate e le intestazioni, come dati separati da tabulazioni.

Un'altra opzione, se si desidera copiare tutte le righe nel foglio di calcolo, è fare clic sul quadrato vuoto in alto, come indicato dalla freccia rossa nell'immagine sottostante. Tutte le righe verranno evidenziate e sarà possibile copiare i dati negli appunti.

Facendo clic con il tasto destro del mouse sullo stesso riquadro vuoto si ottiene un menu che consente di scegliere se copiare con o senza intestazioni.

Copiare i dati da MySQL

Nella schermata qui sotto, potete vedere la mia query per estrarre tutti i dipendenti che avranno più di 65 anni all'inizio del 2024; l'azienda può usare queste informazioni per considerare alcuni dipendenti per il pensionamento. Per eseguire la query sto usando MySQL Workbench.

Anche in questo caso, ci sono alcuni modi per copiare negli appunti: Modifica → Copia dal menu o CTRL+C. In questo modo i dati vengono copiati senza intestazioni e sono delimitati da tabelle.

Se si fa clic sulla cella vuota in alto indicata nell'immagine sottostante, vengono selezionate tutte le righe. Se poi si copiano negli appunti, verranno incluse le intestazioni.

Copiare i dati da BigQuery

Nella schermata seguente, ho scritto una query per estrarre un elenco di banche registrate presso la FDIC. Si tratta di un database presente nei dati pubblici di BigQuery. Per eseguire la query sto usando BigQuery Studio.

Anche se in teoria è possibile evidenziare le righe e utilizzare CTRL+C o la funzione Appunti del browser, in pratica non funziona bene: le colonne tendono a non essere sincronizzate. L'unico modo valido per copiare negli appunti è utilizzare la funzione Salva risultati, evidenziata nell'immagine sottostante:

In questo modo si ottiene un menu:

Se si sceglie Copia negli appunti, i dati vengono copiati in formato tab-delimitato con intestazioni. Nota: in questo modo è possibile copiare solo 1 MB di dati.

Incollare in Excel

Una semplice operazione di Incolla, utilizzando l'icona Appunti nel menu Home o digitando CTRL+V, porta i dati in Excel, a partire dalla posizione del cursore. Di solito è necessario eseguire alcune formattazioni per rendere i dati più gradevoli: allargare le colonne, aggiungere intestazioni significative, ecc.

Se i dati non vengono visualizzati come ci si aspettava, alla fine di questo articolo c'è una sezione dedicata alla risoluzione dei problemi più comuni nell'esportazione di dati SQL in fogli di calcolo.

Incollare in Libre Calc

In Libre Calc, l'operazione di incollaggio si avvia digitando CTRL+V o scegliendo Modifica → Incolla dal menu iniziale. Il programma consente di impostare alcune opzioni per controllare il modo in cui i dati vengono importati. Viene visualizzato un menu a comparsa:

Se si fa semplicemente clic su OK, il programma utilizzerà le impostazioni predefinite per importare i dati. Di solito questo funziona bene, ma in caso contrario potrebbe essere necessario impostare alcune opzioni.

La modifica più comune è quella di impostare il tipo di dati di una colonna che viene importata in modo errato. A tale scopo, è sufficiente fare clic sulla colonna nella finestra di anteprima dei dati per selezionarla. È quindi possibile impostare il tipo di dati facendo clic sulla casella Tipo di colonna appena sopra la sezione di anteprima e selezionando un tipo di dati:

A volte, per ragioni meglio conosciute dai gremlin dei byte nascosti in tutti i computer, questa finestra di dialogo non viene visualizzata e i dati vengono incollati utilizzando le impostazioni predefinite. Di solito va bene. Ma se è necessario impostare una qualsiasi opzione, è possibile forzare la finestra di dialogo scegliendo Modifica → Incolla speciale → Incolla speciale dal menu Home :

Si aprirà un altro menu:

Scegliere Utilizza la finestra di dialogo di importazione del testo. Verrà quindi visualizzata la stessa finestra di dialogo vista in precedenza.

Incollare in Google Sheets

In Google Sheets, è sufficiente posizionare il cursore e scegliere Modifica → Incolla oppure premere CTRL+V. Non ci sono opzioni che consentano di controllare il modo in cui i dati vengono incollati; il programma utilizza semplicemente le sue impostazioni predefinite.

Metodo 2: Utilizzo di file CSV per trasferire i dati

I file CSV (valori separati da virgole) sono un metodo molto diffuso per trasferire dati da un'applicazione all'altra.

Li si usa in alternativa al copia-incolla quando:

• I dati sono troppo grandi per essere inseriti comodamente negli appunti. In genere, non si vuole usare il copia-incolla per più di circa diecimila righe.
• Il foglio di calcolo si trova su un computer diverso da quello che si sta utilizzando per estrarre i dati.
• Si desidera far circolare i dati per consentire a qualcun altro di importarli nel proprio foglio di calcolo.

Le colonne del risultato della query saranno separate da virgole nel file CSV.

Estrazione di un file CSV da SQL Server

Nella finestra dei risultati della query, fare clic con il tasto destro del mouse sulla cella vuota in alto a sinistra dei dati. Verrà visualizzato il seguente menu.

Scegliere Salva risultati con nome. Viene quindi visualizzata una finestra di dialogo standard per il salvataggio dei file, che consente di assegnare un nome al file CSV e di navigare verso la posizione in cui si desidera memorizzarlo.

Estrarre un file CSV da MySQL

Appena sopra la finestra dei risultati della query, c'è una funzione per esportare i risultati in un file esterno. Questa funzione è evidenziata nell'immagine sottostante.

Facendo clic su di essa, si aprirà la finestra di dialogo Salva file, che consente di assegnare un nome al file e di navigare verso la posizione in cui si desidera memorizzarlo.

Estrazione di un file CSV da BigQuery

In BigQuery Studio, c'è un pulsante Salva risultati appena sopra la finestra dei risultati. Facendo clic su di esso, si ottiene il seguente menu:

È quindi possibile scegliere tra:

• CSV Local File per salvare sul computer. Si aprirà la consueta finestra di dialogo Salva file.
• CSV (Google Drive). Questo file verrà salvato in Google Drive, utilizzando (a mio parere) un nome poco amichevole. Fortunatamente, viene visualizzato un link come nell'immagine sottostante. Facendo clic sul file, è possibile eseguire operazioni utili, come rinominarlo, condividerlo o aprirlo con Google Sheets.

Aprire un file CSV in Excel

È possibile aprire un file CSV dal menu File → Apri. Quando appare la finestra di dialogo Apri file, cercate il punto in cui avete salvato il file. In alcune versioni di Excel, i file CSV vengono elencati automaticamente. In altre, è necessario impostare la visualizzazione di Tutti i file, come si vede nell'immagine seguente:

È quindi possibile selezionare il file e aprirlo.

Probabilmente sarà necessario formattarlo per renderlo più gradevole. Usare sempre Salva con nome per salvarlo come cartella di lavoro di Excel, perché i file CSV non possono includere alcuna formattazione.

Apertura di un file CSV in Libre Calc

In Libre Calc, scegliere File → Apri e navigare fino al punto in cui è stato salvato il file. Libre Calc mostra automaticamente tutti i file, quindi non dovrebbe essere difficile trovarlo.

Una volta selezionato il file e aperto, Libre Calc visualizzerà la stessa finestra di dialogo per l'importazione del testo già vista per incollare i risultati delle query.

Aprire un file CSV in Google Sheets

Aprite un nuovo foglio vuoto, quindi scegliete File → Importa.

In questo modo si avrà la possibilità di scegliere da dove importare il file:

• Se il file è stato salvato in Google Drive, scegliere My Drive e selezionare il file desiderato.
• Se il file si trova sul computer, scegliere Carica. In questo modo è possibile trascinare il file nella finestra. In alternativa, è possibile scegliere Sfoglia per accedere alla finestra di dialogo Apri file.

Verranno quindi visualizzate le seguenti opzioni:

Normalmente è sufficiente fare clic su Importa dati e utilizzare le impostazioni predefinite. Tuttavia, è possibile modificare l'opzione Sostituisci foglio di calcolo. Facendo clic su di essa si ottengono diverse scelte utili.

Se si deseleziona Converti il testo in numeri, date e formule, tutti i dati verranno importati come testo. Questo può essere utile, poiché a volte le date e i campi di testo che contengono numeri possono risultare errati. Ad esempio, una banca può avere un numero di conto numerico di 16 cifre. Poiché Google Sheets ha un limite di 15 cifre significative per i campi numerici, il numero di conto può essere danneggiato dall'arrotondamento.

Metodo 3: funzioni di esportazione nel software RDBMS

La maggior parte dei software per la gestione dei database relazionali (RDBMS) include funzioni per l'esportazione dei risultati delle query. Ne illustrerò brevemente alcuni.

SQL Server

SQL Server dispone di due utility utili per l'esportazione da SQL a Excel. Entrambe sono piuttosto complicate, quindi mi limiterò a descriverne le funzioni e a fornire i link alla relativa documentazione Microsoft.

• Programma di copia in blocco (BCP). Il programma di copia in blocco è progettato per copiare i dati da SQL Server in vari formati. È possibile utilizzarlo per creare file CSV. Inoltre, poiché è possibile programmare l'esecuzione regolare, è un buon modo per automatizzare il trasferimento di dati da SQL a Excel o a qualsiasi altro foglio di calcolo. Potete imparare a usare BCP qui.
• Procedura guidata di importazione ed esportazione di SQL Server. Consente di esportare i risultati di una query in vari formati, tra cui Excel. Per sapere come usare la procedura guidata per esportare dal database SQL a Excel, cliccate qui. Poiché sia Libre Calc che Google Sheets possono aprire file Excel, è possibile utilizzare questo metodo anche per esportare in questi programmi.

MySQL

Nella sezione precedente abbiamo visto come utilizzare MySQL Workbench per produrre i dati in un file CSV. L'opzione Esporta set di risultati in un file esterno può essere usata anche per produrre altri tipi di file.

Come si può vedere, una delle scelte possibili è quella di esportare in un foglio di calcolo Excel. In questo modo i dati vengono esportati come file XML di un foglio di calcolo, che può essere facilmente e accuratamente importato in Excel. Anche Libre Office aprirà con successo i file esportati in questo modo, ma Google Sheets non lo farà.

BigQuery

Il pulsante Salva risultato in BigQuery può essere utilizzato per esportare i dati direttamente in Google Sheets, come si può vedere dalle opzioni sottostanti. Questo è il modo più semplice ed efficiente per importare i dati da BigQuery in Google Sheets. Poiché Google Sheets consente di esportare in Excel e Libre Calc può aprire fogli di calcolo Excel, è possibile utilizzare questo metodo anche per portare i dati in uno di questi pacchetti.

Metodo 4: Utilizzo di altri strumenti di analisi dei dati per creare fogli di calcolo

Esistono molti strumenti di analisi dei dati sul mercato e quasi tutti consentono di utilizzare SQL per estrarre i dati da un database. Molti hanno anche la possibilità di salvare i risultati come foglio di calcolo. Poiché la maggior parte degli strumenti è in grado di programmare regolarmente i rapporti, essi possono rappresentare un buon modo per produrre automaticamente fogli di calcolo quando necessario. Alcuni degli strumenti che permettono di estrarre i dati con SQL e di salvarli come fogli di calcolo sono:

• Tableau
• Rapporti di cristallo
• Power BI
• Zoho Analytics
• Rapporti Jasper

Problemi comuni riscontrati nell'esportazione di SQL in fogli di calcolo

La maggior parte delle volte i dati vengono importati correttamente nei fogli di calcolo, ma a volte si verificano dei problemi. Ecco alcuni problemi comuni e le relative soluzioni.

Tutti i dati appaiono in una singola colonna

I risultati vengono incollati in Excel. Ma invece che in una colonna separata, tutti i campi appaiono in un'unica colonna. Questo accade di solito quando Excel "ricorda" (da una precedente operazione di incollaggio) che deve usare qualcosa di diverso dalle tabulazioni per i delimitatori. I dati possono apparire così:

Per risolvere il problema, andare alla scheda Dati della barra multifunzione. Assicurarsi che sia selezionato Scegli testo nelle colonne :

Dalla finestra a comparsa, scegliere Delimitato, quindi fare clic su Avanti. Nella finestra di dialogo successiva, selezionare Tabulazioni sotto Delimitatori, quindi Avanti. A questo punto è possibile scegliere i tipi di dati per ogni colonna.

Se si desidera modificare uno di essi, fare clic sulla colonna e selezionare il tipo dai pulsanti di opzione in alto. Questo è particolarmente utile se si desidera che le date appaiano in un formato diverso o che i numeri vengano trattati come testo.

Se questo problema continua a verificarsi, cosa si può fare? La prossima volta che si desidera incollare dei dati, fare clic sulla freccia sotto il pulsante Incolla. Si potrà scegliere di utilizzare la procedura guidata di importazione del testo, ovvero le finestre di dialogo viste nel paragrafo precedente. In questo modo è possibile scegliere Tab come delimitatore; Excel "ricorderà" questa scelta per le future operazioni di incollaggio.

Le date sono sbagliate

Questo può accadere quando si incolla o si importa da un CSV. A volte è solo un problema di formattazione: si può selezionare la colonna e usare il comando Formato per far apparire le date nel modo desiderato.

Ma a volte il problema può essere più serio. Il formato americano della data breve, ad esempio, è mm/dd/yyyy, mentre il formato britannico della data breve è dd/mm/yyyy. Se il formato predefinito della data nel foglio di calcolo è diverso da quello dell'applicazione di database, si potrebbe finire per interpretare una data che dovrebbe rappresentare il 4 gennaio come il 1° aprile, perché i giorni e i mesi sono invertiti.

Il modo migliore per evitarlo è quello di eseguire una formattazione in SQL per assicurarsi che le date siano nel formato previsto dal foglio di calcolo.

I numeri sono in notazione scientifica

Se nel foglio di calcolo sono presenti numeri che assomigliano a 123456E+14, significa che sono in notazione scientifica. Ciò potrebbe essere dovuto al fatto che la colonna è troppo stretta; è possibile risolvere il problema allargando la colonna. Tuttavia, è probabile che il problema sia dovuto a un numero lungo più di 15 cifre. Tutti e tre i fogli di calcolo che abbiamo esaminato non sono in grado di gestire numeri più grandi. Li arrotondano automaticamente e li mostrano in notazione scientifica.

Questo va bene per le applicazioni scientifiche, ma non va bene per la contabilità, e sicuramente non va bene se il numero rappresenta un numero di conto a 16 cifre!

È possibile visualizzare correttamente i numeri trattandoli come testo e non come numeri. Tuttavia, non sarà possibile eseguire operazioni aritmetiche in questo formato.

Se si è usato il taglia e incolla per importare i dati, usare l'opzione Incolla speciale → Importazione guidata testo per cambiare il tipo di dati della colonna in testo.

Se i dati provengono da un file CSV, il modo più semplice per importarli correttamente è aprire il file in Blocco note (o qualsiasi altro editor di testo di base), selezionare e copiare tutti i dati e usare Incolla speciale → Importazione guidata testo per specificare i tipi di dati.

Se i dati sono troppo grandi per un'unica operazione di copia/incolla, potrebbe essere necessario eseguire l'operazione di copia/incolla in parti più piccole.

La colonna ID ha perso gli zeri iniziali o una cifra

È possibile che una colonna contenga un identificatore numerico e che i numeri siano molto grandi. Gli zeri iniziali sono importanti in questo caso, poiché fanno parte dell'ID.

La soluzione è la stessa dei numeri in notazione scientifica: Trattare la colonna come testo, non come dati numerici.

Costruire le proprie competenze da SQL a foglio elettronico

Come abbiamo visto, SQL e fogli di calcolo possono essere facilmente utilizzati insieme per sfruttare la potenza di entrambe le applicazioni. L'SQL è la porta d'accesso al mondo dei dati e vale la pena di aumentare le proprie competenze in questo campo.

Se non avete ancora imparato l'SQL, il nostro corso SQL per principianti è un ottimo punto di partenza. E se volete investire davvero nel vostro futuro con SQL, il pacchetto Completo per sempre vi dà accesso a tutti i nostri corsi, compresi quelli che non abbiamo ancora scritto! Questo significa che sarete sempre in grado di rimanere aggiornati su tutte le nuove funzionalità che verranno aggiunte a SQL in futuro.

Se siete in grado di utilizzare SQL e fogli di calcolo, e di farli lavorare insieme, le vostre competenze saranno sempre richieste nel mondo odierno basato sui dati. Quindi fate il primo passo oggi stesso! Iniziate a imparare, a sperimentare e a prendere in mano il vostro futuro!