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.O LEDB.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 ???
4  12806 
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("MyTa ble")
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(file FullPath)
fieldValues = myReader.ReadLi ne().Split(sepe rator)
'Create data columns accordingly
For i = 0 To fieldValues.Len gth() - 1
myTable.Columns .Add(New DataColumn("Fie ld" & i))
Next
'Adding the first line of data to data table
myRow = myTable.NewRow
For i = 0 To fieldValues.Len gth() - 1
myRow.Item(i) = fieldValues(i). ToString
Next
myTable.Rows.Ad d(myRow)
'Now reading the rest of the data to data table
While myReader.Peek() <> -1
fieldValues = myReader.ReadLi ne().Split(sepe rator)
myRow = myTable.NewRow
For i = 0 To fieldValues.Len gth() - 1
myRow.Item(i) = fieldValues(i). ToString
Next
myTable.Rows.Ad d(myRow)
End While
Catch ex As Exception
MsgBox("Error building datatable: " & ex.Message)
Return New DataTable("Empt y")
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.O LEDB.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 ???
In some specail casees it wouldn't work, like--
FaxNumber,Compa ny,"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***@discussi ons.microsoft.c om> wrote in message
news:BE******** *************** ***********@mic rosoft.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("MyTa ble")
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(file FullPath)
fieldValues = myReader.ReadLi ne().Split(sepe rator)
'Create data columns accordingly
For i = 0 To fieldValues.Len gth() - 1
myTable.Columns .Add(New DataColumn("Fie ld" & i))
Next
'Adding the first line of data to data table
myRow = myTable.NewRow
For i = 0 To fieldValues.Len gth() - 1
myRow.Item(i) = fieldValues(i). ToString
Next
myTable.Rows.Ad d(myRow)
'Now reading the rest of the data to data table
While myReader.Peek() <> -1
fieldValues = myReader.ReadLi ne().Split(sepe rator)
myRow = myTable.NewRow
For i = 0 To fieldValues.Len gth() - 1
myRow.Item(i) = fieldValues(i). ToString
Next
myTable.Rows.Ad d(myRow)
End While
Catch ex As Exception
MsgBox("Error building datatable: " & ex.Message)
Return New DataTable("Empt y")
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.O LEDB.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 ???
In some specail casees it wouldn't work, like--
FaxNumber,Compa ny,"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***@discussi ons.microsoft.c om> wrote in message
news:BE******** *************** ***********@mic rosoft.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("MyTa ble")
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(file FullPath)
fieldValues = myReader.ReadLi ne().Split(sepe rator)
'Create data columns accordingly
For i = 0 To fieldValues.Len gth() - 1
myTable.Columns .Add(New DataColumn("Fie ld" & i))
Next
'Adding the first line of data to data table
myRow = myTable.NewRow
For i = 0 To fieldValues.Len gth() - 1
myRow.Item(i) = fieldValues(i). ToString
Next
myTable.Rows.Ad d(myRow)
'Now reading the rest of the data to data table
While myReader.Peek() <> -1
fieldValues = myReader.ReadLi ne().Split(sepe rator)
myRow = myTable.NewRow
For i = 0 To fieldValues.Len gth() - 1
myRow.Item(i) = fieldValues(i). ToString
Next
myTable.Rows.Ad d(myRow)
End While
Catch ex As Exception
MsgBox("Error building datatable: " & ex.Message)
Return New DataTable("Empt y")
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.O LEDB.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 ???
Hey, I got the solution, it was datatype problem when reading using
Microsoft.Jet.O LEDB.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*********@de v.com> wrote in message
news:eE******** ******@TK2MSFTN GP12.phx.gbl...
In some specail casees it wouldn't work, like--
FaxNumber,Compa ny,"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***@discussi ons.microsoft.c om> wrote in message
news:BE******** *************** ***********@mic rosoft.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("MyTa ble")
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(file FullPath)
fieldValues = myReader.ReadLi ne().Split(sepe rator)
'Create data columns accordingly
For i = 0 To fieldValues.Len gth() - 1
myTable.Columns .Add(New DataColumn("Fie ld" & i))
Next
'Adding the first line of data to data table
myRow = myTable.NewRow
For i = 0 To fieldValues.Len gth() - 1
myRow.Item(i) = fieldValues(i). ToString
Next
myTable.Rows.Ad d(myRow)
'Now reading the rest of the data to data table
While myReader.Peek() <> -1
fieldValues = myReader.ReadLi ne().Split(sepe rator)
myRow = myTable.NewRow
For i = 0 To fieldValues.Len gth() - 1
myRow.Item(i) = fieldValues(i). ToString
Next
myTable.Rows.Ad d(myRow)
End While
Catch ex As Exception
MsgBox("Error building datatable: " & ex.Message)
Return New DataTable("Empt y")
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.O LEDB.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 ???
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Simon |
last post by:
Hi all,
I have a process, where I take a dataset from an SQL call, and need to write
an XML file from that dataset.
The data set can contain 10's of tables, each with 100's of rows, and I have
no way of knowing what the tables are, or what they contain.
I am currently using the Dataset.WriteXML method passing an XMLWriter.
|
by: Scott M. Lyon |
last post by:
I've just discovered a bug in some code I wrote a little while ago, and I
need you guys' help to fix it.
My program imports data from a standard Excel Spreadsheet (just with
specific column headers). I used ODBC in my VB.NET program to read that
spreadsheet into a dataset, to make it easy to manipulate. The code I use to
read it is as the bottom of this posting.
|
by: hzgt9b |
last post by:
(FYI, using VB .NET 2003)
Can someone help me with this... I'm trying to read in an XML file... it
appears to work in that the DataSet ReadXML method dose not fail and then I
am able to access the table names that are in the XML file, but I'm not able
to access the rows.
Here's the code that I've got - it assumes that the fileName passed in
already exists:
Public Sub GetInput(ByVal fileName As String)
dsFileCopy = New...
|
by: Brian Parker |
last post by:
Here's a snippet of code I have:
==============================================
DataSet ds = new DataSet();
string strXMLFileName = Path.GetTempFileName();
StreamWriter sw = File.AppendText( strXMLFileName );
sw.WriteLine(@"<?xml version='1.0'?>");
sw.WriteLine(@"<Results>");
|
by: dba123 |
last post by:
I need some help and direction on what classes and an example or two
(article) on how to read an Excel Worksheet and insert one column into a
database table column. I am using .NET 2.0 only. What namespaces and
classes should I use and how?
--
dba123
| | |
by: rwiegel |
last post by:
I'm trying to read rows from an Excel file and display them in an
ASP.NET DataGridview. I am using C# for the code file. I am using
OleDb to read from the Excel file. The columns that contain text load
into the grid fine, but the columns that contain just numbers don't
show up at all. I tried converting the text of the cells to an
integer first, but I get an error for converting from a type DBNull.
Anybody who has any help at all, I...
|
by: ahtan |
last post by:
Hi,
I have a little problem reading a csv file. The following is the code that I have and it works, however, it doesn't read the first value in the csv file. In the csv file, I have:
1, test10, test11, test12
2, test20, test21, test22
3, test30, test31, test32
However, then I display the value in the dataset, I get:
2, test20, test21, test22
|
by: Lucvdv |
last post by:
This would better be described by 'serialization' than 'interop', but I
didn't find a newsgroup that seems closer on topic.
The problem in a few words: I save data with DataSet.WriteXML, but I get
different data back when I read it later with DataSet.ReadXml.
More detail:
|
by: tshad |
last post by:
I have a csv file that I am reading into my dataset. But I want to ignore
any rows that have nothing in the 3rd column - something like:
da = new OleDbDataAdapter("SELECT * FROM " +
Path.GetFileName(strFile) + "WHERE F3 is not null",csvConnection);
or Where F3 = ''
where F3 would be some type of column number. The problem is I don't have
any column names as I am reading this from a csv text file.
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| | |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
