Published:
23 Jan 2007
|
Abstract
This article demonstrates how to read CSV files using Dataset with the help of Microsoft.Jet.OLEDB.4.0 Provider.
|
|
by Vishal Patil
Feedback
|
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days):
37295/
59
|
|
|
Overview |
In most projects, an application needs to read CSV (Comma
Separated Value) files and load them into the database. Data which is used for
loading into database will usually be in the form of a text file wherein data
contained in the file is separated by commas. These files generally have .csv
extension.
In the below example we will demonstrate how to read a CSV
file using dataset.
|
Step - 1: Create the CSV file |
Create the text file using any text editor, preferably
NotePad, and save the file as Employee.txt.
Listing 1
EmpID,FIRSTNAME,LASTNAME,ADDRESS,PHONE,POSITION
1,John,Gower,"111 N Grove, Chicago, IL -60666",(111)222-3333,President
2,Sam,Ricards,"222 N Drive, Houston, TX - 411111",(222)222-3333,Vice-President
3,Lee,Sou,"333 W Madison, Dalla, TX - 411101",(333)222-3333,Chairmen
4,Tong,Lio,"444 S Hampton, Stamford, CT - 31111",(444)222-3333,Managing Director
5,David,Reid,"555 W Pine, Pinceton, NJ - 575675",(555)222-3333,Program Manager
|
Step - 2: Create the User Interface |
Create a Windows Application project with the name
ReadingCSVFile. To this, add a form which should look similar to the one below.
Figure 1
In the btnGetFile button click event add the following code.
Listing 2
Private Sub btnGetFile_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnGetFile.Click
Dim fileLookUp As New OpenFileDialog
fileLookUp.Title = "Get File"
fileLookUp.Filter = "Files (*.txt)|*.txt|All Files (*.*)|*.*"
If fileLookUp.ShowDialog() = DialogResult.OK Then
Me.txtFile.Text = fileLookUp.FileName
mFileName = System.IO.Path.GetFileName(Me.txtFile.Text)
mFolder = System.IO.Path.GetDirectoryName(Me.txtFile.Text)
End If
End Sub
In the above listing the OpenFileDialog object is instantiated
and the Title & Filter is set to it. Then the OpenFileDialog is opened to
select the file (in this case the file shown in Listing 1 is selected) from
which data will be read. Once the File is selected, File Name and Folder Path
will be captured which will be used in the Connectionstring (refer to listing
2).
Now, add the below code in the ReadData button click event.
Listing 3
Private Sub btnReadData_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnReadData.Click
Dim ConnectionString, CommandText As String
Dim conn As OleDb.OleDbConnection
Dim Command As OleDbCommand
Dim Count As Integer
Dim strMsg As String
ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & mFolder & "\;Extended Properties='text;HDR=Yes'"
CommandText = "select * from Employee.txt"
conn = New System.Data.OleDb.OleDbConnection(ConnectionString)
Command = New System.Data.OleDb.OleDbCommand(CommandText, conn)
conn.Open()
Dim da As OleDbDataAdapter = New OleDbDataAdapter(CommandText, conn)
Dim ds As DataSet = New DataSet
' fill dataset
da.Fill(ds, "Employees")
For Count = 0 To ds.Tables(0).Rows.Count - 1
strMsg = "EmpID : " & ds.Tables(0).Rows(Count)(0) & vbNewLine & "Name
" & ds.Tables(0).Rows(Count)(1) & ", " & ds.Tables(0).Rows(Count)(2)
MessageBox.Show(strMsg, "CSV File", MessageBoxButtons.OK)
Next
End Sub
In the above code the Connection string is set. Connection
string properties are explained below.
DataSource: Specifies the File Path.
Extended properties: Specifies type of file. In this case,
since it is a CSV file, the Text is specified.
HDR = Yes: Specifies that Column Headers will be omitted
while reading data.
Once the connection is set then the data is read into dataset
using Oledbcommand and OledbData Adapter. After data is read into the dataset, the
using for..Loop statement data is displayed in the MessageBox from the
datatable.
|
Downloads |
|
References |
|
Conclusion |
In this article we have seen how to read a CSV file using the
Dataset. Similarly, Excel files can also be read using Dataset and
Microsoft.Jet.OLEDB.4.0 Provider.
|
|
|
User Comments
Title:
Jay
Name:
jay
Date:
2011-04-07 2:52:38 AM
Comment:
Hi I found error: "The Microsoft Jet database engine could not find the object 'Employee.txt'." Plz help me out.......
|
Title:
: Ram kumar
Name:
: Ram kumar
Date:
2011-02-09 7:47:14 AM
Comment:
hi friends can u tell me about, how can i see to view the data using to csv(text)file. in php if u have ant idea than tel me ..and my e-maail id is sunnycse999@gmail.com
|
Title:
CSV file
Name:
Jagadish
Date:
2008-08-12 6:42:07 AM
Comment:
i want to run automated .csv file in my office.but in that firstname and lastname in between comma 'll come that time very problem.manually delete that comma and run that file and store in to table.how that overcome problem please help me
|
Title:
Something not right
Name:
Ken
Date:
2008-06-07 7:59:44 PM
Comment:
I cannot seem to fill the dataset. The dialogue etc...seems stable but it errors when it gets to the dataset side. I have added the "Employees" dataset to the application but it still stops on the fill....
|
Title:
Reading into IEnumerable for use with LINQ
Name:
Mary James
Date:
2008-04-12 4:07:41 AM
Comment:
An alternative would be to use the free library at http://www.codeproject.com/KB/linq/LINQtoCSV.aspx
That supports tab delimited files, files with values that contain commas, international date formats, etc.
|
Title:
Good
Name:
Aurangzeb
Date:
2008-01-26 3:33:26 PM
Comment:
Can any one guide me how to create csv file from database to .txt(csv format) and from .txt(csv format) to database.
mirza_aurangzeb_ashraf@yahoo.com
|
Title:
Nice
Name:
Yogesh
Date:
2007-06-16 5:44:47 AM
Comment:
nice one
|
Title:
worked well
Name:
Glenn Visca
Date:
2007-05-06 4:25:19 AM
Comment:
Hi and thanks ... this worked well.
It tool me a little while to work out what the string was in the da.fill statement. After some trial and error I worked out this was assigned to the tablename property.
Also worth noting that should you elect to include headers in your CSV file, those headers will be assigned to the ColumnName property and become usable in other code pieces. I found this handy when wanting to use databindings.
Thanks again.
|
Title:
CSV Files Containig Unocides
Name:
Sohrab Hashemi
Date:
2007-02-18 2:38:35 AM
Comment:
Hi, I have a problem with reading those CSV files that containes some unicodes. Can you help me?
sohrab_hashemi2005@yahoo.com
|
Title:
Reading CSV Files Using Dataset
Name:
Prakash
Date:
2007-02-02 2:39:54 AM
Comment:
hi.. i found ur code wil work for my requirement.. but wats the problem s it s giving an error as couldn't find installable isam... wats that. i need to use text file as database. how to pass to datasource if i have my file text file c: and the file name is sample.txt.
|
Title:
Performance problem
Name:
alex.lvovich
Date:
2007-01-28 10:06:45 AM
Comment:
Yes, it certanly the option. But I used similar solution to get data of 70mb text file, it allocated X10 memory in RAM. PC with 512 mb if RAM simply crashes. I used common solution for it - StreamReader.
|
|
Product Spotlight
|
|