Writing dataset to CSV file?

[dast]

Hello,

I’m having trouble writing the contents of a dataset to a csv file.

I have made a solution that loops through the rows in the dataset, but it’s
too slow. There must be a faster and easier way.

Here is a stripped down version of my failed attempt of writing without
looping through the dataset:

Private Sub WriteCSVFile(ByVal path As String, ByVal file As String, ByVal
dsNewMeasurements As DataSet)

'Connect to csv file
Dim csvConnection As New
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + path +
";Extended Properties='text;FMT=Delimited;HDR=NO';")
Dim csvCommand As New OleDbCommand("SELECT * FROM " + file, csvConnection)
Dim csvAdapter As New OleDbDataAdapter(csvCommand)
csvConnection.Open()

'Read csv file, merge with new data and write back
Dim csvDataset As New DataSet
csvAdapter.Fill(csvDataset)
csvDataset.Merge(dsNewMeasurements)
Dim rowsUpdated As Integer = csvAdapter.Update(csvDataset)
csvConnection.Close()

End Sub

The two datasets seem to get merged (at least according to the row count in
the table), but rowsUpdated becomes zero, and no changes are made to the csv
file. How come this doesn’t work?

Oughtn’t there be some way to do this without having to read from the csv
file first?

I have fought with this for a while now, and searched the net for solutions.
I’ve seen hints that this is possible, but haven’t found any examples. I’d be
very thankful for any help on this.

Best regards
/Daniel

[Paul Clement]

On Tue, 14 Jun 2005 02:40:02 -0700, "dast" <da**@discussions.microsoft.com> wrote:

¤ Hello,
¤
¤ I’m having trouble writing the contents of a dataset to a csv file.
¤
¤ I have made a solution that loops through the rows in the dataset, but it’s
¤ too slow. There must be a faster and easier way.
¤
¤ Here is a stripped down version of my failed attempt of writing without
¤ looping through the dataset:
¤
¤ Private Sub WriteCSVFile(ByVal path As String, ByVal file As String, ByVal
¤ dsNewMeasurements As DataSet)
¤
¤ 'Connect to csv file
¤ Dim csvConnection As New
¤ OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + path +
¤ ";Extended Properties='text;FMT=Delimited;HDR=NO';")
¤ Dim csvCommand As New OleDbCommand("SELECT * FROM " + file, csvConnection)
¤ Dim csvAdapter As New OleDbDataAdapter(csvCommand)
¤ csvConnection.Open()
¤
¤ 'Read csv file, merge with new data and write back
¤ Dim csvDataset As New DataSet
¤ csvAdapter.Fill(csvDataset)
¤ csvDataset.Merge(dsNewMeasurements)
¤ Dim rowsUpdated As Integer = csvAdapter.Update(csvDataset)
¤ csvConnection.Close()
¤
¤ End Sub
¤
¤ The two datasets seem to get merged (at least according to the row count in
¤ the table), but rowsUpdated becomes zero, and no changes are made to the csv
¤ file. How come this doesn’t work?
¤
¤ Oughtn’t there be some way to do this without having to read from the csv
¤ file first?
¤
¤ I have fought with this for a while now, and searched the net for solutions.
¤ I’ve seen hints that this is possible, but haven’t found any examples. I’d be
¤ very thankful for any help on this.

I don't believe this will work since the Text ISAM driver does not have update capability.
Paul
~~~~
Microsoft MVP (Visual Basic)

[Ken Tucker [MVP]]

Hi,

Dont see where you added the update and insert commands. Try
using a commandbuilder to create them. Never tried this with a csv

http://msdn.microsoft.com/library/de...classtopic.asp

Ken
----------------------
"dast" <da**@discussions.microsoft.com> wrote in message
news:E6**********************************@microsof t.com...
Hello,

I'm having trouble writing the contents of a dataset to a csv file.

I have made a solution that loops through the rows in the dataset, but it's
too slow. There must be a faster and easier way.

Here is a stripped down version of my failed attempt of writing without
looping through the dataset:

Private Sub WriteCSVFile(ByVal path As String, ByVal file As String, ByVal
dsNewMeasurements As DataSet)

'Connect to csv file
Dim csvConnection As New
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + path +
";Extended Properties='text;FMT=Delimited;HDR=NO';")
Dim csvCommand As New OleDbCommand("SELECT * FROM " + file,
csvConnection)
Dim csvAdapter As New OleDbDataAdapter(csvCommand)
csvConnection.Open()

'Read csv file, merge with new data and write back
Dim csvDataset As New DataSet
csvAdapter.Fill(csvDataset)
csvDataset.Merge(dsNewMeasurements)
Dim rowsUpdated As Integer = csvAdapter.Update(csvDataset)
csvConnection.Close()

End Sub

The two datasets seem to get merged (at least according to the row count in
the table), but rowsUpdated becomes zero, and no changes are made to the csv
file. How come this doesn't work?

Oughtn't there be some way to do this without having to read from the csv
file first?

I have fought with this for a while now, and searched the net for solutions.
I've seen hints that this is possible, but haven't found any examples. I'd
be
very thankful for any help on this.

Best regards
/Daniel

[shriop]

My CSV utility will write a DataTable out to a CSV file in roughly a
line or two of code. Just look at the WriteAll method of the CsvWriter
class.

http://www.geocities.com/shriop/index.html

Paul Clement wrote:

On Tue, 14 Jun 2005 02:40:02 -0700, "dast" <da**@discussions.microsoft.com> wrote:

¤ Hello,
¤
¤ I'm having trouble writing the contents of a dataset to a csv file.
¤
¤ I have made a solution that loops through the rows in the dataset, but it's
¤ too slow. There must be a faster and easier way.
¤
¤ Here is a stripped down version of my failed attempt of writing without
¤ looping through the dataset:
¤
¤ Private Sub WriteCSVFile(ByVal path As String, ByVal file As String, ByVal
¤ dsNewMeasurements As DataSet)
¤
¤ 'Connect to csv file
¤ Dim csvConnection As New
¤ OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" +path +
¤ ";Extended Properties='text;FMT=Delimited;HDR=NO';")
¤ Dim csvCommand As New OleDbCommand("SELECT * FROM " + file, csvConnection)
¤ Dim csvAdapter As New OleDbDataAdapter(csvCommand)
¤ csvConnection.Open()
¤
¤ 'Read csv file, merge with new data and write back
¤ Dim csvDataset As New DataSet
¤ csvAdapter.Fill(csvDataset)
¤ csvDataset.Merge(dsNewMeasurements)
¤ Dim rowsUpdated As Integer = csvAdapter.Update(csvDataset)
¤ csvConnection.Close()
¤
¤ End Sub
¤
¤ The two datasets seem to get merged (at least according to the row count in
¤ the table), but rowsUpdated becomes zero, and no changes are made to the csv
¤ file. How come this doesn't work?
¤
¤ Oughtn't there be some way to do this without having to read from thecsv
¤ file first?
¤
¤ I have fought with this for a while now, and searched the net for solutions.
¤ I've seen hints that this is possible, but haven't found any examples.. I'd be
¤ very thankful for any help on this.

I don't believe this will work since the Text ISAM driver does not have update capability.


Paul
~~~~
Microsoft MVP (Visual Basic)

[dast]

Hello again,

When testing shriops demoversion I get this error on the WriteAll line:
"The process cannot access the file "c:\temp\data.csv" because it is being
used by another process."

Shouldn't closing the reader free the file for use again? Or is there some
way to go around this and make sure the file is closed properly?

Below is my code.

Dim reader As New CsvReader(path & "\" & file)
csvDataset.Tables.Add(reader.ReadToEnd(False))
csvDataset.Merge(otherDataset)
reader.Close()
reader.Dispose()

Dim writer As New CsvWriter(path & "\" & file)
writer.WriteAll(csvDataset.Tables.Item(0), False)
writer.Close()
writer.Dispose()

Best regards
/Daniel

"shriop" wrote:

My CSV utility will write a DataTable out to a CSV file in roughly a
line or two of code. Just look at the WriteAll method of the CsvWriter
class.

http://www.geocities.com/shriop/index.html

[shriop]

Yes, closing the reader should absolutely free it up for use again and
I've never ran into an issue in it not doing so. I would bet that
you've opened the file in Excel after writing it and still have Excel
open, or another program with that file opened in it. I created a file
called C:\test.csv with a couple of rows of data in it, then ran this
code over and over with no issues.

Module ErrorTest

Sub Main()
Dim Path As String = "c:"
Dim File As String = "test.csv"

Dim csvDataset As New DataSet
Dim otherDataset As New DataSet

Dim reader As New CsvReader(Path & "\" & File)
csvDataset.Tables.Add(reader.ReadToEnd(False))
csvDataset.Merge(otherDataset)
reader.Close()
reader.Dispose()

Dim writer As New CsvWriter(Path & "\" & File)
writer.WriteAll(csvDataset.Tables.Item(0), False)
writer.Close()
writer.Dispose()
End Sub

End Module

[dast]

Mea Culpa.

I had another reader which I didn't close properly in some scenarios.
Now everything works fine.
Thanks for all help.

/Daniel
"shriop" wrote:

Yes, closing the reader should absolutely free it up for use again and
I've never ran into an issue in it not doing so. I would bet that
you've opened the file in Excel after writing it and still have Excel
open, or another program with that file opened in it. I created a file
called C:\test.csv with a couple of rows of data in it, then ran this
code over and over with no issues.

Module ErrorTest

Sub Main()
Dim Path As String = "c:"
Dim File As String = "test.csv"

Dim csvDataset As New DataSet
Dim otherDataset As New DataSet

Dim reader As New CsvReader(Path & "\" & File)
csvDataset.Tables.Add(reader.ReadToEnd(False))
csvDataset.Merge(otherDataset)
reader.Close()
reader.Dispose()

Dim writer As New CsvWriter(Path & "\" & File)
writer.WriteAll(csvDataset.Tables.Item(0), False)
writer.Close()
writer.Dispose()
End Sub

End Module