Estrazione dei dati in Excel

Capita molto spesso di dover estrarre dei dati in Excel a partire da query SQL. Chiaramente ci sono vari modi per farlo da quello standard a quello più semplice a quello più sofisticato con un programma.

Tramite il wizard dell'Enterprise Manager

Il modo più naturale per un data export in Excel è quello di utilizzare l'Enterprise Manager di SQL Server. I passi da seguire sono abbastanza semplici. Una volta individuata la query il cui output deve andare in Excel:

Selezionare il DB di partenza

Click Destro -> Tutte le attività -> Export Data

Come sorgente scegliere il database su cui eseguire la query

Come destinazione selezionare Excel 97-2000 e indicare il nome del file da generare

Quindi scegliere l'utilizzo di una query per specificare i dati da trasferire

Inserire la query e lanciarla andando fino alla fine nel wizard

E' tutto molto semplice però ogni volta è necessario far partire il Wizard, selezionare la fonte dati sorgente, selezionare il driver Excel, e così via.

Sorge quindi la necessità di avere qualcoa di più automatica da poter lanciare senza eseguire tanti passaggi, non contando il fatto che alla prima partenza il Wizard dell'Enterprise Manager deve caricare la lista di tutti i driver e di tutti i server e quindi impiega sempre un pò di tempo.

Query Secca dal Query Analyzer

Esiste un modo per esportare via query analyzer in modo velocissimo.

exec master..xp_cmdshell 
'bcp "select * from tabella1 join tabella2 eccetera eccetera" queryout "c:\nomefile.csv" -c -C ANSI -t";" -U"nomeutentedb" -P"passwordutentedb"'

Il comando bcp può essere anche lanciato da prompt del dos, ed è possibile anche specificare un server diverso con l'opzione -S.

Questa soluzione ha però non poche limitazioni: non è possibili inserire il nome del campo, il file generato non è un vero file Excel ma un file di testo formattato in modo tale da essere visualizzato corretamente anche in Excel, la query deve essere scritta in una sola riga altrimenti il query analizer o il prompt del dos lanciano un messaggio di errore, e altro ancora.

Codice Visual Basic

Ed ecco la soluzione finale che ho creato con poche righe di codice Visual Basic. Un programmino che chiaramente ho chiamato SQL2Excel, che prende in input la connessione al database, quindi qualsiasi tipo di database, anche un fonte dati ODBC. Un altro parametro input è il percorso del file output xls, e poi la query (che può anche essere una mega query scritta su più righe), il tutto inserito in un file di testo. Quello che segue è un esempio di file da compilare.

Provider=SQLOLEDB.1;Password=miapassword;Persist Secutiry Info=True;User ID=sa;Initial Catalog=nomedatabase;Data Source=localhost
c:\pippo.xls
select * 
from tabella1 T1
join tabella2 T2 on T1.campo1 = T2.campo1 
where T2.campo3 = 1

Nella prima riga c'è la stringa connessione al database che come detto può anche essere Oracle o DB2 o qualasiasi fonte ODBC, nella seconda ci va il percorso completo del file (se il file esiste già viene cancellato senza preavviso), e di seguito nelle linee successive ci va la query che magari abbiamo testato e ottimizzato con il query analyzer.

Download SQL2Excel

Ecco le quattro righe di codice rigorosamente non commentato che permetto di creare un foglio excel a partire da una query sql.

Option Explicit

Sub Main()
    On Error GoTo errore
    Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim sql As String
    Dim fileConf As String
    Dim connectionString As String
    Dim logFile As TextStream, errore As String

    Dim fileExcel As String
    Dim i As Integer
    Dim fs As Scripting.FileSystemObject, f As TextStream
    
    
    Set fs = CreateObject("Scripting.FileSystemObject")
    
    If Len(Trim(Command)) = 0 Then
        If Not fs.FileExists(App.Path & "/default.txt") Then
            MsgBox ("E' necessario specificare il nome del file da linea di comando oppure creare un file default.txt nella stessa directory dell'eseguibile")
            End
        Else
            fileConf = App.Path & "/default.txt"
        End If
    Else
        If Not fs.FileExists(fileConf) Then
            MsgBox ("Impossibile trovare il file spcificato")
            End
        Else
            fileConf = Trim(Command)
        End If
    End If
    Set f = fs.OpenTextFile(fileConf, ForReading)
    connectionString = f.ReadLine
    fileExcel = f.ReadLine
    sql = f.ReadAll
    
    
    Set conn = CreateObject("ADODB.Connection")
    conn.CommandTimeout = 900 '15 minuti
    conn.Open connectionString
    
    Set rs = conn.Execute(sql)
    
    
    Dim objExcelApplication, objExcelBook, objExcelSheet
    
    If fs.FileExists(fileExcel) Then fs.DeleteFile fileExcel
    
    Set objExcelApplication = CreateObject("Excel.Application")
    objExcelApplication.Visible = False

    Set objExcelBook = objExcelApplication.Workbooks.Add
    Set objExcelSheet = objExcelBook.Worksheets(1)
    
    For i = 0 To rs.Fields.Count - 1
        If i < 26 Then
            objExcelSheet.Range(Chr(65 + i) & "1").Value = rs(i).Name
        Else
            objExcelSheet.Range(Chr(65 + (Fix(i / 26)) - 1) & Chr(65 + (i Mod 26)) & "1").Value = rs(i).Name
        End If
    Next
    objExcelSheet.Range("A1:" & IIf(i < 26, Chr(65 + i) & "1", Chr(65 + (Fix(i / 26)) - 1) & Chr(65 + (i Mod 26)) & "1")).Font.Bold = True

    objExcelSheet.Range("A2").CopyFromRecordset rs
    Set rs = Nothing
    Set objExcelSheet = Nothing

    objExcelBook.SaveAs (fileExcel)
    Set objExcelBook = Nothing
    objExcelApplication.Quit
    Set objExcelApplication = Nothing
    conn.Close
    Set conn = Nothing
    Exit Sub
errore:
    errore = Err.Description
    Set logFile = fs.OpenTextFile(App.Path & "/Err.log", ForAppending, True)
    logFile.WriteLine Now & " - " & errore & " - " & connectionString & " - " & fileExcel & " - " & sql
End Sub

come si evince dal codice il programma si aspetta come parametro il nome del file dove andare a prendere tutte le informazioni, altrimenti si aspetta un default.txt nella sua stessa directory. Questo permette anche di lanciare un batch multiplo che genera più fogli excel a aprtire da più query.

C'è anche l'accenno alla gestione di un log degli errori, appena avrò un pò di tempo, cioè mai, approfondirò il programma.