Bulk upload data in Microsoft Dynamics CRM from Excel using c#, How to Import data from Excel in C#

In this blog I am going to explain how we could upload bulk data in CRM from Excel sheet using C#. For this demo I have taken a windows application, check the below steps and use the code as it is. This code also includes code how we could import Excel data.


1. First of all take a textbox and two different buttons. textbox for showing teh selected file path and first button for Browsing file and 2nd button is for uploading Data in CRM.

2. Place a OpenFileDialog control too in your form.

3. Now place code as below on Browse, Bulk_Upload button::


OrganizationServiceProxy _orgservice = CreateCrmServiceObject();
     
        private void btnBrowse_Click(object sender, EventArgs e)
        {
            DialogResult fd = openFileDialog1.ShowDialog();
            if (fd == DialogResult.OK)
            {
                textBox1.Text = openFileDialog1.FileName;
            }
        }

        //Browse excel file for bulk uploading in CRM
        private void btnUpload_Click(object sender, EventArgs e)
        {
            Stopwatch st = new Stopwatch();
            st.Start();
            if (textBox1.Text != "" || textBox1.Text != string.Empty)
            {
                string FileName = Path.GetFileName(textBox1.Text);
                string Extension = Path.GetExtension(textBox1.Text);
                DataTable dt = ImportData(textBox1.Text, Extension);
                if (dt.Rows.Count > 0)
                {
                    progressBar1.Minimum = 0;
                    progressBar1.Maximum = dt.Rows.Count - 1;
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        try
                        {

                            // Create Account entity object by specifying the entity logical name and Alternate key
                            Entity accountEntity = new Entity("account");
                            // Specify fields to update
                            accountEntity["name"] = dt.Rows[i][3].ToString();
                            accountEntity["telephone1"] = dt.Rows[i][8].ToString();
                            //Similarly add as much as fields you want to add in CRM from your sheet and put conditions if required before adding values above

                            // Create an ExecuteMultipleRequest object.
                            var multipleRequest = new ExecuteMultipleRequest()
                            {
                                // Assign settings that define execution behavior: continue on error, return responses.
                                Settings = new ExecuteMultipleSettings()
                                {
                                    ContinueOnError = false,
                                    ReturnResponses = true
                                },
                                // Create an empty organization request collection.
                                Requests = new OrganizationRequestCollection()
                            };

                            // Add a CreateRequest for each entity to the request collection.
                            CreateRequest createRequest = new CreateRequest { Target = accountEntity };
                            multipleRequest.Requests.Add(createRequest);

                            // Execute all the requests in the request collection using a single web method call.
                            ExecuteMultipleResponse mulResp = (ExecuteMultipleResponse)_orgservice.Execute(multipleRequest);

                            //Set the value for progress bar
                            progressBar1.Value = i;
                        }
                        catch (Exception ex)
                        {
                            MessageBox.Show(ex.Message);
                        }
                    }
                    st.Stop();
                    TimeSpan ts = st.Elapsed;
                    MessageBox.Show(dt.Rows.Count + " records in Account Entity Created/ Updated Successfully, for this Process Total Time Consumed is  " + ts);
                }
                else
                {
                    MessageBox.Show("Error occured while fetching records in Account Entity");
                }
            }
            else
            {
                MessageBox.Show("Please Select File First", "Warning", MessageBoxButtons.RetryCancel, MessageBoxIcon.Warning);
            }
        }

        //ImportData method used in UploadData Button click
        private DataTable ImportData(string FilePath, string Extension)
        {
            string connString = "";
            DataTable dt = new DataTable();
            switch (Extension)
            {
                case ".xls":
                    connString = ConfigurationManager.ConnectionStrings["Excel03ConString"]
                     .ConnectionString;
                    break;
                case ".xlsx":
                    connString = ConfigurationManager.ConnectionStrings["Excel07ConString"]
                     .ConnectionString;
                    break;
            }
            connString = String.Format(connString, FilePath);
            try
            {
                OleDbConnection connExcel = new OleDbConnection(connString);
                OleDbCommand cmdExcel = new OleDbCommand();
                OleDbDataAdapter oda = new OleDbDataAdapter();
                cmdExcel.Connection = connExcel;

                //Get the name of First Sheet
                connExcel.Open();
                DataTable dtExcelSchema;
                dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
                connExcel.Close();

                //Read Data from First Sheet
                connExcel.Open();
                cmdExcel.CommandText = "SELECT * From [" + SheetName + "]";
                oda.SelectCommand = cmdExcel;
                oda.Fill(dt);
                connExcel.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error occured: " + ex, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }

            return dt;
        }


public static OrganizationServiceProxy CreateCrmServiceObject()
        {

            OrganizationServiceProxy srvOnline = null;
            string strServerUrl = "https://yourSolution.crmX.dynamics.com";
            string userId = "username@yourSolution.onmicrosoft.com";
            string password = "YourPassword";

            string orgUri = string.Empty;
            orgUri = string.Concat(strServerUrl, "/XRMServices/2011/Organization.svc");

            IServiceManagement orgServiceManagement =
             ServiceConfigurationFactory.CreateManagement(new Uri(orgUri));

            AuthenticationCredentials creds = new AuthenticationCredentials();
            creds.ClientCredentials.UserName.UserName = userId;
            creds.ClientCredentials.UserName.Password = password;
            AuthenticationCredentials tokenCredentials = orgServiceManagement.Authenticate(creds);

            try
            {
                srvOnline = new OrganizationServiceProxy(orgServiceManagement, tokenCredentials.SecurityTokenResponse);
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error Occurs: " + ex.Message);
            }

            return srvOnline;
        }