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

Writing dataset to CSV file?

P: n/a
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
Nov 21 '05 #1
Share this Question
Share on Google+
6 Replies


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

Hello,

Im 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 its
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 doesnt work?

Oughtnt 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.
Ive seen hints that this is possible, but havent found any examples. Id 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)
Nov 21 '05 #2

P: n/a
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
Nov 21 '05 #3

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


Nov 21 '05 #4

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


Nov 21 '05 #5

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

Nov 21 '05 #6

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

Nov 21 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.