The import and export framework is a commonly used tool. It works fine when the data is correct, and you do not get errors. However, this is of course not always the case. Scenarios with 3000 items, customers or vendors that need to be imported always have several lines that result in errors. And it is quite hard to get a good overview of the errors.
Now, we can of course solve this by manually correcting all lines and if we have a small number of lines, this is fine. Here is an example for rerun the corrected error lines:
But when there is a higher number of errors with a higher complexity, it would be much easier to just be able to export the errors and correct them in Excel and import them again.
Unfortunately, this is not by default possible in the import and export framework. But I have good news, there is a small tweak that makes this almost possible!
In the code at the end of this blog you will find a way that makes it possible to export your errors with all the related data with an Export failed button.
The tool is not perfect, but it helps a lot, gives a good overview of your errors, and makes it easier to solve them. But it is also perfect for creating data migration reports. (Getting the error in clear list is impossible.) When you have exported the errors, you will get a CSV file which you have to open in Excel. You must convert the text to columns by using the ”convert text to columns” wizard.
After that, you must remove the columns that you do not use as well as the column with error info (advised is to do this after you have solved the issue in Excel or the setup in D365).
Example of columns that must be removed.
- DefinitionGroup
- ExecutionId
- IsSelected
- TransferStatus
- Columns where the configuration key is turned off
- ErrorInfo
After this, save it as an Excel file (.XLSX), (.XSL is not supported with difx) and you are ready to import your earlier errors again.
The tweak is not 100% perfect, but the advantage in relation to the standard solution is huge.
Two other handy tricks for the import and export framework:
- MS is improving, the issue with no unique record error has become better. Here is an example for importing the Sales Lines, you can see on the column name where to look:
- Failed to insert record into staging table. The keys of the record are DEFINITIONGROUP, EXECUTIONID, INVENTORYLOTID, PARTITION. Duplicate records must be removed from the file prior to import.-The duplicate key value is (5637144576, sales lines, sales lines-2021-08-16T13:40:36-51DA388B72D94FB99A4D760957941D00, )
- Another handy tip is keeping the Excel name equal to the entity name, so you don’t have to think about the mapping
Finally, the code (use it at your own risk, and share your improvements). I assume that no additional explanation is required for creating the menu item and the form extensions. I hope this will help you with your errors in the import and export framework. Good luck!!
[ExtensionOf(formStr(DMFDataVizualization))]
final class DMFDataVizualizationXX_Extension
{
public void export()
{
TextStreamIo io =TextStreamIO::constructForWrite();
DMFEntity entity;
str headers;
select firstOnly EntityTable, EntityName from entity where entity.EntityName == defGroupExecutionTable.Entity;
if (entity.EntityTable)
{
sysDictTable dictTable = new sysDictTable(tableName2id(entity.EntityTable));
Query q = new Query();
QueryBuildDataSource qbds = q.addDataSource(dictTable.id());
SysQuery::findOrCreateRange(qbds, fieldName2Id(dictTable.id(), 'TransferStatus')).value(queryValue(DMFTransferStatus::Error));
SysQuery::findOrCreateRange(qbds, fieldName2Id(dictTable.id(), 'DefinitionGroup')).value(defGroupExecutionTable.DefinitionGroup);
SysQuery::findOrCreateRange(qbds, fieldName2Id(dictTable.id(), 'ExecutionId')).value(defGroupExecutionTable.ExecutionId);
QueryRun qr = new QueryRun(q);
for (int i=1; i<=dictTable.columnCnt(); i++)
{
SysDictField sdf = new SysDictField(dictTable.id(),dictTable.fieldCnt2Id(i));
if(isConfigurationKeyEnabled(sdf.configurationKeyId()))
{
if(headers)
{
headers += ',';
}
headers += strFmt('"%1"',dictTable.fieldName(dictTable.fieldCnt2Id(i)));
}
}
headers += ',"ERRORINFO"';
io.write(headers);
while (qr.next())
{
str line;
Common buffer = qr.get(dictTable.id());
for (int i=1; i<=dictTable.columnCnt(); i++)
{
SysDictField sdf = new SysDictField(dictTable.id(),dictTable.fieldCnt2Id(i));
if(isConfigurationKeyEnabled(sdf.configurationKeyId()))
{
if(line)
{
line += ',';
}
line += strFmt('"%1"', buffer.(dictTable.fieldCnt2Id(i)));
}
}
line += ','+this.logText(buffer);
io.write(line);
}
System.IO.Stream stream = io.getStream();
File::SendFileToUser(stream, dictTable.name()+'.csv');
}
}
public display SysInfoLogStr logText(Common _staging)
{
DMFStagingValidationLog log;
str logStr ;
SysInfoLogStr logText;
FieldId execFieldId, defGroupFieldId;
execFieldId = fieldName2Id(_staging.TableId,identifierStr(ExecutionId));
defGroupFieldId = fieldName2Id(_staging.TableId,identifierStr(DefinitionGroup));
while select ErrorMessage, RecId from stagingValidationLog
order by log.RecId desc
where log.DefinitionGroupName == _staging.(defGroupFieldId)
&& log.ExecutionId == _staging.(execFieldId)
&& log.StagingRecId == _staging.RecId
&& log.EntityName == defGroupExecutionTable.Entity
{
logStr = strRem(stagingValidationLog.ErrorMessage,'\n');
logText += strRem(logStr,'\r');
}
return logText + '"';
}
[FormControlEventHandler(formControlStr(DMFDataVizualization, XXExptortErrors), For mControlEventType::Clicked), SuppressBPWarning('BPParameterNotUsed', 'Parameter required by the event interface.')]
public static void FormButtonControl1_OnClicked(FormControl sender, FormControlEventArgs e)
{
FormRun form = sender.formRun();
form.export();
}
} Related information Data entities overview – Finance & Operations | Dynamics 365 | Microsoft Docs
Please check for a related blog post this link DMF