Export data from SQL Store Procedure to text comma delimited

Hi Expert,

Please help me export data from my sql to text file with comma delimited format. I have SP that generates the data and i want it from the user interface to download the data and save is as text comma delimited.

Many Thanks.
akoITAsked:
Who is Participating?
 
akoITAuthor Commented:
Thanks all I already got it :)

Protected Sub ExportToCSV(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnExportCSV.Click
   'Get the data from database into datatable
   Dim strQuery As String = "select CustomerID, ContactName, City," & _
           " PostalCode from customers"
   Dim cmd As New SqlCommand(strQuery)
   Dim dt As DataTable = GetData(cmd)
 
   Response.Clear()
   Response.Buffer = True
   Response.AddHeader("content-disposition", _
           "attachment;filename=DataTable.txt") ' or csv
   Response.Charset = ""
   Response.ContentType = "application/text"
 
   Dim sb As New StringBuilder()
   For k As Integer = 0 To dt.Columns.Count - 1
        'add separator
         sb.Append(dt.Columns(k).ColumnName + ","c)
   Next
   'append new line
   sb.Append(vbCr & vbLf)
   For i As Integer = 0 To dt.Rows.Count - 1
      For k As Integer = 0 To dt.Columns.Count - 1
        'add separator
         sb.Append(dt.Rows(i)(k).ToString().Replace(",", ";") + ","c)
      Next
     'append new line
      sb.Append(vbCr & vbLf)
   Next
   Response.Output.Write(sb.ToString())
   Response.Flush()
   Response.End()
End Sub

credits to https://www.aspsnippets.com :)
0
 
Pawan KumarDatabase ExpertCommented:
Can you change your stored procedure.
0
 
Dirk StraussSenior Full Stack DeveloperCommented:
The one way to do this is in SQL Server Management Studio (I'm assuming SSMS here). Export query result to .csv file in SQL Server 2008. I am assuming though that you want to do this via code. If so, just write the results of the DataSet to a CSV file: Best way to save DataSet to a CSV file.

An interesting comment on the previous article on StackOverflow was to use FileHelpers.
FileHelpers is a free and easy to use .NET library to import or export data from fixed length or delimited records in files, strings or streams.
.
It's free, why not give that a try?
0
 
Dirk StraussSenior Full Stack DeveloperCommented:
I feel that there should be an accepted/assisted solution. The author did not respond to any of the experts.
0
 
akoITAuthor Commented:
Hi Dirk,

Sorry for I do not have response to any of the expert solution because  there is no assisted solution on the current problem.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.