Thursday, 14 June 2012

Comma Separated Value (CSV)

In this article I am trying to demonstrate, how to generate comma separated value (CSV) from a particular table objects fields.
So I am going to demonstrate it as step by step that we can understand it properly.

Step-1 [ The base Table Objects ]
CREATE TABLE #tbl_Student
       (Roll  INT         NOT NULL IDENTITY(1,1) PRIMARY KEY,
        Sname VARCHAR(50) NOT NULL)
Step-2 [ Inserting Records ]
INSERT INTO #tbl_Student
VALUES ('Joydeep'),

SELECT * FROM #tbl_Student  

Roll        Sname
1           Joydeep
2           Sukamal
3           Sangram
4           Tuhin
5           Sudip
6           Bhola 
Step-3 [ Creating CSV format ]
SELECT   SUBSTRING((SELECT ',''' + a.Sname+''''
FROM     #tbl_Student  a
ORDER BY a.Sname
FOR XML PATH('')),2,200000) As RESULT;


It is easy but very useful.

Hope you like it.
Posted by: MR. JOYDEEP DAS


  1. You've demonstrated a comma separated pivot, not creation of a CSV file. Your solution will be truncated at 200000 characters.

    Try this:
    REPLACE(REPLACE(REPLACE([results].[name], '', ','), '', ''), '', '')
    SELECT '''' + [a].[Sname] + '''' as [name]
    FROM #tbl_Student [a]
    ORDER BY [a].[Sname]
    FOR XML PATH ('')
    ) [results] ([name])

  2. The easiest way to create a CSV file from SQL Server is to use BCP. Just submit a query and get the results back.