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

reading text file to dataset

P: n/a
I need to read text file having data either comma seperated or tab seperated
or any custom seperator and convert into a DataSet in C# .

I tried Microsoft Text Driver and Microsoft.Jet.OLEDB.4.0 to read text file
but could not get the data in correct format.

All columns are not coming in dataset and rows are messing up.

Suggestions please ???


Mar 22 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a
You can use IO.StreamReader class to open the file then read it into a
datatable. After done, add this datatable to the dataset.
Here is the working function in VB.Net (can be easily converted to C#) that
read a text file (can be CSV, tab delitmiting, or anything... You just need
to specify what the seperator character is)and return a dataTable.

'Reading file to datatable
Private Function BuildDataTable(ByVal fileFullPath As String, ByVal
seperator As Char) As DataTable

Dim myTable As DataTable = New DataTable("MyTable")
Dim i As Integer
Dim myRow As DataRow
Dim fieldValues As String()
Dim f As IO.File
Dim myReader As IO.StreamReader
Try
'Open file and read first line to determine how many fields
there are.
myReader = f.OpenText(fileFullPath)
fieldValues = myReader.ReadLine().Split(seperator)
'Create data columns accordingly
For i = 0 To fieldValues.Length() - 1
myTable.Columns.Add(New DataColumn("Field" & i))
Next
'Adding the first line of data to data table
myRow = myTable.NewRow
For i = 0 To fieldValues.Length() - 1
myRow.Item(i) = fieldValues(i).ToString
Next
myTable.Rows.Add(myRow)
'Now reading the rest of the data to data table
While myReader.Peek() <> -1
fieldValues = myReader.ReadLine().Split(seperator)
myRow = myTable.NewRow
For i = 0 To fieldValues.Length() - 1
myRow.Item(i) = fieldValues(i).ToString
Next
myTable.Rows.Add(myRow)
End While
Catch ex As Exception
MsgBox("Error building datatable: " & ex.Message)
Return New DataTable("Empty")
Finally
myReader.Close()
End Try

Return myTable
End Function

Hope this helps.
VHD50.

"Amit Maheshwari" wrote:
I need to read text file having data either comma seperated or tab seperated
or any custom seperator and convert into a DataSet in C# .

I tried Microsoft Text Driver and Microsoft.Jet.OLEDB.4.0 to read text file
but could not get the data in correct format.

All columns are not coming in dataset and rows are messing up.

Suggestions please ???


Mar 22 '06 #2

P: n/a


In some specail casees it wouldn't work, like--

FaxNumber,Company,"First Name , Last Name"

123456789,"asd,fgjh",xxx yyy

In this example there are three coulmns. In first row third coulmn having
comma within coulmn name enclosed by ", and same in second column of second
row. Split(seperator) will give incorrect coulmns values. Microsoft Text
Driver working fine but its showing first column of first row as null which
should be 'FaxNumber'. No idea where is the prob with this. And if save this
file as CSV the same thing occur but if I use any other CSV file which is
generated by code, it works fine for that.

What should I do to resolve this..???

"VHD50" <VH***@discussions.microsoft.com> wrote in message
news:BE**********************************@microsof t.com...
You can use IO.StreamReader class to open the file then read it into a
datatable. After done, add this datatable to the dataset.
Here is the working function in VB.Net (can be easily converted to C#) that read a text file (can be CSV, tab delitmiting, or anything... You just need to specify what the seperator character is)and return a dataTable.

'Reading file to datatable
Private Function BuildDataTable(ByVal fileFullPath As String, ByVal
seperator As Char) As DataTable

Dim myTable As DataTable = New DataTable("MyTable")
Dim i As Integer
Dim myRow As DataRow
Dim fieldValues As String()
Dim f As IO.File
Dim myReader As IO.StreamReader
Try
'Open file and read first line to determine how many fields
there are.
myReader = f.OpenText(fileFullPath)
fieldValues = myReader.ReadLine().Split(seperator)
'Create data columns accordingly
For i = 0 To fieldValues.Length() - 1
myTable.Columns.Add(New DataColumn("Field" & i))
Next
'Adding the first line of data to data table
myRow = myTable.NewRow
For i = 0 To fieldValues.Length() - 1
myRow.Item(i) = fieldValues(i).ToString
Next
myTable.Rows.Add(myRow)
'Now reading the rest of the data to data table
While myReader.Peek() <> -1
fieldValues = myReader.ReadLine().Split(seperator)
myRow = myTable.NewRow
For i = 0 To fieldValues.Length() - 1
myRow.Item(i) = fieldValues(i).ToString
Next
myTable.Rows.Add(myRow)
End While
Catch ex As Exception
MsgBox("Error building datatable: " & ex.Message)
Return New DataTable("Empty")
Finally
myReader.Close()
End Try

Return myTable
End Function

Hope this helps.
VHD50.

"Amit Maheshwari" wrote:
I need to read text file having data either comma seperated or tab seperated or any custom seperator and convert into a DataSet in C# .

I tried Microsoft Text Driver and Microsoft.Jet.OLEDB.4.0 to read text file but could not get the data in correct format.

All columns are not coming in dataset and rows are messing up.

Suggestions please ???


Mar 23 '06 #3

P: n/a

In some specail casees it wouldn't work, like--

FaxNumber,Company,"First Name , Last Name"

123456789,"asd,fgjh",xxx yyy

In this example there are three coulmns. In first row third coulmn having
comma within coulmn name enclosed by ", and same in second column of second
row. Split(seperator) will give incorrect coulmns values. Microsoft Text
Driver working fine but its showing first column of first row as null which
should be 'FaxNumber'. No idea where is the prob with this. And if save this
file as CSV the same thing occur but if I use any other CSV file which is
generated by code, it works fine for that.

What should I do to resolve this..???

"VHD50" <VH***@discussions.microsoft.com> wrote in message
news:BE**********************************@microsof t.com...
You can use IO.StreamReader class to open the file then read it into a
datatable. After done, add this datatable to the dataset.
Here is the working function in VB.Net (can be easily converted to C#) that read a text file (can be CSV, tab delitmiting, or anything... You just need to specify what the seperator character is)and return a dataTable.

'Reading file to datatable
Private Function BuildDataTable(ByVal fileFullPath As String, ByVal
seperator As Char) As DataTable

Dim myTable As DataTable = New DataTable("MyTable")
Dim i As Integer
Dim myRow As DataRow
Dim fieldValues As String()
Dim f As IO.File
Dim myReader As IO.StreamReader
Try
'Open file and read first line to determine how many fields
there are.
myReader = f.OpenText(fileFullPath)
fieldValues = myReader.ReadLine().Split(seperator)
'Create data columns accordingly
For i = 0 To fieldValues.Length() - 1
myTable.Columns.Add(New DataColumn("Field" & i))
Next
'Adding the first line of data to data table
myRow = myTable.NewRow
For i = 0 To fieldValues.Length() - 1
myRow.Item(i) = fieldValues(i).ToString
Next
myTable.Rows.Add(myRow)
'Now reading the rest of the data to data table
While myReader.Peek() <> -1
fieldValues = myReader.ReadLine().Split(seperator)
myRow = myTable.NewRow
For i = 0 To fieldValues.Length() - 1
myRow.Item(i) = fieldValues(i).ToString
Next
myTable.Rows.Add(myRow)
End While
Catch ex As Exception
MsgBox("Error building datatable: " & ex.Message)
Return New DataTable("Empty")
Finally
myReader.Close()
End Try

Return myTable
End Function

Hope this helps.
VHD50.

"Amit Maheshwari" wrote:
I need to read text file having data either comma seperated or tab seperated or any custom seperator and convert into a DataSet in C# .

I tried Microsoft Text Driver and Microsoft.Jet.OLEDB.4.0 to read text file but could not get the data in correct format.

All columns are not coming in dataset and rows are messing up.

Suggestions please ???


Mar 23 '06 #4

P: n/a
Hey, I got the solution, it was datatype problem when reading using
Microsoft.Jet.OLEDB.4.0, it was treating first column as a Double because
all the contents except first row are double type. To resolve this i created
a schema.ini file in which i wrote ScanRows=1 which means scan only first
row to decide the datatype.
Now there is another thing to think is if first row is empty and contents
start from second row then it understand that file has only one column and
in dataset will only have one column.But for now i am not worrying abt it.
"Amit Maheshwari" <am*********@dev.com> wrote in message
news:eE**************@TK2MSFTNGP12.phx.gbl...

In some specail casees it wouldn't work, like--

FaxNumber,Company,"First Name , Last Name"

123456789,"asd,fgjh",xxx yyy

In this example there are three coulmns. In first row third coulmn having
comma within coulmn name enclosed by ", and same in second column of second row. Split(seperator) will give incorrect coulmns values. Microsoft Text
Driver working fine but its showing first column of first row as null which should be 'FaxNumber'. No idea where is the prob with this. And if save this file as CSV the same thing occur but if I use any other CSV file which is
generated by code, it works fine for that.

What should I do to resolve this..???

"VHD50" <VH***@discussions.microsoft.com> wrote in message
news:BE**********************************@microsof t.com...
You can use IO.StreamReader class to open the file then read it into a
datatable. After done, add this datatable to the dataset.
Here is the working function in VB.Net (can be easily converted to C#)

that
read a text file (can be CSV, tab delitmiting, or anything... You just

need
to specify what the seperator character is)and return a dataTable.

'Reading file to datatable
Private Function BuildDataTable(ByVal fileFullPath As String, ByVal
seperator As Char) As DataTable

Dim myTable As DataTable = New DataTable("MyTable")
Dim i As Integer
Dim myRow As DataRow
Dim fieldValues As String()
Dim f As IO.File
Dim myReader As IO.StreamReader
Try
'Open file and read first line to determine how many fields
there are.
myReader = f.OpenText(fileFullPath)
fieldValues = myReader.ReadLine().Split(seperator)
'Create data columns accordingly
For i = 0 To fieldValues.Length() - 1
myTable.Columns.Add(New DataColumn("Field" & i))
Next
'Adding the first line of data to data table
myRow = myTable.NewRow
For i = 0 To fieldValues.Length() - 1
myRow.Item(i) = fieldValues(i).ToString
Next
myTable.Rows.Add(myRow)
'Now reading the rest of the data to data table
While myReader.Peek() <> -1
fieldValues = myReader.ReadLine().Split(seperator)
myRow = myTable.NewRow
For i = 0 To fieldValues.Length() - 1
myRow.Item(i) = fieldValues(i).ToString
Next
myTable.Rows.Add(myRow)
End While
Catch ex As Exception
MsgBox("Error building datatable: " & ex.Message)
Return New DataTable("Empty")
Finally
myReader.Close()
End Try

Return myTable
End Function

Hope this helps.
VHD50.

"Amit Maheshwari" wrote:
I need to read text file having data either comma seperated or tab seperated or any custom seperator and convert into a DataSet in C# .

I tried Microsoft Text Driver and Microsoft.Jet.OLEDB.4.0 to read text file but could not get the data in correct format.

All columns are not coming in dataset and rows are messing up.

Suggestions please ???



Mar 23 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.