Πώς μπορούμε να εξάγουμε καθημερινά δεδομένα από τον SQL Server σε Excel και να τα στέλνουμε με email

• Σχετικά με
• τελευταίες δημοσιεύσεις

Stratos Matzouranis

Το ταξίδι άρχισε το 2008 όπου ξεκίνησα να εργάζομαι επίσημα στον τομέα της τεχνολογίας πληροφορικής (IT). Ξεκινώντας το πρώτο εξάμηνο της σχολής κατάλαβα μια ιδιαίτερη έλξη προς τις βάσεις δεδομένων και τους αυτοματισμούς.Έχω ασχοληθεί με βάσεις δεδομένων όπως Microsoft SQL Server / Oracle Database, ανάλυση δεδομένων και αυτοματισμούς με τη χρήση γραμμής εντολών (CLI), Visual Basic for Applications και Python. Μέσα από τα χρόνια εμπειρίας εξέλιξα αυτές τις δυνατότητες ώστε να μπορώ να κάνω τη ζωή μου πιο εύκολη. Για μένα ο σκοπός του κάθε IT guy αλλά και κάθε υπαλλήλου γραφείου είναι το να έχει τις γνώσεις ώστε μέσω εργαλείων να μπορεί να δουλεύει λίγο αλλά να παράγει πολύ. Μέσα από αυτό τον ιστότοπο του DataPlatform.gr προσπαθώ να προσφέρω γνώσεις και να προτείνω λύσεις σε καθημερινά προβλήματα.

Πιστοποιήσεις:

Τελευταίες δημοσιεύσεις από Stratos Matzouranis (Προβολή όλων)

• Πώς μπορούμε να συνδέσουμε Oracle Database με SQL Server με χρήση Oracle Gateway σε Windows - 5 Απρίλιος 2024
• Πώς μπορούμε να λαμβάνουμε email κάθε φορά που ξεπερνάει ένα όριο η μνήμη PGA σε βάση δεδομένων της Oracle - 1 Μάρτιος 2024
• Πώς κάνουμε μαζικά detach / attach βάσεις δεδομένων στον SQL Server - 2 Φεβρουάριος 2024

Στις καθημερινές εργασίες των εταιριών είναι συχνή ανάγκη να πρέπει να εξάγονται καθημερινά δεδομένα από τη βάση και να δίνονται σε κάποιο Excel.

Σε αυτά τα Excel συνήθως θέλουμε να έχουμε κάποιο συγκεκριμένο όνομα μαζί με ημερομηνία και να αποθηκεύονται σε συγκεκριμένο path. Πολύ χρήσιμο είναι να στέλνονται αυτά τα Excel απευθείας με email από τον SQL Server τον ίδιο.

Στο άρθρο θα αναλύσουμε βήμα βήμα τον τρόπο και το τι απαιτείται για να γίνουν όλα τα παραπάνω χωρίς την χρήση πακέτων SSIS.

Τα προαπαιτούμενα

Θα πρέπει να έχουμε προεγκαστήσει έναν provider από την Microsoft που θα μας επιτρέπει να γράφουμε τα δεδομένα μας από τη βάση στο Excel. Μπορούμε να κατευάσουμε την 12 έκδοση από εδώ.

Πρέπει το account που σηκώνει το service account του SQL Server να έχει δικαιώμα να γράψει στο path αυτών τον αρχείων στο λειτουργικό όπως επίσης να είναι sysadmin στο instance.

Τέλος θα πρέπει να έχουμε τρέξει τα παρακάτω στο instance που θα μας επιτρέψουν να τρέξουμε το T-SQL script να γράφει στο Excel:

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO
EXECUTE sp_configure 'xp_cmdshell', 1;  
GO
RECONFIGURE;
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
GO

Για να δούμε τι providers έχουν εγκατασταθεί στο instance:

EXEC master.dbo.sp_MSset_oledb_prop

Το παράδειγμα

Έχουμε έναν πίνακα customers στο οποίο έχουμε εγγραφές. Θέλουμε να τον εξάγουμε σε ένα Excel με χρώματα στους τίτλους και μορφοποιήσεις με όνομα customers_yyyymmdd.xlsx. Σαν επόμενο βήμα θέλουμε να στέλνουμε αυτό το Excel με ένα email σε κάποιον παραλήπτη.

Οπότε για το παράδειγμα έχουμε περάσει μόνο 3 εγγραφές στον πίνακα.

insert into mig_db.dbo.customers
values
('Stratos'),
('Nikos'),
('Giorgos');

select * from mig_db.dbo.customers;

Για να έχουμε τις μορφοποιήσεις του Excel που θέλουμε, δηλαδή Bold, περιθώρια, κίτρινα κελιά φτιάχνουμε ένα template.xlsx στο οποίο θα έχουμε κάνει όλες τις μορφοποιήσεις αυτές και το έχουμε αποθηκεύσει στο path που θα εξάγονται τα αρχεία.

Το επόμενο βήμα είναι να έχουμε βάλει στο script μας να κάνει copy το template σε καινούργιο αρχείο με το όνομα που θα θέλουμε τελικός να έχει π.χ. customers_20210101.xlsx.

declare @Sql varchar(200)
set @Sql='COPY C:\Share\template.xlsx C:\Share\customers_'+convert(varchar(15),getdate(),112)+'.xlsx'
print @Sql
exec xp_cmdshell @Sql
GO

Οπότε θα δούμε αμέσος ότι δημιουργήθηκε το αρχείο στον φάκελο μας το οποίο ακόμα δεν περιέχει εγγραφές.

Στο επόμενο βήμα θα γίνει η μεταφορά τον εγγραφών στο Excel. Εκεί θα πρέπει:

• στην μεταβλητή @importq να βάλουμε το query που θα διαβάζει από τον πίνακα
• στην μεταβλητή @dbfile να βάλουμε το path του αρχείου με το όνομα που θα έχει όπως κάναμε στο προηγούμενο βήμα με την εντολή copy (C:\Share\customers_’+convert(varchar(15),getdate(),112)+’.xlsx’)
• Θα πρέπει να αλλάξουμε το όνομα στο template του φύλλου του Excel σε Sheet1 (αν δεν είναι ήδη) αλλιώς θα πρέπει να το αλλάξουμε στη μεταβλητή @dbfile στο select * from [Sheet1$]

USE mig_db
GO
set ANSI_NULLS ON
GO
set QUOTED_IDENTIFIER ON
GO
declare @dbfile varchar(2000), @importq varchar(2000)
set @importq = 'select * from dbo.customers'
set @dbfile = 'INSERT INTO OPENROWSET(''Microsoft.ACE.OLEDB.12.0'''+','+'''Excel 12.0;Database=C:\Share\customers_'+convert(varchar(15),getdate(),112)+'.xlsx'''+','+'''select * from [Sheet1$]'''+')'+@importq
print @dbfile
exec(@dbfile)
go

Αυτό ήταν, αν ανοίξουμε τώρα το Excel θα δούμε τις εγγραφές με την μορφοποίηση που θέλουμε.

Σε προηγούμενο άρθρο είχαμε δει πως ενεργοποιούμε το Database Mail στον SQL Server. Αφού κάνουμε αυτά τα βήματα με το παρακάτω script μπορούμε να στείλουμε το Excel και με Email.

declare @dbfile2 varchar(1000)
set @dbfile2 = 'C:\Share\customers_'+convert(varchar(15),getdate(),112)+'.xlsx'
EXEC msdb.dbo.sp_send_dbmail  
@profile_name = 'gmail',  
@recipients = 'account@dataplatform.gr',  
@subject = 'Customers Report',  
@body = 'Here is the daily customer report.',
@file_attachments = @dbfile2;
print @dbfile2

Οπότε αν πάνε όλα καλά θα έρθει το Email όπως φαίνεται στην από κάτω εικόνα.

Συγκεντρωτικά ο κώδικας:

declare @Sql varchar(200)
set @Sql='COPY C:\Share\template.xlsx C:\Share\customers_'+convert(varchar(15),getdate(),112)+'.xlsx'
print @Sql
exec xp_cmdshell @Sql
GO

set ANSI_NULLS ON
GO
set QUOTED_IDENTIFIER ON
GO
declare @dbfile varchar(2000), @importq varchar(2000)
set @importq = 'select * from mig_db.dbo.customers'
set @dbfile = 'INSERT INTO OPENROWSET(''Microsoft.ACE.OLEDB.12.0'''+','+'''Excel 12.0;Database=C:\Share\customers_'+convert(varchar(15),getdate(),112)+'.xlsx'''+','+'''select * from [Sheet1$]'''+')'+@importq
print @dbfile
exec(@dbfile)
go

declare @dbfile2 varchar(1000)
set @dbfile2 = 'C:\Share\customers_'+convert(varchar(15),getdate(),112)+'.xlsx'
EXEC msdb.dbo.sp_send_dbmail  
@profile_name = 'gmail',  
@recipients = 'account@dataplatform.gr',  
@subject = 'Customers Report',  
@body = 'Here is the daily customer report.',
@file_attachments = @dbfile2;
print @dbfile2

Και αν θέλω όλη αυτή η διαδικασία να γίνεται αυτόματα κάθε μέρα μια συγκεκριμένη ώρα;

Μπορούμε απλά να φτιάξουμε ένα Job στον SQL Server Agent.

Η διαδικασία είναι να κάνουμε δεξί κλικ New, Job… , Steps, New…, να δώσουμε ένα όνομα στο step, να επιλέξουμε τη βάση που έχει τα δεδομένα και να προσθέσουμε τα sql scripts που φτιάξαμε πριν, πατάμε ΟΚ και τέλος στην καρτέλα Schedules να ορίσουμε το πότε θα τρέχει.

Υπάρχει εναλλακτικός πιο άμεσος τρόπος;

Σε περίπτωση που δεν μας νοίαζει η εμφάνιση του Excel με bold τίτλους, χρώματα κτλ, δεν μας νοιάζει να κρατάμε αυτόματα τα reports και να τους δίνουμε όνομα με την ημερομηνία δυναμικά, τότε μπορούμε κατά την εκτέλεση της procedure που στέλνει το email msdb.dbo.sp_send_dbmail να ορίσουμε απευθείας το query που θέλουμε να εκτελέσουμε και να το επισυνάψει ως attachment όπως με τον προηγούμενο τρόπο:

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'gmail',
@recipients = 'account@dataplatform.gr',
@query = '
set nocount on;
USE mig_db;
go
select * from dbo.customers
go',
@subject = 'Customers Report',
@body = 'Here is the daily customer report.',
@query_attachment_filename='customers.xls',
@query_result_header = 1,
@query_result_width = 256,
@query_result_separator = ',',
@exclude_query_output = 1,
@append_query_error = 1,
@query_no_truncate = 0,
@query_result_no_padding = 1,
@attach_query_result_as_file = 1;