Import Utility Issue : ZK error when cell reference to another worksheet

Post Reply
User avatar
tkinte
Posts: 145
Joined: 18 Nov 2014, 08:24
Location: https://www.linkedin.com/in/tshitshi-kia-ntoni
Contact:

Hi.

The import utility throws the following error when cell reference to another worksheet in the same excel file :

Code: Select all

ZK Error evaluating cell 'Strategic_Goals-KSF'!C2
I am using MS Excel 2016 and Import Utility Release 1.6.5.

Thanks .

Tshitshi
User avatar
jonathan.carter
Posts: 1087
Joined: 04 Feb 2009, 15:44

Hi Tshitshi,

Thanks for your post.

We use a lot of computed cell values in our data capture spreadsheets when we use the Import Utility ourselves, so I wonder if there is something different about this particular cell / column?

It looks like the Import Utility is raising the error at the first row - C2 - of the Strategic_Goals-KSF sheet.

Thanks for mentioning the version of Excel that you are using. We have using the latest release of Excel on Windows and Mac ourselves but does the import run with other worksheets in the same document?
Have other worksheets been processed before this message is posted, or is the document simply the Strategic_Goals-KSF sheet?

More specifically, what is the nature of the formula that is being used to compute the value in C2? e.g. is it a particularly complex formula or is this something that has been previously imported without any issue?

If it is a complex calculation, it may help the import to create a new cell that refers to C2 (e.g. Z2), and import that value instead.

Let me know the answers to these questions and we can take it from there. If it is possible, it would be very helpful if you could send us a copy of your Import Specification (from the Edit Import Activity option, select “Download Import Specification”) and your source spreadsheet.

Jonathan
Essential Project Team
User avatar
tkinte
Posts: 145
Joined: 18 Nov 2014, 08:24
Location: https://www.linkedin.com/in/tshitshi-kia-ntoni
Contact:

Thank you Jonathan.

I send you the import specification with 2 excel files (with and without formulas). The import works fine without formulas.

Aditionnally, how can I set the Planning_action Status based on cells values : Plans_stratégiques!Planning_Status and Plans_stratégiques!Planning_Status_value in the FIN-Strategy-Arch-Type-ValuesOnly.xlsx

Best regards.

Tshitshi
You do not have the required permissions to view the files attached to this post.
User avatar
jonathan.carter
Posts: 1087
Joined: 04 Feb 2009, 15:44

Thanks very much for sharing this with us.

I have been able to replicate the behaviour (and error) that you are seeing.

It looks like some of the functions that Excel uses, e.g. the CONCAT that you are using have been updated in the latest / later release and this is causing a problem in the Import Utility, where it can no longer find these functions.

We have been using CONCAT and similar functions for some years in our data capture spreadsheets, so this is not a fundamental issue with what you should be able to do in a spreadsheet. We'll take a look at the versions of Excel that we expect to be able to support and make relevant upgrades in order to support Excel 2016 etc. and get back to you.

Jonathan
Essential Project Team
User avatar
tkinte
Posts: 145
Joined: 18 Nov 2014, 08:24
Location: https://www.linkedin.com/in/tshitshi-kia-ntoni
Contact:

Hi Jonathan.

Thanks for your help and attention.

Best regards.

Tshitshi
User avatar
jonathan.carter
Posts: 1087
Joined: 04 Feb 2009, 15:44

Hi Tshitshi,

We're going to continue to work on this but I just tried something that will resolve the issue for you now.

If you replace your uses of the 'CONCAT()' function with 'CONCATENATE()', the import should work as you need it to.
I have been able to replicate the issue that you are seeing when using the CONCAT() function but was able to successfully import the content when I changed CONCAT() to CONCATENATE().

Hopefully, the impact of making this change won't be too much in your spreadsheet but it should be worth it to avoid having to hard-code some of your values. As for why there are both of these functions in Excel and why one works and one does not, I'm not sure yet.

Regards

Jonathan
Essential Project Team
User avatar
tkinte
Posts: 145
Joined: 18 Nov 2014, 08:24
Location: https://www.linkedin.com/in/tshitshi-kia-ntoni
Contact:

Hi Jonathan.

Thank you for posting the workaround. But I still have the same error.

Did you test with MS Excel 2016 ?

Best regards.

Tshitshi
User avatar
jonathan.carter
Posts: 1087
Joined: 04 Feb 2009, 15:44

Yes, I tried this with Excel v15 (2017).

If there are any cells that the Import Utility might try to read that still contain "CONCAT()" that would cause the issue.

Regards

Jonathan
Essential Project Team
User avatar
tkinte
Posts: 145
Joined: 18 Nov 2014, 08:24
Location: https://www.linkedin.com/in/tshitshi-kia-ntoni
Contact:

Hi.

You are right. All fine now and fully enjoying the import utility experience.

Thank you very much for the support.

Best regards.

Tshitshi
Post Reply