C# Read CSV File

C# Read CSV File

Some one told me to write a program which reads CSV file. Well the code is very simple and easy to understand. To read CSV file, you can use class OledbProvider found under System.Data.Oledb namespace.

You can refer www.connectionstrings.com for connection string required to open your CSV file.

You can execute query like [ select * from xyz ] on CSV file.

Note:* Your CSV should contain each items placed in double quote separated by comma.

I have created xxx.csv file on local D:\ drive.

After executing the code you will get all data into data table, where you can do further processing on it.

You can bind data table to GridView or iterate data or filter it.

Here are the contents of my xxx.csv file.

      "First Name", "Last Name"

      "Satalaj", "More"

      "Rod","Jhonson"

      "Shane","Glover"

      

As you can see, the first line of CSV file is header content which is nothing but information about columns.

And here is sample code.
private void Form1_Load(object sender, EventArgs e)
   {
     // Prepare cnnection string  
     string connectionstring = 
     @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\;Extended Properties='text;HDR=Yes;FMT=Delimited';";

     // Create connection object 

     OleDbConnection connection = new OleDbConnection();

     // Assign connection to connection object 

     connection.ConnectionString = connectionstring;

     // Prepare command object 

     OleDbCommand command = new OleDbCommand();
     command.CommandType = CommandType.Text;

     // Tell command object to use connection object 


     command.Connection = connection;

     // prepare command statement 

     command.CommandText = "select * from [xxx.csv]";

     // Open connection with your csv file 

     connection.Open();

     // create data table object             

     DataTable dt = new DataTable();

     // Execute Reader and load the data into datatable 

     dt.Load(command.ExecuteReader());

     // Close the connection 

     connection.Close(); 
   }
Description

At connection string HDR=Yes means first row of CSV file contains header (columns) information of your table. We have placed the file at root drive. The location information is passed in connection string as d:\.

The select * from [xyz.csv] statement returns all rows in CSV file. You can also filter the records by using where clause in select statement.

VB.net Read CSV file example.

The code uses same CSV file to read in VB.net program. Let's see example of how to read csv file.

Private Sub Form1_Load(sender As Object, e As EventArgs)
	' Prepare cnnection string  
	Dim connectionstring As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\;Extended Properties='text;HDR=Yes;FMT=Delimited';"

	' Create connection object 

	Dim connection As New OleDbConnection()

	' Assign connection to connection object 

	connection.ConnectionString = connectionstring

	' Prepare command object 

	Dim command As New OleDbCommand()
	command.CommandType = CommandType.Text

	' Tell command object to use connection object 


	command.Connection = connection

	' prepare command statement 

	command.CommandText = "select * from [xxx.csv]"

	' Open connection with your csv file 

	connection.Open()

	' create data table object             

	Dim dt As New DataTable()

	' Execute Reader and load the data into datatable 

	dt.Load(command.ExecuteReader())

	' Close the connection 

	connection.Close()
End Sub

Tags:

Author

My name is Satalaj, but people call me Sat. Here is my homepage: . I live in Pune, PN and work as a Software Engineer. I'm former MVP in ASP.net year 2010.
Disclaimer: Views or opinion expressed here are my personal research and it has nothing to do with my employer. You are free to use the code, ideas/hints in your projects. However, you should not copy and paste my original content to other web sites. Feel free to copy or extend the code.
If you want to fight with me, this website is not for you.
 

I'm Satalaj.