When importing data from an Excel sheet into Enterprise Studio using an Excel connection, a script error message appears about values not being compatible, and the import fails. Example of an Excel connection, Excel sheet, and error message:
The error message is caused by the fact that the cells in one or more data columns in the Excel sheet have different formatting. These differences in formatting may not always be visible. As can be seen in the figure above, the error message indicates to which data the error applies. This is an indication where to look in the Excel sheet. To solve the problem:
- Open the Excel sheet and select the column of which cells have different formatting.
- On the Home tab, in the Number Format box, select the format "Text", so that all cells in the selected column now have this formatting. Alternatively you can right-click the selected column, select Format Cells, and on the Number tab select category "Text".
Repeat this step for other columns if needed.
- Save the changes, close the file, and perform the import once again.
If your Excel file is locked for editing after the failed import, close the file, open the Windows Task Manager, and end any Excel tasks present in the processes list. Then retry editing it.