Excel Add-in connector freeze - Updating Workbook

(1) Share

Report

Posted on by Sunny2012

Hi Experts!

I have a client who is using Excel Add-in to upload approximately 20,000 lines per day.

After showing progress percentage, data connector shows Updating workbook..... and then process cannot be done while BC received 20,000 lines.

Is this MS bug? If so, when does MS issue patch and where?

Also, how many lines (maximum) is suggested using Excel Add-in?

I do not want to propose to the client using configuration package as an alternative solution because configuration package is used for data migration by MS partner like us and the feature is system admin module where usually ordinary end user is not allowed to access.

Below is the information of source data and target table

Target Table: General Journal Line (81)

Total number of line: approximately 20,000 - 25,000 per day.

Below is the columns in the file (total 12 column)

Journal Template Name

Journal Batch Name

Line No

Posting Date

Document No

Account Type

Account No

Description

Amount ($)

Shortcut Dimension Code 1

Shortcut Dimension Code 2

External Document Number

Total number of cell: 240,000 - 300,000

Each cell does not contain color, formula, grid lines etc.

Account type=G/L account only (nothing customer, vendor, bank etc).

I have the same question (0)

All responses (2)

Answers (1)

Suggested answer

Valentin Castravet 31,481 Super User 2025 Season 2 on at

Like (2)

Report

Its not a bug. Edit in excel is not recommended to be used as a mass data upload tool, as once you get into thousands of rows its just very slow. There is a reason as a MS Partner you use configuration packages rather than edit in excel for data import. With over 20,000 rows you've probably reached some of the Business Central operational limits hence the error - https://learn.microsoft.com/en-us/dynamics365/business-central/dev-itpro/administration/operational-limits-online.

I do not want to propose to the client using configuration package as an alternative solution because configuration package is used for data migration by MS partner like us and the feature is system admin module where usually ordinary end user is not allowed to access.

I disagree with this. If the client is a super user there is no reason not to allow them to use a tool that the system came with. You could setup a basic journal import config pack for them to use, so they don't go and create their own.

Otherwise, you can get a developer to create an xmlport or another sort of import, which would work a lot faster than Edit in Excel or Configuration Packages anyway; and if they use it daily it would be a huge time saver.

Valentin Castravet
Work: Zander ERP Services
Blog: Dynamics 365 Business Central Insights
LinkedIn: www.linkedin.com/in/valentin-c-0500a247/

Was this reply helpful? Yes No
Verified answer

YUN ZHU 95,729 Super User 2025 Season 2 on at

Like (1)

Report

Unfortunately, this is a limitation of Odata in Web Services.

https://learn.microsoft.com/en-us/dynamics365/business-central/dev-itpro/administration/operational-limits-online?wt.mc_id=DX-MVP-5004336

When I discussed how to add Edit in Excel to other pages, I mentioned that this backend uses Odata, so it also has this limitation. . .

https://yzhums.com/22941/

Hope this helps

Thanks.

ZHU

Was this reply helpful? Yes No