exporting sql query resultset to cvs or xls

• Tushar Chheda

  Mr or Mrs. 500

  Points: 585

  More actions

  January 3, 2006 at 9:35 am

  #166390

  Hi All,

  I want to export data retrived using a complex query in csv or xls file format for sending to my clients.

  Is there a way of generating the same on sql server or it has to be done using a front end like asp or vb.

  Thanks in advance.

  Tushar Chheda

• Ray M

  SSC-Insane

  Points: 21093

  More actions

  January 3, 2006 at 10:20 am

  #612435

  You can generate csv or xls from Sql Server DTS

  Lots of info and tutorials on sqldts.com

• Tushar Chheda

  Mr or Mrs. 500

  Points: 585

  More actions

  January 3, 2006 at 10:26 am

  #612436

  Thanks Ray

  but my problem is i do not want to use dts, i want to generate using sql stored procedure or query analyzer.

  Regards,

  Tushar Chheda

• rangab

  Old Hand

  Points: 303

  More actions

  January 3, 2006 at 10:35 am

  #612442

  if you can do manually on Query Analyser, right click on results after executing query and save as csv file...

  if you want to do in automation you can use BCP or OSQL commands...

  
  bondada

• sliu

  Old Hand

  Points: 329

  More actions

  January 3, 2006 at 11:06 am

  #612452

  'Try this:

  'Here's your recordset:

  set rsp=objConnhc.Execute(sql)

  'Then write the recordset to your CSV file, here's example MyFile.CSV

  Open "MyFile.CSV" For Output as #1

  For Each F In rsp.Fields

  Head = Head & ", " & F.Name

  Next Head = Mid(Head,3) & vbCrLf

  Response.ContentType = "text/plain"

  Response.Write Head

  Response.Write rsp.GetString(,,", ",vbCrLf,"")

  close #1

  Response.end

  %>

  Good Luck.