Post
Ask Question

How To Read CSV File Using C#

In this article, we will learn how to read an Excel file using Microsoft’s Interop Excel library. There are lots of free/Paid excel libraries available in Nuget manager. You can check my other article of same series with other excel library. We will make a new Windows application using C# to implement this. Let’s start.

  1. Open Visual Studio. Select New Project, then Windows Form Application.
  2. Name it as you want. My application's name is ReadExcelFileApp.
  3. Assemblies, Extensions, then to the Microsoft.Office.Interop.Excel.
  4. If you are using Visual Studio 2010, then you can add Reference using two ways:
    Right-click on References, then select Add Reference. After that, a dialog appears,
    1. Click on the Browse button, C drive, Microsoft Office, Office12, then open EXCEL.EXE.
    2. Select .NET tab then choose Microsoft.Office.Interop.Excel.
  5. After adding Reference, add namespace using Excel = Microsoft.Office.Interop.Excel and other namespaces are shown in the figure.
  6. Add two buttons: Choose, Read File, and Close. 
  7. Add a DataGridView to see the result (excel data).
  8. Create a method ReadExcel, which returns a datatable using the following logic. 
    public DataTable ReadExcel(string fileName, string fileExt) 
{
    string conn = string.Empty;
    DataTable dtexcel = new DataTable();
    if (fileExt.CompareTo(".xls") == 0)
    {
        conn = @"provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Extended Properties='Excel 8.0;HRD=Yes;IMEX=1';"; //for below excel 2007
    }
    else
    {
        conn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties='Excel 12.0;HDR=NO';"; //for above excel 2007
    }
    using(OleDbConnection con = new OleDbConnection(conn)) 
    {
       try 
       {
           OleDbDataAdapter oleAdpt = new OleDbDataAdapter("select * from [Sheet1$]", con); 
           //here we read data from sheet1
           oleAdpt.Fill(dtexcel); //fill excel data into dataTable
       } 
       catch {}
     }
     return dtexcel;
}
  9. Let's discuss the ReadExcel() method.
  10. Firstly, we will decide whether the file has the extension .xls or .xlsx because there is a difference between the connection strings of both files.
  11. If the file has extension .xls, then the connection string will be the following: 
    provider=Microsoft.Jet.OLEDB.4.0;Data Source='fileName';Extended Properties='Excel 8.0;HRD=Yes;IMEX=1';
    Otherwise 
    Provider=Microsoft.ACE.OLEDB.12.0;Data Source='FileName';Extended Properties='Excel 12.0;HDR=NO';"
  12. Here, HDR is the header field. Depends upon you whether you want to add it or not,
    IMEX=1 is used to retrieve the mixed data from the columns.
  13. Now, by using the OleDbConnection, define an OleDbDataAdapter. 
    OleDbDataAdapter oleAdpt = new OleDbDataAdapter("select * from [Sheet1$]", con);
  14. Here Sheet1 is the sheet number that you want to select, you can select any sheet e.g. Sheet2, Sheet3, etc. If you want to choose some specific columns,   then you can. For example, if you want to read just two columns, say Name and Salary, from the Excel file, then your query should be like the following: 
    Select Name,Salary from [Sheet1$]
  15. If there are no headers in complex Excel files, then you can select columns like F1, F20, etc. In that case, the query be like the following: 
    Select F11,F41,F70 from [Sheet1$]
  16. Add the following logic in button click events. 
    private void btnChooseFile_Click(object sender, EventArgs e) 
{
    string filePath = string.Empty;
    string fileExt = string.Empty;

    OpenFileDialog file = new OpenFileDialog(); //open dialog to choose file

    if (file.ShowDialog() == System.Windows.Forms.DialogResult.OK) //if there is a file chosen by the user
    {
        filePath = file.FileName; //get the path of the file
        fileExt = Path.GetExtension(filePath); //get the file extension

        if (fileExt.CompareTo(".xls") == 0 || fileExt.CompareTo(".xlsx") == 0) 
        {
            try 
            {
                 DataTable dtExcel = new DataTable();
                 dtExcel = ReadExcel(filePath, fileExt); //read excel file
                 dataGridView1.Visible = true;
                 dataGridView1.DataSource = dtExcel;
            } 
            catch (Exception ex) 
            {
                  MessageBox.Show(ex.Message.ToString());
            }
         } 
         else 
         {
             MessageBox.Show("Please choose .xls or .xlsx file only.", "Warning", 
             MessageBoxButtons.OK, MessageBoxIcon.Error); //custom messageBox to show error
         }
   }
}


private void btnClose_Click(object sender, EventArgs e) 
{

    this.Close(); //to close the window(Form1)
}
  17. After choosing the file, the Result will be like the following whether you upload a .xls or .xlsx file.   

I hope you find it simple. Thank you very much.


Similar Articles