DataSet to Text File

  • Thread starter Thread starter scorpion53061
  • Start date Start date
S

scorpion53061

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?
 
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
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
 
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!!
 
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()
///
 
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:
to
Click to expand...

Then you can call the routine with:

Well this isn't correct!
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:
Encoding.Default)
Click to expand...

Hope this helps
Jay
 
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()
///
 
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.
 
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.
 
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
 
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)
Click to expand...

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