473,320 Members | 1,868 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Comma Delimited

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
5 2694
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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Arne | last post by:
From: "Arne de Booij" <a_de_booij@hotmail.com> Subject: Comma delimited array into DB problems Date: 9. februar 2004 10:39 Hi, I have an asp page that takes input from a form on the previous...
4
by: Christine Forber | last post by:
I wonder if anyone knows of some javascript code to check a comma-delimited list of email addresses for basic formating. What I'm looking for is the javascript code to check a form field on form...
3
by: Elmo Watson | last post by:
I've been asked to develop a semi-automated type situation where we have a database table (sql server) and periodically, there will be a comma delimited file from which we need to import the data,...
2
by: A E | last post by:
Hi, I was wondering if there was a function that handles list elements of a comma delimited list? I need to be able to pass values as a comma delimited list, count the number of values, and...
2
by: Larry Williams | last post by:
Is there an easy way to convert a string of data to XML format without having to create the xml one field at a time? Sorry I'm a newbie and my xml knowledge is limited. I have tried to search...
1
by: John B. Lorenz | last post by:
I'm attempting to write an input routine that reads from a comma delimited file. I need to read in one record at a time, assign each field to a field array and then continue with my normal...
9
by: Wayne | last post by:
I have the following string: "smith", "Joe", "West Palm Beach, Fl." I need to split this string based on the commas, but as you see the city state contains a comma. String.split will spilt the...
9
by: Bernie Yaeger | last post by:
Is there a way to convert or copy a .xml file to a comma delimited text file using vb .net? Thanks for any help. Bernie Yaeger
4
by: JustSomeGuy | last post by:
Hi. I have a comma delimited text file that I want to parse. I was going to use fscanf from the C library but as my app is written in C++ I thought I'd use the std io stream library... My Text...
2
by: Ron | last post by:
so if my textbox is named textbox1 and my listbox is named ltsdisplay, for the button that would make this all happen I would just need to: ...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.