Populate dataset from .csv file

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm trying to find sample code to Populate a dataset from a .csv file, but
all I see are samples of populating a dataset from an xml file.
Is there an easy way to get my .csv file into a dataset?

Thanks,
Brian Rupert
 
¤ I'm trying to find sample code to Populate a dataset from a .csv file, but
¤ all I see are samples of populating a dataset from an xml file.
¤ Is there an easy way to get my .csv file into a dataset?
¤

The following connects to a text file, dumps the data into a DataTable in a DataSet, displays the
data in a DataGrid, and also lists the column names and values.

Dim ConnectionString As String

ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & "E:\My Documents\TextFiles" & ";" & _
"Extended Properties=""Text;HDR=NO;"""

Dim TextConnection As New System.Data.OleDb.OleDbConnection(ConnectionString)
TextConnection.Open()

Dim da As New System.Data.OleDb.OleDbDataAdapter("SELECT * FROM CSV#txt", TextConnection)

Dim ds As New DataSet("TextFiles")
da.Fill(ds, "CSV")

Dim dt As DataTable

dt = ds.Tables("CSV")

DataGrid1.SetDataBinding(ds, "CSV")

Dim drCurrentCol As DataColumn
For Each drCurrentCol In dt.Columns
Console.WriteLine(drCurrentCol.ColumnName)
Next

Dim drCurrent As DataRow
For Each drCurrent In dt.Rows
Console.WriteLine(drCurrent(0).ToString)
'Console.WriteLine(drCurrent(1).ToString)
'Console.WriteLine(drCurrent(2).ToString)
'Console.WriteLine(drCurrent(3).ToString)
'Console.WriteLine(drCurrent(4).ToString)
'Console.WriteLine(drCurrent(5).ToString)
'Console.WriteLine(drCurrent(6).ToString)
Next

TextConnection.Close()


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
Paul,

I was reading through the posts and found your post most helpful. I too am
trying to populate a dataset from a CSV file. However, when I used your code
I ran into an error "An unhandled exception of type
'System.Data.OleDb.OleDbException' occurred in system.data.dll" when
executing the TextConnection.Open() line.

Below is my code. Would you please advise me of what I may have done wrong?

Function ImportTxtFile(ByVal myFileName As String)
'The following connects to a text file, dumps the data into a
DataTable in a DataSet,
'displays the data in a DataGrid, and also lists the column names
and values.

Dim ConnectionString As String

ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & myFileName & ";" & _
"Extended Properties=""Text;HDR=NO;"""

Dim TextConnection As New
System.Data.OleDb.OleDbConnection(ConnectionString)
TextConnection.Open()

Dim da As New System.Data.OleDb.OleDbDataAdapter("SELECT * FROM " &
myFileName, TextConnection)

Dim ds As New DataSet("TextFiles")
da.Fill(ds, "CSV")

Dim dt As DataTable

dt = ds.Tables("CSV")

DataGrid1.SetDataBinding(ds, "CSV")

Dim drCurrentCol As DataColumn
For Each drCurrentCol In dt.Columns
Console.WriteLine(drCurrentCol.ColumnName)
Next

Dim drCurrent As DataRow
For Each drCurrent In dt.Rows
Console.WriteLine(drCurrent(0).ToString)
'Console.WriteLine(drCurrent(1).ToString)
'Console.WriteLine(drCurrent(2).ToString)
'Console.WriteLine(drCurrent(3).ToString)
'Console.WriteLine(drCurrent(4).ToString)
'Console.WriteLine(drCurrent(5).ToString)
'Console.WriteLine(drCurrent(6).ToString)
Next

'TextConnection.Close()
End Function

I look forward to your response, or that of anyone who may be able to help
me with this.
 
Użytkownik "Raddster said:
Paul,

I was reading through the posts and found your post most helpful. I too
am
trying to populate a dataset from a CSV file. However, when I used your
code
I ran into an error "An unhandled exception of type
'System.Data.OleDb.OleDbException' occurred in system.data.dll" when
executing the TextConnection.Open() line.

Below is my code. Would you please advise me of what I may have done
wrong?

Function ImportTxtFile(ByVal myFileName As String)
'The following connects to a text file, dumps the data into a
DataTable in a DataSet,
'displays the data in a DataGrid, and also lists the column names
and values.

Dim ConnectionString As String

ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & myFileName & ";" & _
"Extended Properties=""Text;HDR=NO;"""
Click to expand...

If myFileName variable contains full path (for example E:\DataFiles\Any
folder\FileName.csv) then ConnectionString will look as below:
Provider=Microsoft.Jet.OLEDB.4.0; Data Source=E:\DataFiles\Any
folder\FileName.csv;
Extended Properties="Text;HDR=NO;"

But Connection string should look as follows:
Provider=Microsoft.Jet.OLEDB.4.0; Data Source=E:\DataFiles\Any folder;
Extended Properties="Text;HDR=NO;"

(folder path instead of file path)
Dim TextConnection As New
System.Data.OleDb.OleDbConnection(ConnectionString)
TextConnection.Open()

Dim da As New System.Data.OleDb.OleDbDataAdapter("SELECT * FROM " &
myFileName, TextConnection)
Click to expand...

Another error: If myFileName contains full path of file your select will
look:

Select * From D:\DataFiles\Any folder\FileName.csv

Instead it should look as below:
Select * From FileName#csv

Compare with:
:
(...)
Click to expand...
(...)

Regards,
Grzegorz
 
Back
Top