Estrazione dei dati in Excel

Home | SQL Server

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.

    Home