Customer Engagement & Dynamics CRM Forum

 View Only
  • 1.  CRM 2015 data export and import issue - Excel related?

    Posted Dec 20, 2022 08:01 AM
    Hi all,

    We are still using CRM 2015 for now and have kicked off a D365 migration project.

    However for the time being, the business have come across an issue with trying to import data back into CRM.

    Scenario:

    User export cases data by clicking on EXPORT TO EXCEL

    User then opens file in Excel 365 app (not cloud) and adds data in then clicks on the IMPORT DATA button

    However, a vauage error pops up

    After trace log, I can see this error:
    MSCRM Error Report:
    --------------------------------------------------------------------------------------------------------
    Error: Object reference not set to an instance of an object.
    Error Message: Object reference not set to an instance of an object.
    Source File: Not available
    Line Number: Not available
    Request URL: 
    Stack Trace Info: [NullReferenceException: Object reference not set to an instance of an object.]
       at Microsoft.Xrm.Office.ExcelReader.GetColumnFormat(Int32 colIndex)
       at Microsoft.Xrm.Office.ExcelReader.<ParseHeaderRow>b__3(String stringValue, Int32 index)
       at System.Linq.Enumerable.<SelectIterator>d__5`2.MoveNext()
       at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
       at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
       at Microsoft.Xrm.Office.ExcelReader.get_Columns()
       at Microsoft.Xrm.Office.ExcelReader.ParseDataRows(IEnumerable`1 dataRows)
       at Microsoft.Xrm.Office.ExcelReader.get_Rows()
       at Microsoft.Crm.Spreadsheet.SpreadsheetAdapter.Validate()
       at Microsoft.Crm.Application.Pages.Import.ImportFileUploadPage.PopulateExcelMetadata(Byte[] fileContent)
       at Microsoft.Crm.Application.Pages.Import.ImportFileUploadPage.ProcessFileForImport(Byte[] inputBytes, Int32 inputLength, String contentType, String shortName)
       at Microsoft.Crm.Application.Pages.Import.ImportFileUploadPage.ProcessFile()
       at Microsoft.Crm.Application.Pages.Import.ImportFileUploadPage.ConfigureForm()
    ​


    Thing I have noticed:
    • If I open the Excel file and do nothing but click Save, the file size increases by about 10KB, it will then error when I try to upload.
    • If I open the Excel file and amend some data then click Save, it will then error when I try to upload.
    • If I open the file but then do nothing and close it and try to upalod, there's no error and it successfully gets uploaded.

    My conclusion is that Excel is to blame here, so I've tried to downgrade my 365 office installation to the August 2020 version, and lo' and behold, amending the exported Excel file and uploading works!


    However, we can't really downgrade everyones Office version, so im wondering if anyone has any ideas?


    ------------------------------
    Giovanni Vetere
    Westcoast Ltd
    UK
    ------------------------------


  • 2.  RE: CRM 2015 data export and import issue - Excel related?

    TOP CONTRIBUTOR
    Posted Dec 21, 2022 09:27 AM
    Hi Giovanni-
      Just as a guess, I am thinking that the GUID's do not match?  D365 generates a unique GUID for all records that will not be the same as the one issued in D2015.  Try opening the CRM 2015 spreadsheet and I believe it is hidden column "A".  (Also be sure that Excel columns A-D are present on the Import).  Compare that GUID to the one assigned to the record in D365.  Actually- compare all 4 hidden columns.  If the same, then I am wrong as I am just throwing out an idea.  
      A quick check can be done by exporting an abbreviated file from both CRM 2015 and D365, then compare...

    Hope this helps tho?
    Ron

    ------------------------------
    Ron Goetz
    Application Support Analyst
    Goddard Systems, Inc.
    Freeport IL
    ------------------------------



  • 3.  RE: CRM 2015 data export and import issue - Excel related?

    Posted Dec 21, 2022 09:32 AM
    Hi Ron,

    thanks for the reply, I believe you've misunderstood, this is all based on CRM 2015 data,  D365 is not involved at all

    ------------------------------
    Giovanni Vetere
    Westcoast Ltd
    UK
    ------------------------------



  • 4.  RE: CRM 2015 data export and import issue - Excel related?

    TOP CONTRIBUTOR
    Posted Dec 21, 2022 10:04 AM
    Got it.  Do you get the following message when you try to open the exported file the first time?

    Perhaps you need to rename the file to xxxxx.XLS, (Excel 97- 2003 version), edit it, save it and try to import it as an XLS file?

    ------------------------------
    Ron Goetz
    Application Support Analyst
    Goddard Systems, Inc.
    Freeport IL
    ------------------------------



  • 5.  RE: CRM 2015 data export and import issue - Excel related?

    Posted Dec 21, 2022 10:09 AM
    Unfortunately .XLS is not supported by CRM, only .xlsx

    ------------------------------
    Giovanni Vetere
    Westcoast Ltd
    UK
    ------------------------------



  • 6.  RE: CRM 2015 data export and import issue - Excel related?

    TOP CONTRIBUTOR
    Posted Dec 21, 2022 11:21 AM

    OK- one last try.  First off, you are correct in that only the following are acceptable formats for CRM 2015.


      Since the failure is related to your latest saved version of Excel, I would try to "Save As" one of the above formats and see if it changes anything?

    BTW- I did an export and import using Excel and everything worked OK.  Something is corrupting your file(s) between export and import.  As mentioned previously- compare columns A-D before and after saving, then compare each column of your files.  It could be as simple as overwriting a drop down cell,or formatting a date as text during cleanup before distribution for updating. 



    ------------------------------
    Ron Goetz
    Application Support Analyst
    Goddard Systems, Inc.
    Freeport IL
    ------------------------------



  • 7.  RE: CRM 2015 data export and import issue - Excel related?

    Posted Dec 22, 2022 04:44 AM
    Just had a check and comparison and everything matches.

    I guess what ever changes have happened to Excel 365 from version 2020ish don't play well with file exported via the Excel engine in CRM 2015.

    ------------------------------
    Giovanni Vetere
    Westcoast Ltd
    UK
    ------------------------------



  • 8.  RE: CRM 2015 data export and import issue - Excel related?

    Posted Dec 28, 2022 10:04 AM
    By chance does your export have a column with a concatenated field (like Full Name for the First and Last name, Address, etc), or a related table field?  Those won't re-import and will error upon import. You'll need to delete those columns from the spreadsheet. If say you had Full Name but not the First and Last name fields, you'll need to export again with those fields.  May not be your issue, but just throwing that out there just in case.

    ------------------------------
    Duane Wells
    CRM Administrator
    O'Neal Industries
    Birmingham AL
    ------------------------------



  • 9.  RE: CRM 2015 data export and import issue - Excel related?

    Posted Jan 04, 2023 07:29 AM
    So, I got in contact with MS Support and they are aware of this issue and no fix is coming.

    "Based on the information provided on case description it seems you are experiencing issue with importing Excel file. I apologize for the inconvenience caused; I will try my best to assist you with this issue.

    We have received cases with same issue in past from customer who are using old version of CRM, our internal team is aware of this issue, but we do not have any solution fix for old version. This issue is already fix in latest version of CRM. Also I can see that you are on the extended support: https://learn.microsoft.com/en-us/lifecycle/products/dynamics-crm-2015. This does not qualify for any break fix issues. You already know the workaround and I believe you have implemented that by downgrading Office 365 to August 2020 version. For permanent fix you will have to update to the latest version."



    Only solution is to downgrade Excel for now

    ------------------------------
    Giovanni Vetere
    Westcoast Ltd
    UK
    ------------------------------



If you've found this thread useful, dive deeper into User Group community content by role