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
            (Sname)
VALUES ('Joydeep'),
       ('Sukamal'),
       ('Sangram'),
       ('Tuhin'),
       ('Sudip'),
       ('Bhola')


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;

RESULT
'Bhola','Joydeep','Sangram','Sudip','Sukamal','Tuhin'

It is easy but very useful.

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

3 comments:

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

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

    ReplyDelete
  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.

    ReplyDelete