By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
448,784 Members | 1,182 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 448,784 IT Pros & Developers. It's quick & easy.

Comma Delimited

P: n/a
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
Nov 20 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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" <yk*****@grandpacificresorts.com> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl...
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

Nov 20 '05 #2

P: n/a
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
"Jay B. Harlow [MVP - Outlook]" <Ja************@msn.com> wrote in message
news:OR**************@TK2MSFTNGP11.phx.gbl...
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" <yk*****@grandpacificresorts.com> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl...
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


Nov 20 '05 #3

P: n/a
Hi,

I am using the following:
response.Clear()

response.ClearContent()

'set the response mime type for text

response.ContentType = "text/plain"

HttpContext.Current.ApplicationInstance.CompleteRe quest()

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]" <Ja************@msn.com> wrote in message
news:OR**************@TK2MSFTNGP11.phx.gbl...
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" <yk*****@grandpacificresorts.com> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl...
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


Nov 20 '05 #4

P: n/a
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

"Yama" <yk*****@grandpacificresorts.com> wrote in message
news:uW**************@TK2MSFTNGP12.phx.gbl...
Hi,

I am using the following:
response.Clear()

response.ClearContent()

'set the response mime type for text

response.ContentType = "text/plain"

HttpContext.Current.ApplicationInstance.CompleteRe quest()

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

Nov 20 '05 #5

P: n/a
Before using Response.Write(csvtext) use:

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

"Yama" <yk*****@grandpacificresorts.com> wrote in message
news:uW**************@TK2MSFTNGP12.phx.gbl...
Hi,

I am using the following:
response.Clear()

response.ClearContent()

'set the response mime type for text

response.ContentType = "text/plain"

HttpContext.Current.ApplicationInstance.CompleteRe quest()

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]" <Ja************@msn.com> wrote in message
news:OR**************@TK2MSFTNGP11.phx.gbl...
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" <yk*****@grandpacificresorts.com> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl...
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



Nov 20 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.