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;
        }


Comments

  1. Excel is a software program from Microsoft basic excel for beginners that is a part of Microsoft Office. Excel calculations is compiled for making and altering spreadsheets that are spared with a .xls expansion. It's general uses in corporate cell-based figuring, turn tables, and different diagramming devices. With an Excel spreadsheet, you could make a month to month spending plan, track costs of doing business, or sort and compose a lot of information calculations in spreadsheets of Excels.

    ReplyDelete
  2. Hiii, i got problem on this line
    " connString = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;"
    It says Configurationmanager doesnt exist in the current context

    ReplyDelete
    Replies
    1. Add reference to your solution.

      Delete
  3. above code executed successfully but none of record has been added..

    ReplyDelete

Post a Comment

Popular posts from this blog

How to show enlarge image when mouse hover on image or link in Asp.Net(c#) using JavaScript

StateCode and StatusCode Values for mostly used entities in Microsoft Dynamics CRM 2013

How to add custom filter lookup field based on OptionSet field selection in Dynamics CRM.

go to top image