CRM Crate

How to read values from an Excel / CSV file in C#?

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.
read values from an Excel / CSV file in C#

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.

CRM Crate

All In One Platform For Learning Microsoft CRM.

5 1 vote
Article Rating
Subscribe
Notify of
2 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Rekha Swami
2 months ago

Crisp & clear article. This helped me. Thanks.

Ramesh Jadhav
2 months ago
Reply to  Rekha Swami

True

error: CRM Crate Security Engine - Right Click Is Disabled!
2
0
Would love your thoughts, please comment.x
()
x

Congratulations!

Well Done,
Welcome to CRM Crate

Stay tuned with us and get all latest updates and learning in Microsoft CRM and related techonologes.

Join CRM Crate Now!

Subscribe our newsletter for free and get all latest updates in Microsoft CRM & related technologies.

Learn & Grow Together