Come esportare i dati da SQL Server a Excel

Come esportare i dati da SQL Server a Excel

In un precedente articolo, SQL ed Excel: perché avete bisogno di entrambi, abbiamo dimostrato che per molte aziende l’uso di SQL Server ed Excel è un approccio ottimale alla gestione dei dati. Per le organizzazioni che utilizzano ampiamente SQL Server ed Excel, è spesso necessario esportare i dati da SQL Server a Excel. I motivi più comuni per ottenere i dati in Excel sono:

• produrre visualizzazioni di dati, quali tabelle e grafici per l’utilizzo in un report
• di aggregazione o di analizzare i dati in grafici pivot
• raccolta di dati da fonti diverse per ulteriore trasformazione
• rendere i dati disponibili per i non utenti del database per visualizzare o processo

In questo articolo, stiamo andando a guardare come esportare i dati da SQL Server a Excel. Il primo metodo utilizza la procedura guidata di importazione ed esportazione di SQL Server, che può essere utilizzata per esportare i dati in un numero di tipi di destinazione, non solo in Excel. Il secondo metodo utilizza la funzionalità nativa in Excel. Il terzo metodo utilizza il componente aggiuntivo SQL Spreads per Excel, che fornisce funzionalità oltre il processo di esportazione. Ci sono pro e contro di ogni metodo, che riassumeremo alla fine dell’articolo.

Metodo (1): Esportazione mediante la procedura guidata di importazione ed esportazione di SQL Server

La procedura guidata di importazione ed esportazione di SQL Server può essere avviata da SQL Server Management Studio o come applicazione autonoma.

Da SQL Server Management Studio, fare clic sul database da cui si desidera esportare i dati. Stiamo usando il database demo nel nostro esempio, quindi fare clic su questo e quindi fare clic destro e selezionare Attività > Esporta dati.

Viene visualizzata la pagina di benvenuto, e si può andare avanti e fare clic su ‘Avanti’.

Nella schermata successiva, selezionare l’origine dati. Per esportare da SQL Server, selezionare ‘SQL Server Native Client’.

Una volta selezionato ‘SQL Server Native Client’, i dettagli sull’origine dati verranno compilati dalla procedura guidata.

Fare clic su ‘Avanti’ per andare alla schermata successiva, dove si sta andando a scegliere la destinazione per l’esportazione, che per noi è Excel.

Quando si seleziona Excel come destinazione, è necessario fornire la posizione del file Excel. Quando si fa clic su “Avanti”, è possibile visualizzare il seguente messaggio:

L’operazione non può essere completata.
ULTERIORI INFORMAZIONI:
Il ‘Microsoft.ACE.OLEDB.16.0 ‘ il provider non è registrato sul computer locale. (Sistema.Dati)

Il motivo per cui viene visualizzato questo messaggio è che quando si avvia la procedura guidata di importazione ed esportazione di SQL Server da SSMS, viene avviata una versione a 32 bit della procedura guidata (poiché SSMS è a 32 bit) e il sistema operativo è a 64 bit. Ci sono ulteriori informazioni su questo qui.

Per aggirare questo problema, è necessario chiudere SSMS e andare al menu Start di Windows e cercare la versione a 64 bit della procedura guidata di importazione ed esportazione di SQL Server dal menu Start. Si noti che questa versione verrà installata sul computer solo se è stato installato SQL Server.

Una volta avviata la versione stand-alone della procedura guidata, è possibile eseguire i passaggi descritti finora, ovvero specificare l’origine e la destinazione della copia dei dati. Ora dobbiamo definire ciò che vogliamo copiare e (facoltativamente) configurare l’operazione di copia.

Dopo aver fatto clic su “Avanti” nella schermata “Scegli una destinazione”, si specifica se si desidera esportare una o più tabelle o viste complete o un sottoinsieme di dati tramite una query SQL. Nel nostro caso, stiamo esportando da una singola tabella, quindi possiamo lasciare selezionata l’opzione predefinita.

Successivamente, modificheremo le mappature delle colonne facendo clic sul pulsante ‘Modifica mappature…’. La schermata Mappature colonne consente di apportare modifiche al nome e al tipo di dati delle colonne che verranno create in Excel mediante l’operazione di copia. Nel nostro esempio, cambieremo il tipo di dati per la colonna ‘order_date’ da VarChar a DateTime. Lasceremo gli altri come set predefinito dalla procedura guidata. Fare clic su ‘Avanti’. La schermata successiva nella procedura guidata riassume che l’origine è una tabella SQL chiamata sales e la destinazione sarà un foglio chiamato ‘sales’ nella cartella di lavoro di Excel. È possibile modificare il nome del foglio in base alle esigenze.

Una volta fatto, fare clic su ” Ok “per chiudere la finestra di dialogo e quindi su “Avanti”.

Se uno qualsiasi dei mapping specificati potrebbe non riuscire nel processo di esportazione, la procedura guidata Importazione ed esportazione di SQL Server mostra la pagina Revisione mappatura dei tipi di dati. In questa pagina vengono evidenziate le conversioni che la procedura guidata deve eseguire per gestire le mappature specificate. Indica anche cosa farà la procedura guidata se si verifica un errore durante la conversione. Può ignorare l’errore o causare il fallimento dell’esportazione. Nel nostro esempio, sono state contrassegnate diverse colonne: quelle in cui è presente una conversione da nvarchar(max) al suo equivalente Excel, Testo lungo e anche la conversione da VarChar a DateTime per la colonna ‘order_date’. L’impostazione predefinita è che la procedura guidata fallisca se c’è un errore, quindi lasceremo tale opzione in posizione.

Nota: questa procedura guidata di importazione ed esportazione SQL utilizza SSIS sotto il cofano; in altre parole, ogni volta che si esegue la procedura guidata, si sta creando un pacchetto SSIS. Questo è il motivo per cui ti viene data la possibilità di salvarlo per un uso futuro.Fare clic su ‘Avanti’. La procedura guidata offre ora la possibilità di salvare la configurazione guidata come pacchetto SSIS oltre all’opzione predefinita di eseguire immediatamente la copia.

Stiamo solo andando a lasciare l’opzione predefinita selezionata e quindi fare clic sul pulsante ‘Avanti’.

La procedura guidata ora visualizza un riepilogo dei passaggi configurati.

Fare clic su ‘Fine’, e la procedura guidata mostrerà ora la schermata finale, che è il risultato dell’esecuzione. Nel nostro caso, l’esecuzione ha avuto successo e possiamo vedere che 5484 record sono stati copiati nel foglio ‘Query’ nella nostra cartella di lavoro Excel.

Possiamo verificare che i dati siano stati esportati da SQL Server in Excel aprendo il file che abbiamo specificato e controllando il foglio “vendite”.

Ora che i dati sono in Excel, possiamo creare alcune visualizzazioni o aggregazioni o qualsiasi altra elaborazione che vogliamo eseguire. Ad esempio, potremmo creare un grafico pivot per visualizzare il Totale per articolo per regione.

Metodo (2): Esportazione utilizzando la funzionalità nativa in Excel

Sebbene il metodo stesso sia più o meno lo stesso, Microsoft ha apportato miglioramenti nelle ultime versioni al modo in cui si ottengono dati da altre fonti in Excel. La modifica principale è stata l’aggiunta delle procedure guidate e degli strumenti di importazione dei dati di Power Query come componente nativo in Excel 2016. Questi sono accessibili dall’editor Power Query, anche se non viene visualizzato come tale sulla barra multifunzione di Excel, ma viene contrassegnato come Get & Trasforma gruppo di dati nella scheda Dati della barra multifunzione di Excel.

I passaggi descritti di seguito sono validi per Excel 2016 e oltre.

Per iniziare con l’esportazione da SQL Server a Excel, fare clic su Dati > Ottieni dati > Dal database > Dal database SQL Server.

Viene richiesto di immettere il server di database a cui connettersi e, facoltativamente, specificare un database. Se non si specifica un database, è possibile selezionare quelli disponibili nella schermata successiva.

Fare clic su ‘Ok’. Ti verrà quindi richiesto di fornire i dettagli di autenticazione. L’impostazione predefinita è Windows, quindi puoi andare avanti e fare clic su “Connetti”.

La schermata successiva consente di navigare tra i database e le tabelle sul server di database specificato. Fare clic sul database ‘demo’ per espanderlo e quindi fare clic sulla tabella ‘vendite’. I dati per la tabella selezionata vengono visualizzati nella finestra di destra. Ora potremmo fare clic sul pulsante “Carica” per ottenere immediatamente i dati in Excel. In alternativa, possiamo fare clic su “Trasforma dati” per eseguire trasformazioni sui dati (ad esempio modificare i tipi di dati, ripulire alcuni dati, eseguire calcoli). Per questo esempio, stiamo solo andando a caricare i dati così com’è, in modo da andare avanti e fare clic su ‘Carica’.

I dati vengono ora esportati dalla tabella in SQL Server e caricati in un nuovo foglio in Excel.

Questo è tutto ciò che dobbiamo fare per eseguire una semplice esportazione di una tabella completa da SQL Server a Excel. A parte la semplice procedura che abbiamo descritto qui, le procedure guidate e gli strumenti di importazione dei dati di Power Query forniscono modi per “modellare” i dati da fonti esterne. Ad esempio, è possibile rimuovere una colonna, modificare un tipo di dati, unire tabelle per soddisfare le proprie esigenze specifiche.

Metodo (3): Esportazione utilizzando SQL Spreads Add-In per Excel

Se non si dispone già di SQL Spreads Add-In per Excel installato, è possibile scaricare una copia qui.

Una volta installato SQL Spreads, vedrai che è stato aggiunto come una nuova scheda ribbon; vai qui e fai clic sul pulsante Design Mode.

Nel pannello SQL Spreads Designer sul lato destro, fare clic sul pulsante Modifica per aprire la finestra di dialogo Connessione SQL Server.

Immettere il nome di SQL Server nel campo Nome SQL Server. È inoltre necessario scegliere un metodo di autenticazione: Windows-login (autenticazione di Windows) o autenticazione di SQL Server (utilizzando un nome e una password impostati in SQL Server). L’autenticazione di Windows è la più sicura delle due opzioni.

Fare clic su OK. Gli spread SQL proveranno a connettersi al database. Se la connessione ha esito positivo, i database verranno visualizzati nel pannello SQL Spreads Designer.

Ora possiamo espandere il database demo e selezionare la tabella di vendita.

Quando si fa clic sulla tabella, i dati vengono immediatamente esportati da SQL Server e copiati in un foglio chiamato “vendite” in Excel.

Questo è tutto! Siamo riusciti a esportare i dati da SQL Server a Excel con solo un paio di clic.

Ci sono molte altre opzioni disponibili in SQL Spreads Designer. Ad esempio, possiamo:

• filtrare i dati esportati da SQL Server
• impostare se alcune colonne da visualizzare e/o di sola lettura
• utilizzare una look-up di valore per visualizzare il testo invece di un documento di identità per una colonna
• di ordinare i dati
• effettuare aggiornamenti in Excel e salvare le modifiche di SQL Server

Vedremo alcune di queste opzioni in un post futuro.

Riepilogo

La possibilità di esportare i dati da SQL Server a Excel è un requisito comune nella maggior parte delle aziende. Abbiamo esaminato tre metodi per eseguire questa attività quotidiana. Il primo utilizza la procedura guidata di importazione ed esportazione di SQL Server, che è la funzionalità nativa in SQL Server. Il secondo metodo utilizza gli strumenti nativi ‘Get & Transform’ in Excel. Il terzo utilizza il componente aggiuntivo SQL Spreads per Excel.

Quindi quale metodo dovresti usare?

Se normalmente lavori con i database e SSMS è la tua seconda casa, probabilmente riterrai che la procedura guidata di importazione ed esportazione di SQL Server sia l’opzione migliore per te. Se sei un utente di Excel, allora ha senso lavorare all’interno di Excel stesso per esportare i dati da SQL Server. Entrambe le due opzioni di Excel che abbiamo esaminato sono robuste e facili da usare, ma SQL Spreads ha un netto vantaggio perché consente agli utenti di aggiornare i dati direttamente in Excel e quindi salvare i dati su SQL Server. Questo è un enorme vantaggio per quelle organizzazioni che amano il meglio dei due mondi: la facilità d’uso e la familiarità di Excel, con la potenza e l’integrità dei dati associati a SQL Server.

Prova SQL Spreads per vedere quanto sia facile e veloce esportare i dati da SQL Server in Excel; scarica una versione di prova gratuita oggi.