AspAlliance.com LogoASPAlliance: Articles, reviews, and samples for .NET Developers
URL:
http://aspalliance.com/articleViewer.aspx?aId=1107&pId=-1
CodeSnip: Reading CSV Files Using Dataset
page
by Vishal Kumar Patil
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 63633/ 128

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

 


Product Spotlight
Product Spotlight 

©Copyright 1998-2019 ASPAlliance.com  |  Page Processed at 2019-08-25 2:58:57 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search