We had a business requirement to “Create” entity records in bulk using OOB “Import Data” feature of Dynamics 365.
While doing so, we were experiencing an issue while importing data in Dynamics 365, the error we were getting was “The source data is not in the required format”
We were importing the data as CSV file. Let’s say, we have one entity named “Referral” in CRM and we need to create multiple “Referral” records after importing CSV file.
As you can see in below table, CSV data needs to be mapped with “Referral” record field in CRM as below:
Now to import the CSV file in Dynamics 365, perform these steps – Navigate to Setting→Data Management→Import→Click on Import Data→ and then choose the file
We were experiencing failures while processing some of the data present in the CSV file, when we were trying to import CSV file with above data into Dynamics 365 containing “Date” and “Single Line of Text” data type. CRM was giving the OOB error “The source data is not in the required format” and hence, failed few of the records specifically for Date fields while processing.
As shown in below screenshot “Referred Date” was failed, as it was the date field with a format (MM/DD/YYYY) in CRM but while importing through CSV file the date format used was (DD/MM/YYYY).
We were not sure why this was happening, so to explore it further we did below:
We navigated to →Options→Formats for checking the current format for Date.
And found that Current Format in CRM selected was English (United States) as shown below:
When we continued exploring, we found that there was a discrepancy in the date format (DD/MM/YYYY) passed in CSV file and date fields format (MM/DD/YYYY) present in CRM. And hence while processing through OOB import functionality it failed with the description “The source data is not in the required format”.
It was happening, because in the US they follow MM/DD/YYYY format for date where as in UK they follow DD/MM/YYYY format. And in our case source system was the UK and target system was US due which we were getting that OOB error while processing the data with date field such as one which is processed above 30/01/2018, as there are no 30 months in the year.
Also, we noticed while importing, there were few data with date field which were processed successfully in excel like with date 12/01/2018. (Because there are 12 Months in a year)
We were able to get rid of this failure, just by one step as below:
Navigate to→Options→Formats→Select the Current Format as English (United Kingdom)
And it did work. We were able to import the CSV file. Without any failure.
Note: while using OOB importing functionality make sure that “Source” and “Target” date formats are same. Because if “Source” system format is different than “Target” then system will not allow to do so instead it will throw an error.
