reading text file to dataset

[Amit Maheshwari]

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 ???

 

[Guest]

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]

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..???

 

[Amit Maheshwari]

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..???

 

[Amit Maheshwari]

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.

 

[bulbul]

Need Rest Of The Code!!

Hey Amit!
Could you please post rest of the code. I mean code that is reading the text file and the rest.
Thank you,

bulbul

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 ???