Toad World® Forums

Receiving E4018 error when importing Excel in Toad for Oracle


#1

When you import Excel data file with new formulas "=_xlfn.CONCAT(A5,"-",B5)", the export tool display the error "E4018 error". Would the cell have #NAME? in it to show that there is something wrong. (see screenshot bellow)

The issue is that it is a new function of Excel in Office 365. Starting in Office 365, CONCAT() is a valid Excel function, taking the place of CONCATENATE(). Although CONCATENATE() can still be used. This explains why it was not showing the normal error (#NAME?) for an error. Since we do not have Office 365, it was showing _xlfn.CONCAT(A2,"-",B2) when viewing the formula in the cell. The prefix _xlfn denotes that the function being used is one that is not supported by the Excel version it is currently being viewed in. Here is some documentation on CONCAT() and _xlfn.

https://support.office.com/en-us/article/concat-function-9b1a9a3f-94ff-41af-9736-694cbd6b4ca2

https://support.office.com/en-us/article/Issue-An-xlfn-prefix-is-displayed-in-front-of-a-formula-882f1ef7-68fb-4fcd-8d54-9fbb77fd5025

So, it looks like this import issue is occurring because Toad does not currently support new functions for Excel from Office 365. In this case, specifically CONCAT(). We checked the release notes and they say that it only supports up to Excel 2016, so we are good in the fact that we do not claim to be able to support this. Can you create an enhancement, to support this in the future?

If not, it would be a good idea to change the wording of the error message to something more meaningful. It seemed to be a bit misleading.

Workaround:

Copy the table to the clipboard and load from there instead of copying to a separate Excel without the formulas.

PS: For some reason, the importing works as expected in Toad 12.5 but Toad 12.9 and above doesn't work. 

Excel Sample File: