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

DataSet to Text File

P: n/a
Well I had a way to write an array to an excel spreadsheet but on a huge
time critical run it failed iwth the dreaded HRESULT: 0x800A03EC error. It
worked fine when i sampled the data to go in but when it all tried to go in
it bombed.

So I need to write this to a tab delimited file and I sure hope somebody is
awake tonight.

How do I write my dataset to make a tab delimted text file?


Nov 20 '05 #1
Share this Question
Share on Google+
11 Replies


P: n/a
can u elaborate a bit more so that i may help u in excel stuff...
if u want to stick to tab delimited file then u may need to play with
strings. take every datacell and add tab. repeat for each dataset and dont
forget to add a newline after each row...

"scorpion53061" <Its the end of the world as we know it@here.com> wrote in
message news:OR****************@TK2MSFTNGP09.phx.gbl...
Well I had a way to write an array to an excel spreadsheet but on a huge
time critical run it failed iwth the dreaded HRESULT: 0x800A03EC error. It worked fine when i sampled the data to go in but when it all tried to go in it bombed.

So I need to write this to a tab delimited file and I sure hope somebody is awake tonight.

How do I write my dataset to make a tab delimted text file?

Nov 20 '05 #2

P: n/a
This is my code that failed me when I needed it...

http://www.kjmsolutions.com/datasetarray.htm

I need something quick now because I dont have time to make it work.
Nov 20 '05 #3

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

Instead of setting the delim variable to "," for comma delimited, you can
set it to ControlChars.Tab for tab delimited.

Hope this helps
Jay

"scorpion53061" <Its the end of the world as we know it@here.com> wrote in
message news:OR****************@TK2MSFTNGP09.phx.gbl...
Well I had a way to write an array to an excel spreadsheet but on a huge
time critical run it failed iwth the dreaded HRESULT: 0x800A03EC error. It
worked fine when i sampled the data to go in but when it all tried to go in it bombed.

So I need to write this to a tab delimited file and I sure hope somebody is awake tonight.

How do I write my dataset to make a tab delimted text file?

Nov 20 '05 #4

P: n/a
Jay,

I hope you dont mind and I promise I will help you out someday but right now
my brain is mush and year end is hours away.

My dataset name is dslist1.Tables(0)

I have to write a tab delimted text file of this dataset.

The dataset is large.

Thank you for your help!!

"Jay B. Harlow [MVP - Outlook]" <Ja************@msn.com> wrote in message
news:%2****************@TK2MSFTNGP11.phx.gbl...
scorpion53061
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

Instead of setting the delim variable to "," for comma delimited, you can
set it to ControlChars.Tab for tab delimited.

Hope this helps
Jay

"scorpion53061" <Its the end of the world as we know it@here.com> wrote in
message news:OR****************@TK2MSFTNGP09.phx.gbl...
Well I had a way to write an array to an excel spreadsheet but on a huge
time critical run it failed iwth the dreaded HRESULT: 0x800A03EC error. It worked fine when i sampled the data to go in but when it all tried to go

in
it bombed.

So I need to write this to a tab delimited file and I sure hope somebody

is
awake tonight.

How do I write my dataset to make a tab delimted text file?


Nov 20 '05 #5

P: n/a
Cor
HI Scorp,

For people as Jan and me it is morning but I always start at the bottom with
reading.

Here is your code (as a textfile the other was a memory area)
And delimeted with a comma not with a tab (asc(09))
I would say try it,

Cor

\\\
Dim Scorpion As New ArrayList
For i As Integer = 0 To ds.Tables("scorpion").Rows.Count - 1
Dim row As New System.Text.StringBuilder
Scorpion.Add(row)
For y As Integer = 0 To ds.Tables("scorpion").Columns.Count - 1
row.Append(ds.Tables("scorpion").Rows(i)(y).tostri ng)
If y <> ds.Tables("scorpion").Columns.Count - 1 Then
row.Append(",")
End If
Next
Next
Dim sw As New IO.StreamWriter("C:\test1\Scorpion.csv")
For i As Integer = 0 To Scorpion.Count - 1
sw.WriteLine(Scorpion(i).ToString)
Next
sw.Flush()
sw.Close()
///
Nov 20 '05 #6

P: n/a
scorpion53061,
I'm not sure if you are thanking me or asking for more help! (year ends bite
;-) )

The Export routine I gave should do the job for you. Simply change two lines
that look like:
delim = ","
to
delim = ControlChars.Tab
Then you can call the routine with:
Export("scorpion53061.txt", dslist1.Tables(0))
Well this isn't correct! Dim output As New StreamWriter(path, False, UnicodeEncoding.Default)

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.
I'm actually using the Encoding.Default property above, which gives me the
Win32 code page, which for this routine is more correct...

I would change the line to: Dim output As New StreamWriter(path, False,

Encoding.Default)


Hope this helps
Jay
"scorpion53061" <Its the end of the world as we know it@here.com> wrote in
message news:eP**************@TK2MSFTNGP10.phx.gbl... Jay,

I hope you dont mind and I promise I will help you out someday but right now my brain is mush and year end is hours away.

My dataset name is dslist1.Tables(0)

I have to write a tab delimted text file of this dataset.

The dataset is large.

Thank you for your help!!

"Jay B. Harlow [MVP - Outlook]" <Ja************@msn.com> wrote in message
news:%2****************@TK2MSFTNGP11.phx.gbl...
scorpion53061
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

Instead of setting the delim variable to "," for comma delimited, you
can set it to ControlChars.Tab for tab delimited.

Hope this helps
Jay

"scorpion53061" <Its the end of the world as we know it@here.com> wrote in message news:OR****************@TK2MSFTNGP09.phx.gbl...
Well I had a way to write an array to an excel spreadsheet but on a huge time critical run it failed iwth the dreaded HRESULT: 0x800A03EC
error. It worked fine when i sampled the data to go in but when it all tried to

go in
it bombed.

So I need to write this to a tab delimited file and I sure hope
somebody is
awake tonight.

How do I write my dataset to make a tab delimted text file?



Nov 20 '05 #7

P: n/a
Cor
Hi Scorp,

I did look now at the sample from JayB and would not know why it would not
work.

But this morning I thought you had a problem and started to make it for you
without looking at the sample from Jay B. Now I am thinkig I even have this
somewhere but I even did not search for it.

Now that I look at the sample from Jay B Ii see that it has quote before
and after.

I changed my sample here in the message to make it equal. Than you can check
which is faster.

Not as a competion because I am curious also and you have a hugh dataset to
test it.
I think that Jay B will also be intrested..

Cor

\\\
Dim Scorpion As New ArrayList
For i As Integer = 0 To ds.Tables("scorpion").Rows.Count - 1
Dim row As New System.Text.StringBuilder
Scorpion.Add(row)
row.append("""")
For y As Integer = 0 To ds.Tables("scorpion").Columns.Count - 1
row.Append(ds.Tables("scorpion").Rows(i)(y).tostri ng)
If y <> ds.Tables("scorpion").Columns.Count - 1 Then
row.Append(""",""")
else
row.Append("""")
End If
Next
Next
Dim sw As New IO.StreamWriter("C:\test1\Scorpion.csv")
For i As Integer = 0 To Scorpion.Count - 1
sw.WriteLine(Scorpion(i).ToString)
Next
sw.Flush()
sw.Close()
///


Nov 20 '05 #8

P: n/a
Yes they are.....

I sit on a AIX RS6000 whose mother board died last week in the middle of
inventory. In addition to all the crap of inventory, year end processing,
and then my excel code failing when I needed it most, try dealing with the
fine outstanding customer service over at IBM. I will take MS any day.

Anyway I cheated. I wrote the text file line by line instead of doing a
dataset. I was out of time.

I am exploring yours and Cors sample. I am still baffled why my excel code
died. It worked fine with data under 3 megs.

I am sorry I dumped on you guys like that.
Nov 20 '05 #9

P: n/a
hi,

Turns out there was nothing wrong with the code.

It was "user error".

The problem laid in the data - there were special characters that I was not
aware of.

Thanks to Jay for pointing out the encoding example.
Nov 20 '05 #10

P: n/a
Cor
Hi Scorpion,

Thank you for the work I done, (Cindy have pointed you on that before I
remembered me chr(09) , but you did persist on a comma)

Cor
Nov 20 '05 #11

P: n/a
> Thank you for the work I done, (Cindy have pointed you on that before I
remembered me chr(09) , but you did persist on a comma)


Hi Cor,

Yes thank you for suffering with me through this issue. Everything else
usually has come pretty easy but this has not.

I still have not resolved the issue of writing a datasest via an array to a
MS Word table. My excel code worked fine inside Excel.

Every method I have tried in MS Word has resulted in the "HResult"
autommation errors. I have not found any samples that deal with datasets and
MS Word tables. It is quite baffling.

I am getting ready to pay somebody. Writing it cell by cell is not going to
work in a commercial situation.
Nov 20 '05 #12

This discussion thread is closed

Replies have been disabled for this discussion.