go backarticles

Articles of SQLschool.gr Team

Export data to Excel File using pure T-SQL

Antonios Chatzipavlis

O SQL Server έχει αρκετά εργαλεία για να κάνεις export & import data. Από τα απλά T-SQL BULK INSET ή το κλασσικό BCP μέχρι τα DTS (Data Transformation Services)  και σήμερα τα SSIS ( SQL Server Integration Services).

Όμως υπάρχουν αρκετές περιπτώσεις που δεν θα ήθελες να εμπλακείς με αυτά.

Ένα σενάριο το οποίο κάποια στιγμή σε ένα project μου έτυχε ήταν η απαίτηση να πατάει ο χρήστης ένα κουμπί και να γίνονται τα δεδομένα export σε Excel αρχείο.

Για να δούμε λοιπόν την υλοποίηση του χωρίς να χρησιμοποιήσω κάτι από τα παραπάνω αλλά κάνοντας χρήση αγνού T-SQL και της δυνατότητας που έχει ο SQL Server με τους Linked Servers.

Βασική προϋπόθεση για να μπορέσει η λύση να δουλέψει είναι να δημιουργήσεις ένα άδειο αρχείο excel στο server με συγκεκριμένο όνομα και σε συγκεκριμένο directory. Ας το πούμε empty.xls και ας το βάλουμε στο c:\temp. Αυτό θα λειτουργεί σαν template για τα αρχεία που δα δημιουργήσουμε παρακάτω.

Όμως δεν φτάνει μόνο αυτό.

Θα πρέπει να ανοίξω το excel αρχείο και να δώσω ένα όνομα στο φύλλο (πχ MyData).

Ακόμα θα πρέπει να γνωρίζω πόσα πεδία θα βάλω στο excel φύλλο πχ αν ξέρω ότι θα βάλω 2 πεδία θα πρέπει στα πρώτα 2 κελιά της πρώτης γραμμής να δώσω κάποιο όνομα πχ A,B.

Φτιάχνω την stored procedure που θα κάνει όλη την δουλειά

create proc usp_write2Excel (@fileName varchar(100),@NumOfColumns tinyint,@query varchar(200))

as

begin

declare @dosStmt varchar(200)

declare @tsqlStmt varchar(500)

declare @colList varchar(200)

declare @charInd tinyint

set nocount on

--
-- COLUMNS LIST CREATION

--
set @charInd=0

set @colList = 'A'

while @charInd < @NumOfColumns - 1

begin

set @charInd = @charInd + 1

set @colList = @colList + ',' + char(65+ @charInd)

end

-- CREATE MY EXCEL FILE BY COPING EXCEL TEMPLATE

set @dosStmt = ' copy c:\temp\empty.xls ' + @fileName

exec master..xp_cmdshell @dosStmt

-- Create a "temporary" linked server to that file in order to "Export" Data

EXEC sp_addlinkedserver 'ExcelSource','Jet 4.0','Microsoft.Jet.OLEDB.4.0',@fileName,NULL,'Excel 5.0'

-- construct a T-SQL statement that will actually export the query results-- to the Table in the target linked server

set @tsqlStmt = 'Insert ExcelSource...[MyData$] ' + ' ( ' + @colList + ' ) '+ @query

-- execute dynamically the TSQL statement

exec (@tsqlStmt)

-- drop the linked server

EXEC sp_dropserver 'ExcelSource'

set nocount off

end

GO

Execute sp

exec usp_write2Excel 'c:\temp\Customers.xls',2,'select customerid,companyname from northwind.dbo.customers'


 
Αυτό ήταν έχεις τα δεδομένα σου σε Excel!!!

Antonios Chatzipavlis

Antonios Chatzipavlis

Antonios is a Data Solutions Consultant and Trainer. He has been working in IT since 1988. In his career, he has worked as senior developer, IT Manager, Solutions Architect and IT Consultant. Since 1995 he has been devoted on new technologies and software development tools, mainly by Microsoft, either by training company staff and colleagues or assisting them in design, development and implementation as a consultant or chief developer. He has focused in Databases and Data Science since 1995. He specialized in Microsoft SQL Server since version 6.0 in areas like SQL Server Internals, Database Design and Development, Business Intelligence and in 2010 he has started working with Azure Data Platform, NoSQL databases, Big Data Technologies and Machine Learning. He is an active member of many IT communities in Greece, answering colleagues' questions and writing articles in his web site. He is the owner of SQLschool.gr which is a community portal with a lot of information about Microsoft SQL Server. He has been a Microsoft Certified Trainer (MCT) since 2000. Microsoft honored him as MVP on Data Platform due to his activities in SQL Server since 2010. He holds a large number of Microsoft Certifications and Microsoft SQL Server Certifications since version 6.5.


Relative Articles

Comments

user-gravatar

On 05 Nov 2012 @ 4:18 PM Γιώργος Ροζάκης wrote:

Αντώνη καλησπέρα,προσπαθώ να κάνω export data από έναν πίνακα σε αρχείο .xlsΈτρεξα των κώδικα που έχεις εδώ: http://www.sqlschool.gr/blog/export-data-to-excel-file-using-pure-t-sql-91.aspxΈχω δημιουργήσει το αρχείο excel ακριβώς όπως λες, δημιουργήθηκε σωστά και η stored procedure χωρίς σφάλμα.Όταν πάει να τρέξει τις δύο τελευταίες γραμμές όμως μου βγάζει αφενός μεν για την εντολή Execute sp ότι:Msg 2812, Level 16, State 62, Line 1Could not find stored procedure 'sp'.και αφετέρου για την: exec usp_write2Excel 'c:\temp\Furnace.xls',2,'select Furnace_ID,FurnaceHow from extrusion.dbo.furnace'Msg 15028, Level 16, State 1, Procedure sp_MSaddserver_internal, Line 89The server 'ExcelSource' already exists.OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "ExcelSource" returned message "Cannot start your application. The workgroup information file is missing or opened exclusively by another user.".Msg 7399, Level 16, State 1, Line 1The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "ExcelSource" reported an error. Authentication failed.Msg 7303, Level 16, State 1, Line 1Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "ExcelSource".Τι κάνω λάθος; Stored procedure σκέτο 'sp' όντως δεν υπάρχει στη βάση μου πάντως...

Leave your comment

COMMENT

FULL NAME

EMAIL ADDRESS

We use Gravatar

WEB SITE



captcha


 

Newsletter

If you want to receive updates from us subscribe below with your email.
Follow us in
PASS chapter logo

The Official PASS Local Group for Greece

About us Contact us Terms of Use Privacy Sing in Register
sql school greece logo
© 2010-2020 All rights reserved

This site uses cookies for operational and analytics purposes only. By continuing to browse this site, you agree to their use.