A comma-separated values (CSV) file is a simple text format for tabula data. It is a simple and widely used to transfer information from a database to others program such as spreadsheet. I know that you can use BCP (Bulk Copy Program) command to create CSV files. You can also use DTS or SSIS to do that. But Now, I would like to show the alternative way to generate csv by using Stored Procedure.

First, you need to create the storedprocedure in sql server. Here is the code.

CREATE PROCEDURE [dbo].[sp_get_csv]
(
	@TableName VARCHAR(255),
	@ColumsList VARCHAR(MAX)='*',
	@UseQuote BIT=1,
	@Delimiter VARCHAR(5)=',',
	@CRLF VARCHAR(5)=''
)
AS
BEGIN
	DECLARE @CSV AS VARCHAR(MAX)
	DECLARE @SQL AS NVARCHAR(MAX)
	DECLARE @TBL As TABLE([VALUE] VARCHAR(MAX))
	DECLARE @STR_TMP VARCHAR(50)
IF(@CRLF = '')
BEGIN
	SET @CRLF = CHAR(13) + CHAR(10)
END
IF(@ColumsList = '*')
BEGIN
SET @ColumsList=''

SELECT @ColumsList = @ColumsList +
'ISNULL(CAST(' +[Name] + ' AS VARCHAR){COMMA}'''')' + ','
FROM SYSCOLUMNS WHERE ID =
(SELECT ID FROM SYSOBJECTS WHERE TYPE = 'U' AND [Name] = @TableName)
SET @ColumsList = SUBSTRING(@ColumsList,0,LEN(@ColumsList))
END

SET @CSV = ''
IF(@UseQuote=0)
BEGIN
	SET @STR_TMP = ' + ''' + @Delimiter + ''' + '
	SET @ColumsList = REPLACE(@ColumsList,',',@STR_TMP)
	SET @SQL = 'SELECT (' + @ColumsList + ') AS [VALUE] FROM ' + @TableName
END
ELSE
BEGIN
	SET @STR_TMP = ' + ''"'' + ''' + @Delimiter + ''' + ''"'' + '
	SET @ColumsList = REPLACE(@ColumsList,',',@STR_TMP)
	SET @SQL = 'SELECT (''"'' + ' + @ColumsList + ' + ''"'') AS [VALUE] FROM ' + @TableName
END

	SET @SQL = REPLACE(@SQL,'{COMMA}',',')
	INSERT INTO @TBL EXEC sp_executesql @SQL
	SELECT @CSV = @CSV + VALUE + @CRLF FROM @TBL

	IF(LEN(@CSV) > LEN(@CRLF))
		SET @CSV = SUBSTRING(@CSV,0,LEN(@CSV)-LEN(@CRLF)+1)

	SELECT @CSV AS CSV
END

Parameters

There are five parameters in that storedprocedure. But just the first one is compulsory. All others are optional.
  • @TableName VARCHAR(255)
  • @ColumsList VARCHAR(MAX)=’*'
  • @UseQuote BIT=1
  • @Delimiter VARCHAR(5)=’,'
  • @CRLF VARCHAR(5)=”
@TableName
  • Name of the table that you want to generate csv
@ColumsList
  • Columns you want to include. ( Defaut is * )
  • Note: If don't use default value, you may need to convert the input column to varchar explicitly
@UseQuote
  • Add quotes " at the start and end of the value or not.
  • output of passing 1 = "a","b","c"
  • output of passing 0 = a,b,c
@Delimiter
  • Default is comma ","
@CRLF
  • Default is Char(13)+Char(10)

How to use ?

If lets say we has following table named Employees.

ID Name JoinedDate
1 A 2010-11-01 23:18:49.270
2 B 2010-11-01 23:18:49.270
3 C 2010-11-01 23:18:49.270

Execute the query

exec sp_get_csv 'Employees'
Here is output
"1","A","Nov  1 2010 11:18PM"
"2","B","Nov  1 2010 11:18PM"
"3","C","Nov  1 2010 11:18PM"
Execute with specific columns
exec sp_get_csv 'Employees', 'cast(id as varchar),cast(joineddate as varchar)'
Output will be;
"1","Nov  1 2010 11:18PM"
"2","Nov  1 2010 11:18PM"
"3","Nov  1 2010 11:18PM"
Try with others parameters. Have fun.



author's photo

Author : Blue Cloud

Just a normal software developer who love to write code. Developing professional software since 1999. After working in the software industry for many years, I've started my own website to share knowledges and experiences.

COMMENTS
blog comments powered by Disqus