DataSet to Text File

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?

Sumant
Thu Jan 22 00:41:55 CST 2004

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:OR4%23s9K4DHA.2480@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?
>
>
>
>

scorpion53061
Thu Jan 22 00:52:12 CST 2004

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.

Jay
Thu Jan 22 01:05:44 CST 2004

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:OR4%23s9K4DHA.2480@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?
>
>
>
>

scorpion53061
Thu Jan 22 01:18:40 CST 2004

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]" <Jay_Harlow_MVP@msn.com> wrote in message
news:%23z$ArYL4DHA.1272@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:OR4%23s9K4DHA.2480@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?
> >
> >
> >
> >
>
>

Cor
Thu Jan 22 02:48:28 CST 2004

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).tostring)
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()
///

Jay
Thu Jan 22 08:06:19 CST 2004

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:ePaU6fL4DHA.2348@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]" <Jay_Harlow_MVP@msn.com> wrote in message
> news:%23z$ArYL4DHA.1272@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:OR4%23s9K4DHA.2480@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?
> > >
> > >
> > >
> > >
> >
> >
>
>

Cor
Thu Jan 22 08:39:25 CST 2004

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).tostring)
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()
///

scorpion53061
Thu Jan 22 13:34:09 CST 2004

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.

scorpion53061
Fri Jan 23 06:39:18 CST 2004

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.

Cor
Fri Jan 23 06:53:07 CST 2004

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

scorpion53061
Fri Jan 23 16:45:38 CST 2004

> 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.