Thursday, June 25, 2015

SQL- How to write or create the data into a file

There are a lots of ETL tools available to fetch the data from your database into the file. But these ETL tools are not possible for each one and sometimes, we need to create a file to store the data from our database in SQL and share these data files to business users/ data analysists/sales team for the data analysis to take the right decision. 

In this section, we will learn that how can we write the data into a file (Text or CSV) with the help of SQL.


Note:  'Ole Automation Procedures' component should be turned on as part of the security configuration for your server and a system administrator can enable the use of 'Ole Automation Procedures' by using sp_configure.

If these components are turned off then you can use the following command in your SQL Server Management Studio:

sp_configure 'Ole Automation Procedures', 1
GO
RECONFIGURE;
GO
sp_configure 'show advanced options', 1
GO
RECONFIGURE;

After the above command you will get the following message in SQL-
Configuration option 'Ole Automation Procedures' changed from 0 to 1. Run the RECONFIGURE statement to install.
Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.


We will create a temp table to store the values as given below:

Use Tempdb
Go

----- create temp table
CREATE TABLE EMPLOYEEMASTER
(EMPID INT, EMPNAME VARCHAR(20), SALARY INT)

---- insert data into temp table
INSERT INTO EMPLOYEEMASTER VALUES
(101, 'RYAN ARJUN', 25000),
(102, 'ARJUN PAUL', 35000),
(103, 'RYAN GRAY', 15000),
(104, 'BILL SMITH', 25500),
(105, 'GARY KILL', 45000),
(106, 'SMITH WILL', 55000)

----- select data from the temp table
SELECT * FROM EMPLOYEEMASTER;

EMPID
EMPNAME
SALARY
101
RYAN ARJUN
25000
102
ARJUN PAUL
35000
103
RYAN GRAY
15000
104
BILL SMITH
25500
105
GARY KILL
45000


Now our data is ready to use. We need some local variables to hold the information which are necessary to write the data into the file as given below:
--- declare local variable to hold the values
DECLARE @FS int,
@OLEResult int,
@FileID int,
@filename varchar(100),
@oneline varchar(300),
@itemstring varchar(5000),
@uncpather varchar(200),
@uncfile varchar(200),
@datestr varchar(12)


We need to set the file location in the filename variable. If file exists then we need to delete that file from the disk. We need to open the file and write the header first row as the header into the file as shown below:

--- set the file name into the temp folder
set @filename = 'c:\temp\Employees.txt'

--- create the file object
EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT
                               
IF @OLEResult <> 0 PRINT 'Scripting.FileSystemObject'

--- check the file existence
--- if file exists than delete the file
EXECUTE @OLEResult = sp_OAMethod @FS, 'FileExists', @FileID out, @filename
                                IF @OLEResult = 0
                                begin
                                PRINT 'FileExists'

--- delete the old file if exist on the location
EXECUTE @OLEResult = sp_OAMethod @FS, 'DeleteFile', Null, @filename

---- print msg for confirmation
                                IF @OLEResult = 0
                                PRINT 'FileDeleted'

                                end

--Open a file to write the data into the file
execute @OLEResult = sp_OAMethod @FS, 'OpenTextFile', @FileID OUT, @FileName, 8, 1
                                IF @OLEResult = 0

--- set the file header columns here
execute @OLEResult = sp_OAMethod @FileID, 'WriteLine', Null, 'Id, Name, Salary'

--- check the file's open mode
                                IF @OLEResult = 0
                                PRINT 'OpenTextFile'


Now we need a cursor to hold the values from the source and write the data into the file row by row as shown below:
--- declare cursor for fetch value from the target table
--- and write the cursor data into the targetted file
Declare @Emp_Cursor Cursor

--- set value into the cursor with the separeted comma between each column,
--- where comma is used to identify as a new column into the file
Set @Emp_Cursor = Cursor FOR
SELECT  Convert(varchar,EMPID)  +',"'+EMPNAME +'",'+ Convert(varchar,SALARY)  FROM EMPLOYEEMASTER
                                ORDER BY EMPID
                               
--- open cursor for the next step to insert data into the target file
OPEN @Emp_Cursor

Now, we need to write the data into the file row by row by the help of the cursor as shown below:
--- open cursor for the next step to insert data into the target file
OPEN @Emp_Cursor

Fetch next from @Emp_Cursor into @oneline

--- fetch records from the cursor as well as write the data into the opened file
--- line by line
While (@@FETCH_STATUS =0)
Begin
                                 
---- if exists
if @@FETCH_STATUS = 0
print @oneline

----- write the file
execute @OLEResult = sp_OAMethod @FileID, 'WriteLine', Null, @oneline

---- Fetch the next line
Fetch next from @Emp_Cursor into @oneline

End

---- destroy the file Id
EXECUTE @OLEResult = sp_OADestroy @FileID

EXECUTE @OLEResult = sp_OADestroy @FS

--- close the cursor
Close @Emp_Cursor

--- destroy the cursor variable here
Deallocate @Emp_Cursor


After follow the above steps, execute the query and our file is ready on the file location.
At a Glance: this will be the final Query to create a file in SQL as shown below:

Use Tempdb
Go

----- create temp table
CREATE TABLE EMPLOYEEMASTER
(EMPID INT, EMPNAME VARCHAR(20), SALARY INT)

---- insert data into temp table
INSERT INTO EMPLOYEEMASTER VALUES
(101, 'RYAN ARJUN', 25000),
(102, 'ARJUN PAUL', 35000),
(103, 'RYAN GRAY', 15000),
(104, 'BILL SMITH', 25500),
(105, 'GARY KILL', 45000),
(106, 'SMITH WILL', 55000)

----- select data from the temp table
SELECT * FROM EMPLOYEEMASTER;

--- declare local variable to hold the values
DECLARE @FS int,
@OLEResult int,
@FileID int,
@filename varchar(100),
@oneline varchar(300),
@itemstring varchar(5000),
@uncpather varchar(200),
@uncfile varchar(200),
@datestr varchar(12)

--- set the file name into the temp folder
set @filename = 'c:\temp\Employees.txt'

--- create the file object
EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT
                               
IF @OLEResult <> 0 PRINT 'Scripting.FileSystemObject'

--- check the file existence
--- if file exists than delete the file
EXECUTE @OLEResult = sp_OAMethod @FS, 'FileExists', @FileID out, @filename
                                IF @OLEResult = 0
                                begin
                                PRINT 'FileExists'

--- delete the old file if exist on the location
EXECUTE @OLEResult = sp_OAMethod @FS, 'DeleteFile', Null, @filename

---- print msg for confirmation
                                IF @OLEResult = 0
                                PRINT 'FileDeleted'

                                end

--Open a file to write the data into the file
execute @OLEResult = sp_OAMethod @FS, 'OpenTextFile', @FileID OUT, @FileName, 8, 1
                                IF @OLEResult = 0

--- set the file header columns here
execute @OLEResult = sp_OAMethod @FileID, 'WriteLine', Null, 'Id, Name, Salary'

--- check the file's open mode
                                IF @OLEResult = 0
                                PRINT 'OpenTextFile'

--- declare cursor for fetch value from the target table
--- and write the cursor data into the targetted file
Declare @Emp_Cursor Cursor


--- set value into the cursor with the separeted comma between each column,
--- where comma is used to identify as a new column into the file
Set @Emp_Cursor = Cursor FOR
SELECT  Convert(varchar,EMPID)  +',"'+EMPNAME +'",'+ Convert(varchar,SALARY)  FROM EMPLOYEEMASTER
                                ORDER BY EMPID
                               
--- open cursor for the next step to insert data into the target file
OPEN @Emp_Cursor

Fetch next from @Emp_Cursor into @oneline

--- fetch records from the cursor as well as write the data into the opened file
--- line by line
While (@@FETCH_STATUS =0)
Begin
                                 
---- if exists
if @@FETCH_STATUS = 0
print @oneline

----- write the file
execute @OLEResult = sp_OAMethod @FileID, 'WriteLine', Null, @oneline

---- Fetch the next line
Fetch next from @Emp_Cursor into @oneline

End

---- destroy the file Id
EXECUTE @OLEResult = sp_OADestroy @FileID

EXECUTE @OLEResult = sp_OADestroy @FS

--- close the cursor
Close @Emp_Cursor

--- destroy the cursor variable here
Deallocate @Emp_Cursor

----- drop the temp table
drop table EMPLOYEEMASTER;




By using the above script, you can create raw data file and pull the data from your database and store the file on the shared location or send to the respective persons. 
Output of the file as Text File

 Output of the file as CSV File
Conclusion
By using these features of SQL, we can schedule these script to write the data into text or csv file and share these files to the business team.

1 comment:

  1. What if we want to add multiple worksheets into single excel file?.
    Kindly give the change required in above code.

    ReplyDelete

Popular Posts