Not the clearest error message

Context

In a previous blog, I complimented Business Central but bemoaned the quality of the Dataverse error messages. In this blog, I will discuss one of these error messages

This message turns out to be misleading in the scenario discussed below.

Complex Imports

A common process to import data, the begin with ‘Advanced Find’ in Dataverse. ‘Advanced Find’ produces a list of records that may be updated once exported into Excel. Matching against other data, say Business Central data, (usually involves using Excel ‘VLookup’ functionality). The result of ‘VLookup’ would then be copied from the respective columns and pasted as text. This is fairly common practise.

Identifying the Pattern that results in the error

One step I usually add is to change the table in the ‘Advanced Find Output’ to a range. Here lies the issue.
As soon as I do this, and try and import data, the above error occurs.

Resolution

Keep the exported ‘Advanced Find’ Excel file as native as possible. Do all workings in different Excel spreadsheet and only paste values back to original file. This usually allows import to progress without the error message.

To summarise – changing the Excel ‘Advanced Find’ file from a table to a range – invokes error above.