Blogs

Import data into Dataverse(CDS): Part 3

Inkey, December 4, 2020 1703 Views

In the previous blogs, we saw how to import data into Dataverse(CDS) using Excel here and Dataflow here.

In this blog we will use a .NET console application to upload data from SharePoint file to Dataverse(CDS). We will need to create SharePoint Document Locations in Common Data Service.

Here are the steps.

InsertDataToDataverse : This method is used to insert data to Dataverse(CDS).

internal static bool InsertDataToCRM(string fileName,
                                     out StringBuilder errorLogMessage)
{
  errorLogMessage = new StringBuilder(string.Empty);
  bool isSuccess = true;
  try
  {
    MemoryStream memoryStream = GetExcelData(fileName);
    if (memoryStream == null)
    {
      errorLogMessage.AppendLine($"{Environment.NewLine}File '{fileName}' does not exists in SharePoint.");
      return isSuccess;
    }
    List<Entity> lstEntity = new List<Entity>();
    if (string.IsNullOrWhiteSpace(fileName) == false &&
        fileName.Equals("SharePointFile"))
    {
      lstEntity = GetFileData(fileName, memoryStream, ref errorLogMessage);
    }
    if ( lstEntity != null &&
        lstEntity.Count > 0)
    {
      ExecuteMultipleRequest newExecuteMultipleRequest = new ExecuteMultipleRequest();
      newExecuteMultipleRequest.Settings = new ExecuteMultipleSettings();
      newExecuteMultipleRequest.Settings.ContinueOnError = true;
      newExecuteMultipleRequest.Settings.ReturnResponses = true;
      newExecuteMultipleRequest.Requests = new OrganizationRequestCollection();
      IOrganizationService iOrganizationService = OrganizationService.GetOrganizationService();
      StringBuilder logMessage = new StringBuilder();
      ExecuteMultipleResponseItemCollection requestResponse =
        Plugin.ExecuteMultipleRequests(newExecuteMultipleRequest, ref errorLogMessage,  ref logMessage, false, 400, iOrganizationService, null);
      if (requestResponse != null)
      {
        if (errorLogMessage == null)
        { errorLogMessage = new StringBuilder(string.Empty); }
        // Display the results returned in the responses.
        foreach (ExecuteMultipleResponseItem responseItem in requestResponse)
        {
          if (responseItem.Fault != null)
          {
            CreateRequest failedCreateRequest = newExecuteMultipleRequest.Requests[responseItem.RequestIndex] as CreateRequest;
          }
        }
      }
    }
  }
  catch (Exception ex)
  {
    if (errorLogMessage == null)
    { errorLogMessage = new StringBuilder(string.Empty); }
    errorLogMessage.AppendLine(ex.Message);
  }
  return isSuccess;
}

GetExcelData : This method helps you to read excel file from SharePoint and returns memory stream of file.

private static MemoryStream GetExcelData(string fileName)
{
  MemoryStream memoryStream = null;
  try
  {
    string siteUrl = "https://test.sharepoint.com/sites/SharePoint_to_Dataverse/";
    string userId = "developer@Organization.onmicrosoft.com";
    string userPassword = "test123";
    using (ClientContext clientContext = new ClientContext(siteUrl))
    {
      SecureString password = new SecureString();
      userPassword.ToCharArray().ToList().ForEach(p => password.AppendChar(p));
      clientContext.Credentials = new SharePointOnlineCredentials(userId, password);
      List spList = "Files"; //Add sharepoint document folder name.
      memoryStream = ReadFileName(fileName, clientContext);
    }
  }
  catch (Exception ex)
  {
    throw ex;
  }
  return memoryStream;
}

ReadFileName : This method is used to read file data with the help of fileName and clientContext and return memory stream.

private static MemoryStream ReadFileName(string fileName, ClientContext clientContext)
{
  string sharePointFileName = string.Empty;
  string fldTitle = "LinkFilename";
  string documentName = "Files";
  string relativeUrlForServerFolder = "/sites/SharePoint_to_Dataverse/Files";
  string errorMsg = string.Empty;
  MemoryStream memoryStream = null;
  try
  {
    List list = clientContext.Web.Lists.GetByTitle(documentName);
    CamlQuery camlQuery = new CamlQuery();
    camlQuery.ViewXml = @"<View Scope='Recursive'><Query></Query></View>";
    camlQuery.FolderServerRelativeUrl = relativeUrlForServerFolder;
    ListItemCollection listItems = list.GetItems(camlQuery);
    clientContext.Load(listItems);
    clientContext.ExecuteQuery();
    for (int i = 0; i < listItems.Count; i++)
    {
      ListItem itemOfInterest = listItems[i];
      if (itemOfInterest != null)
      {
        sharePointFileName = Convert.ToString(itemOfInterest.FieldValues["FileLeafRef"]);
        if (sharePointFileName.Equals(fileName, StringComparison.InvariantCultureIgnoreCase) == true)
        {         
          memoryStream = ReadExcelData(clientContext, sharePointFileName);
          break;
        }
      }
    }
  }
  catch (Exception ex)
  {
    errorMsg = ex.Message;
  }
  return memoryStream;
}

ReadExcelData : This method is used to read specific Excel file from SharePoint and return memory stream.

private static MemoryStream ReadExcelData(ClientContext clientContext, string fileName)
{
  string errorMsg = string.Empty;
  string documentName = "Files";
  MemoryStream memoryStream = new MemoryStream();
  try
  {
    List list = clientContext.Web.Lists.GetByTitle(documentName);
    clientContext.Load(list.RootFolder);
    clientContext.ExecuteQuery();
    string relativeUrlForServerFile = $"{list.RootFolder.ServerRelativeUrl}/{fileName}";
    Microsoft.SharePoint.Client.File file = clientContext.Web.GetFileByServerRelativeUrl(relativeUrlForServerFile);
    ClientResult<Stream> data = file.OpenBinaryStream();
    clientContext.Load(file);
    clientContext.ExecuteQuery();
    if (data != null)
    {
      data.Value.CopyTo(memoryStream);
      memoryStream.Position = 0;
    }
  }
  catch (Exception ex)
  {
    errorMsg = ex.Message;
  }
  return memoryStream;
}

• . This is how we insert data in Dataverse(CDS):
  • Read the sheet cell values
  • Sync the custom field with the columns of the file so that the value passed in the columns will be stored in the custom fields at Dataverse(CDS).

GetFileData : This method is used for get file data to insert data in Dataverse(CDS) fields.

private static List<Entity> GetFileData(string fileName, MemoryStream memoryStream, ref StringBuilder errorMsg)
{
  List<Entity> listOfEntity = new List<Entity>();
  try
  {
    List<Tuple<string, string, CRMDataTypes, bool, string>> lstBankData = new List<Tuple<string, string, CRMDataTypes, bool, string>>();
    Tuple<string, string, CRMDataTypes, bool, string> tplBankData = new Tuple<string, string, CRMDataTypes, bool, string>("new_firstname", "FirstName", CRMDataTypes.SingleLineofText, false, string.Empty);
    lstBankData.Add(tplBankData);
    tplBankData = new Tuple<string, string, CRMDataTypes, bool, string>("new_lastname", "LastName", CRMDataTypes.SingleLineofText, false, string.Empty);
    lstBankData.Add(tplBankData);
    tplBankData = new Tuple<string, string, CRMDataTypes, bool, string>("new_amount", "Amount", CRMDataTypes.Currency, false, string.Empty);
    lstBankData.Add(tplBankData);
    //In sharepoint, SheetIndex = 1
    listOfEntity = GetDataFromTabularExcelFile(fileName, memoryStream, lstBankData, 1);
  }
  catch (Exception ex)
  {
    errorMsg.AppendLine(ex.Message);
  }
  return listOfEntity;
}

GetDataFromTabularExcelFile : This method is used to get data from excel file and return list of entity. In this method, we find the columns that exist in the column header and if we didn’t find any column in excel then we have to give them exception. If all columns exist, Add excel data into list.

private static List<Entity> GetDataFromTabularExcelFile(string fileName, MemoryStream memoryStream, List<Tuple<string, string, CRMDataTypes, bool, string>> lstRecordSchemaWithColumnsName, int sheetIndex)
{
  List<Entity> lstEntity = new List<Entity>();
  bool foundTheColumn = false;
  ISheet sheet = GetExcelSheet(fileName, memoryStream, sheetIndex);
  if (sheet != null)
  {
    List<Tuple<string, int, CRMDataTypes, bool, string>> lstRecordSchemaWithColumnsIndex = new List<Tuple<string, int, CRMDataTypes, bool, string>>();

    //Get the Header row index by picking the first element of the tuple.
    //This is because we do not have a fix header row.
    int sheetHeaderRowIndex = -1;
    //Split the columns name using comma and try to find the column index based on the column name.
    List<string> lstFirstTupleColumnName = lstRecordSchemaWithColumnsName[0].Item2.Split(',').ToList();
    foreach (string firstTupleColumnName in lstFirstTupleColumnName)
    {
      if (foundTheColumn == true)
      { break; }
  //Loop the records upto filled row 
      for (int rowIndex = 0; rowIndex <= sheet.LastRowNum; rowIndex++)  
      {
        if (foundTheColumn == true)
        { break; }
        IRow row = sheet.GetRow(rowIndex);
        if (row != null)
        {
          for (int sheetRowCellIndex = 0; sheetRowCellIndex <= row.Cells.Count - 1; sheetRowCellIndex++)
          {
            string cellValue = Convert.ToString(GetCellValue(row, sheetRowCellIndex));
            if (cellValue.Trim().Equals(firstTupleColumnName.Trim(), StringComparison.CurrentCultureIgnoreCase) == true)
            {
              sheetHeaderRowIndex = rowIndex;
              foundTheColumn = true;
              break;
            }
          }
        }
      }
    }
    if (sheetHeaderRowIndex == -1)
    { throw new Exception($"The columns '{lstRecordSchemaWithColumnsName[0].Item2}' do not exist into the provided '{fileName}' file."); }
    IRow sheetHeaderRow = sheet.GetRow(sheetHeaderRowIndex);
    if (sheetHeaderRow != null)
    {
      //Preparing a list of tuple containing the list with the cell index because
      //The cell index is not fixed in the provided file and we can pick the cell value only by using cellIndex.
      foreach (Tuple<string, string, CRMDataTypes, bool, string> tplRecordSchemaWithColumnsName in lstRecordSchemaWithColumnsName)
      {
        //Split the columns name using comma and try to find the column index based on the column name.
        List<string> lstColumnName = tplRecordSchemaWithColumnsName.Item2.Split(',').ToList();
        foundTheColumn = false;
        foreach (string columnName in lstColumnName)
        {
          if (foundTheColumn == true)
          { break; }
          for (int sheetHeaderRowCellIndex = 0; sheetHeaderRowCellIndex <= sheetHeaderRow.Cells.Count - 1; sheetHeaderRowCellIndex++)
          {
            string cellValue = Convert.ToString(GetCellValue(sheetHeaderRow, sheetHeaderRowCellIndex));
            if (cellValue.Trim().Equals(columnName.Trim(), StringComparison.CurrentCultureIgnoreCase))
            {
              if (tplRecordSchemaWithColumnsName.Item3 == CRMDataTypes.DateandTime)
              { lstDateTypeColumnCellIndexes.Add(sheetHeaderRowCellIndex); }
              lstRecordSchemaWithColumnsIndex.Add(new Tuple<string, int, CRMDataTypes, bool, string>(tplRecordSchemaWithColumnsName.Item1, sheetHeaderRowCellIndex, tplRecordSchemaWithColumnsName.Item3, tplRecordSchemaWithColumnsName.Item4, tplRecordSchemaWithColumnsName.Item5));
              foundTheColumn = true;
              break;
            }
          }
        }
      }
    }
    //Find the columns that exist into the column name tuple but not in the index tuple
    //it is because if we didn't find any column in excel then we have to give them exception
    List<Tuple<string, string, CRMDataTypes, bool, string>> lstColumnsDoNotExist = lstRecordSchemaWithColumnsName.Where(recordSchemaWithColumnsName =>
    {
      Tuple<string, int, CRMDataTypes, bool, string> tplRecordSchemaWithColumnsIndex = lstRecordSchemaWithColumnsIndex.Find(recordSchemaWithColumnsIndex =>
      {
        return recordSchemaWithColumnsIndex.Item1.Equals(recordSchemaWithColumnsName.Item1, StringComparison.CurrentCultureIgnoreCase) == true;
      });
      return tplRecordSchemaWithColumnsIndex == null;
    }).ToList();
    List<string> lstNotFoundColumns = new List<string>();
    lstColumnsDoNotExist.ForEach(recordSchemaWithColumnsName => lstNotFoundColumns.Add(recordSchemaWithColumnsName.Item2));
    string notFoundColumns = string.Join(",", lstNotFoundColumns.ToArray());
    if (string.IsNullOrWhiteSpace(notFoundColumns) == false)
    { throw new Exception($"The columns '{notFoundColumns}' do not exist into the provided '{fileName}' file."); }
    //Loop the records upto filled row  
    for (int rowIndex = sheetHeaderRowIndex + 1; rowIndex <= sheet.LastRowNum; rowIndex++) 
    {
      IRow sheetRow = sheet.GetRow(rowIndex);
      if (sheetRow != null) //null is when the row only contains empty cells   
      {
        string firstCellValue = Convert.ToString(GetCellValue(sheetRow, 0));
        Entity entity = new Entity(Entities.REVENUE);
        if (String.IsNullOrWhiteSpace(firstCellValue) == false &&
            firstCellValue.Equals("ACCOUNT DETAILS - BREAKDOWN BY CLIENT ACCOUNTS", StringComparison.CurrentCultureIgnoreCase) == false &&
            firstCellValue.Equals("Account", StringComparison.CurrentCultureIgnoreCase) == false &&
            firstCellValue.Equals("Number", StringComparison.CurrentCultureIgnoreCase) == false &&
            firstCellValue.Equals("DEALER NUMBER :", StringComparison.CurrentCultureIgnoreCase) == false &&
            firstCellValue.Equals("DEALER NAME :", StringComparison.CurrentCultureIgnoreCase) == false &&
            firstCellValue.Equals("Total Upfront Commission", StringComparison.CurrentCultureIgnoreCase) == false &&
            firstCellValue.Equals("Total Trail Commission", StringComparison.CurrentCultureIgnoreCase) == false &&
            firstCellValue.Equals("Total GST", StringComparison.CurrentCultureIgnoreCase) == false)
        {
          PrepareEntityAttributes(lstRecordSchemaWithColumnsIndex, sheetRow, entity);
          lstEntity.Add(entity);
        }
      }
    }
  }
  return lstEntity;
}

GetExcelSheet : This method reads the excel sheet and returns it.

private static ISheet GetExcelSheet(string fileName, MemoryStream memoryStream, int sheetIndex)
{
  ISheet sheet = null; //Create the ISheet object to read the sheet cell values  

  if (string.IsNullOrWhiteSpace(fileName) == false &&
      memoryStream != null)
  {
    var fileExt = Path.GetExtension(fileName); //get the extension of uploaded excel file  
    if (fileExt.Equals(".xls", StringComparison.CurrentCultureIgnoreCase))
    {
  //HSSWorkBook object will read the Excel 97-2000 formats  
      HSSFWorkbook hssfwb = new HSSFWorkbook(memoryStream); 
      if (string.IsNullOrWhiteSpace(sheetName) == false)
        sheet = hssfwb.GetSheet(sheetName); //get provided Excel sheet from workbook
      else
        sheet = hssfwb.GetSheetAt(sheetIndex); //get provided Excel sheet from workbook
    }
    else
    {
  //XSSFWorkBook will read 2007 Excel format
      XSSFWorkbook hssfwb = new XSSFWorkbook(memoryStream);   
      if (string.IsNullOrWhiteSpace(sheetName) == false)
        sheet = hssfwb.GetSheet(sheetName);
      else
        sheet = hssfwb.GetSheetAt(sheetIndex); //get provided Excel sheet from workbook   
    }
  }
  return sheet;
}

This is how we are importing the data from SharePoint to Dataverse(CDS).

To avoid the references errors we need to include below NuGet packages and code.

using Microsoft.SharePoint.Client;
using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Messages;
using Microsoft.Xrm.Sdk.Query;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Security;
using System.Text;

NuGet packages:

• SharePoint by Microsoft
• SharePoint.Client by Microsoft
• SharePoint.Client.Runtime by Microsoft
• SharePoint.Client.ServerRuntime by Microsoft
• NPOI by NPOI Contributors
• Newtonsoft.json

I hope this will help you!!

---

ATM Inspection PowerApp to ease ATM inspection and report generation process.
https://powerapps.microsoft.com/en-us/partner-showcase/inkey-solutions-atm-inspection/

Insert data into Many-to-Many relationship in Dynamics CRM very easily & quickly, using the Drag and drop listbox.
http://www.inkeysolutions.com/what-we-do/dynamicscrmaddons/drag-and-drop-listbox

Comply your Lead, Contact, and User entities of D365 CRM with GDPR compliance using the GDPR add-on.
https://www.inkeysolutions.com/microsoft-dynamics-365/dynamicscrmaddons/gdpr

Create a personal / system view in Dynamics CRM with all the fields on the form/s which you select for a particular entity using the View Creator.
http://www.inkeysolutions.com/what-we-do/dynamicscrmaddons/view-creator