Comma Delimited

[Yama]

Hi,

I am looking to create a report comma delimited on a click of a button.

Explanantion:
1. Get from the database: "SELECT * FROM Customers WHERE Region = 'CA'"
2. Use either DataReader or DataSource
3. Create a button "Export"
4. On ServerClick Event prompt user to save as a text comma delimited file.

Can someone help me?

Yama

 

[Jay B. Harlow [MVP - Outlook]]

Yama,
Here's a quick VB.NET 1.1 export routine that is very general (too
general?):

Its based on a DataSet, however you should be able to adopt it to a
DataReader instead.

' Required imports
Imports System.IO ' for the StreamWriter
Imports System.Text ' for the UnicodeEncoding

' sample usage
Export("Customers.csv", DataSet1.Tables("Customers"))
Export("Employees.csv", DataSet1.Tables("Employees"))


Public Sub Export(ByVal path As String, ByVal table As DataTable)
Dim output As New StreamWriter(path, False, UnicodeEncoding.Default)
Dim delim As String

' Write out the header row
delim = ""
For Each col As DataColumn In table.Columns
output.Write(delim)
output.Write(col.ColumnName)
delim = ","
Next
output.WriteLine()

' write out each data row
For Each row As DataRow In table.Rows
delim = ""
For Each value As Object In row.ItemArray
output.Write(delim)
If TypeOf value Is String Then
output.Write(""""c) ' thats four double quotes and a c
output.Write(value)

output.Write(""""c) ' thats four double quotes and a c
Else
output.Write(value)
End If
delim = ","
Next
output.WriteLine()
Next

output.Close()

End Sub

You can change (or remove) the Encoding parameter above to suit your needs,
I used Unicode as the table had non ASCII characters in it. Also when
writing strings, I don't deal with double quotes in the string. If you make
the StreamWriter a parameter it will be much more flexible (you could go to
a memory stream to support cut & paste). I use the default formatting for
numeric types.

You can change it to use a DataView (for sorting & filtering for example) by
changing the following lines:

'Public Sub Export(ByVal path As String, ByVal table As DataTable)
Public Sub Export(ByVal path As String, ByVal view As DataView)

'For Each col As DataColumn In table.Columns
For Each col As DataColumn In view.Table.Columns

'For Each row As DataRow In table.Rows
For Each row As DataRowView In view

'For Each value As Object In row.ItemArray
For Each value As Object In row.Row.ItemArray


Hope this helps
Jay

 

[Yama]

Wow!

You are da man!

Thanks to you I am sure many other people will refer to this thread... Well
maybe.

Thank you anyway. Your help was certainly appreciated by me.

Yama

 

[Yama]

Hi,

I am using the following:
response.Clear()

response.ClearContent()

'set the response mime type for text

response.ContentType = "text/plain"

HttpContext.Current.ApplicationInstance.CompleteRequest()

But instead of the user being prompted to save output as TEXT they are
prompted to save as ASPX. Although changing it to a .TXT will save the
content as a text file. How to force IE to display the dialog box to save as
TEXT?



Yama

 

[Jay B. Harlow [MVP - Outlook]]

Yama,
I do little or no ASP.NET...

If no one here gives you an answer, you may want to ask this question "down
the hall" in the microsoft.public.dotnet.framework.aspnet newsgroup.

Hope this helps
Jay

 

[Jim Hughes]

Before using Response.Write(csvtext) use:

Page.Response.AddHeader("content-disposition",
"attachment;filename=output.csv")