In this course, we will learn how to read values from an Excel / CSV file in C#. Before we start make sure to subscribe to CRM Crate in order to stay updated in the world of Microsoft Dynamics CRM.

Convert the excel file into CSV.

• Open the excel file which you want to read using a C# application (Class Library or Console Application). Save the excel file as “CSV” or “Comma Separated Values” format.

Using method File.ReadAllLines to read the CSV

• We will read the CSV file by using a method called “File.ReadAllLines”.
• With help of File.ReadAllLines, we can open a file, read all lines of the file into a string array.
• We will use the format as ReadAllLines(FilePath, Encoding). The FilePath defines the location of file and encoding defines the type of encoding which needs to be applied to the file.

Code for reading the CSV & printing the CSV row values in the console output.

        //Main Method.
        public void ExcelCSVReader()
        {
            try
            {
                //Initialzing The Data Table.
                DataTable dt = new DataTable("DataTable");

                //Adding Columns In The Data Table.
                dt.Columns.Add(new DataColumn("Name", typeof(string)));
                dt.Columns.Add(new DataColumn("Age", typeof(string)));
                dt.Columns.Add(new DataColumn("Blood Group", typeof(string)));
                dt.Columns.Add(new DataColumn("Skill", typeof(string)));

                //Calling A Method Which Returns Processed Data Table By Consuming & Creating Data From Comma Separated CSV In The DataTable (Reading From Excel CSV).
                dt = ReadCSV(dt, "TableCSV.csv");

                //Iterating Over The DataTable To Fetch The Row Values In Variable Format.
                //In This For Loop, We Can Obtain The CSV Row Values As Per The Columns.
                foreach (DataRow dr in dt.Rows)
                {
                    string sName = dr["Name"].ToString().Trim();
                    string sAge = dr["Age"].ToString().Trim();
                    string sBloodGroup = dr["Blood Group"].ToString().Trim();
                    string sSkill = dr["Skill"].ToString().Trim();
                }
            }
            catch (Exception ex)
            {
            }
        }

        //Method Which Returns DataTable By Reading The Given Excel CSV File.
        public static DataTable ReadCSV(DataTable tableStructure, string filepath)
        {
            try
            {

                //Using File.ReadAllLines Method To Read The CSV File.
                var lines = File.ReadAllLines(filepath, System.Text.Encoding.UTF7);

                //Iterating Over The Above Formed String Array.
                for (int i = 0; i < lines.Length; i++)
                {
                    if (i != 0)
                    {

                        //Addition Of Rows In The DataTable By Using Separator As ",".
                        tableStructure.Rows.Add(lines[i].Split(','));
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return tableStructure;
        }

Below is the output of the above code if we print it in the console.

Thus we learned how to read a CSV file in C# using File.ReadAllLines method.