Export to CSV

Sql Server Table export to CSV format



ALTER PROCEDURE [dbo].[File_Export]
@TableName nvarchar(500),
@FileName nvarchar(1000),
@Result int  = NULL OUTPUT
AS
/*
Method: Exports table rows to .csv file (Comma Separated Values file)
Input Parameters:
@TableName nvarchar(100)
@FileName nvarchar(1000)
Output Parameters:
@Result int  
- Return values (@Result)
 1 the operation completed successfuly
-1 error
*/

--Exec dbo.[File_Export] '[dbo].[IncidentsHistory]', 'D:\Test.csv'

DECLARE @ErrorID int
DECLARE @ErrorMessage nvarchar(100)
DECLARE @FS int, @OLEResult int, @FileID int

-- verify table @TableName existence
IF NOT exists (SELECT * FROM INFORMATION_SCHEMA.Tables WHERE '['+ Table_Schema + '].[' + Table_Name + ']' = @TableName)
BEGIN
SET @ErrorMessage = 'Invalid table name ' + @TableName
Print @ErrorMessage
RETURN -1
END

-- create FileSystemObject
EXEC @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT
IF @OLEResult <> 0
BEGIN
SET @ErrorMessage = 'Fail to create FileSystemObject'
Print @ErrorMessage
RETURN -1
END

-- open(create) file
EXEC @OLEResult = sp_OAMethod @FS, 'OpenTextFile', @FileID OUT, @FileName, 2, 1
IF @OLEResult <> 0
BEGIN
SET @ErrorMessage = 'Fail to open file ' + @FileName
Print @ErrorMessage
RETURN -1
END

DECLARE @FirstColumn bit
DECLARE @ColumnOrder varchar(500)
DECLARE @ColumnName varchar(500)
DECLARE @ColumnType varchar(100)
DECLARE @ColumnsCount int
DECLARE @SelectCommand nVarChar(Max)
DECLARE @ColumnList nVarChar(Max)
DECLARE @TextLine varchar(Max)
DECLARE cColumns CURSOR SCROLL FOR
SELECT Distinct Ordinal_Position As colorder, Column_Name As colName, Data_Type
FROM INFORMATION_SCHEMA.Columns
WHERE '['+ Table_Schema + '].[' + Table_Name + ']' = @TableName
Order by Ordinal_Position


SET @SelectCommand = 'SELECT '
SET @FirstColumn = 1
SET @ColumnList = ''

--Get table's columns & construct SELECT command string
OPEN cColumns
FETCH NEXT FROM cColumns INTO @ColumnOrder, @ColumnName, @ColumnType
WHILE @@FETCH_STATUS = 0
BEGIN

IF @FirstColumn = 0 SET @SelectCommand = @SelectCommand + ' + '' '' + '
SET @FirstColumn = 0
-- binary fields cannot be exported to text file
IF @ColumnType = 'image' OR @ColumnType = 'varbinary' OR @ColumnType = 'binary'
SET @SelectCommand = @SelectCommand + '''NULL'''
ELSE
SET @SelectCommand = @SelectCommand + ' ISNULL(CAST([' + @ColumnName + '] AS varchar(100)), ''NULL'')'

SET @ColumnList = @ColumnList + '[' + @ColumnName + '],'
FETCH NEXT FROM cColumns INTO @ColumnOrder, @ColumnName, @ColumnType
END
CLOSE cColumns
DEALLOCATE cColumns
SET @SelectCommand = @SelectCommand + ' FROM ' + @TableName
-- create
EXEC('DECLARE cTable CURSOR SCROLL FOR ' + @SelectCommand)
IF @@Error <> 0
BEGIN
SET @ErrorMessage = 'Fail to open table ' + @TableName
Print @ErrorMessage
RETURN -1
END

SET @TextLine = Reverse(SubString(Reverse(@ColumnList), 2, Len(@ColumnList)))
SET @TextLine = REPLACE(@TextLine, ',', '","')
SET @TextLine = '"' + @TextLine + '"'
-- write line into text file
EXEC @OLEResult = sp_OAMethod @FileID, 'WriteLine', Null, @TextLine
IF @OLEResult <> 0
BEGIN
SET @ErrorMessage = 'Fail to write into file ' + @FileName
Print @ErrorMessage
RETURN -1 -- if one fail to write one line into file is useless to try again
END


-- get from every row in table @TableName columns and insert into text file (csv)
OPEN cTable
FETCH NEXT FROM cTable INTO @TextLine
WHILE @@FETCH_STATUS = 0
BEGIN
-- remove new line chars
SET @TextLine = REPLACE(@TextLine, CHAR(13), '')
SET @TextLine = REPLACE(@TextLine, CHAR(10), '')
-- put inside string value two " instead of one "
SET @TextLine = REPLACE(@TextLine, '"', '""')
-- replace separator with ,
SET @TextLine = REPLACE(@TextLine, ' ', '","')
SET @TextLine = '"' + @TextLine + '"'
SET @TextLine = REPLACE(@TextLine, 'NULL', '')

-- write line into text file
EXEC @OLEResult = sp_OAMethod @FileID, 'WriteLine', Null, @TextLine
IF @OLEResult <> 0
BEGIN
SET @ErrorMessage = 'Fail to write into file ' + @FileName
Print @ErrorMessage
RETURN -1 -- if one fail to write one line into file is useless to try again
END

FETCH NEXT FROM cTable INTO @TextLine
END
CLOSE cTable
DEALLOCATE cTable

-- destroy objects
EXEC @OLEResult = sp_OAMethod @FileID, 'Close'
EXEC @OLEResult = sp_OADestroy @FileID
EXEC @OLEResult = sp_OADestroy @FS

RETURN 1