Export to CSV with Column Header having comma separated values in Some columns

• Jai Gupta

  SSC Veteran

  Points: 209

  More actions

  June 11, 2009 at 2:44 am

  #206014

  Hi,

  I have to export the tables (sql 2000 and sql 2005) in CSV format with column header. The problem is some columns have comma separated values. How to handle this?

  regards

  Jai

• WayneS

  SSC Guru

  Points: 95461

  More actions

  June 11, 2009 at 3:26 am

  #1007852

  I assume you have data like:

  declare @test-2 table (colA int, colB varchar(50))

  insert into @test-2

  select 1, 'plain text' union

  select 2, 'csv,data,in,this,row'

  select * from @test-2

  A properly formatted CSV file is not just simply putting a comma between the columns. It would also put double-quotes around the data if the data contains a comma. See http://en.wikipedia.org/wiki/Comma-separated_values.

  So, you could handle this with:

  select ColA, ColB = QuoteName(ColB, CHAR(34)) -- put this field inside double-quotes.

  from @test-2

  At this point, you have the data ready to be put into a csv file. Probably the easiest way to generate the csv file is with the BCP utility (see BOL for a description of how to use this). But, this doesn't create your column header.

  There are two ways to do this. Either way creates a file with just this information:

  1. Create the file from your command prompt: type ColA,ColB > file.txt

  2. BCP out to the file a query that gets your columns:

  declare @columns varchar(500)

  set @columns = stuff((

  select ',' + name

  from "YourDB".sys.columns

  where object_id = OBJECTNAME("your table name")

  order by column_id

  FOR XML PATH('')),1,1,'')

  select @columns

  Now, run the BCP utility to create your csv file of data.

  And finally, merge the two files together:

  COPY File1 + File2 File3

  Wayne
  Microsoft Certified Master: SQL Server 2008
  Author - SQL Server T-SQL Recipes

  ---

  If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

  ---

  Links:
  For better assistance in answering your questions
  Performance Problems
  Common date/time routines
  Understanding and Using APPLY Part 1 & Part 2

• Jai Gupta

  SSC Veteran

  Points: 209

  More actions

  June 11, 2009 at 4:05 am

  #1007870

  Thanks Wayne , for such a quick reply.