[NAV2015] Importing Excel file with Config Package Fails

yukileighyukileigh Member Posts: 1
edited 2018-11-09 in NAV Three Tier
Need help to import a large Excel file!

In NAV 2015, we are trying to import a 12.8 MB (148,750 rows x 27 columns) Excel file of sales forecast data using Configuration Package, but it stops at exact 17,678 records. There is no pop-up error message, no event log error, and nothing special from SQL analyzer.

NAV: 2015 (version 8.00.45483)
Excel version: 2016, 64-bit
Memory usage while importing: 60%~70%
Service Tire Settings:
"Max. no. of XML records to send": 5000
"Chunk Size": 28
"Max Upload Size": 300

Here are what have been tired and the corresponding results:
- Change configuration file "Max. no. of XML records to send" to 500,000, restart Service Tire, and import the original file--> Fail, stop at 17,678
- Run a Powershell script to scrub any possible illegal characters in the original file, then import --> Fail, stop at 17,678
- Import several different files of more than 17,678 rows --> All fail
- Import a file with only row 1 to row 17,678 --> Success
- Import a file with row 17,679 to row 35,356 (17,678 rows in total) --> Success
- Split the original file into several smaller ones of only 10,000 rows, then import them --> Success
- Import only row 17,679 --> Success
- Import file of 10, 100, 1000 rows, respectively --> All success

It looks like the magic number 17,678 is some sort of limit within NAV. I found something in Codeunit 424:
LOCAL CheckNoOfRows(NoOfRows : Integer;VAR AnalysisView : Record "Analysis View")
IF ExcelVersion = '' THEN
IF NOT ISNULL(xlApp)THEN
ExcelVersion := COPYSTR(xlApp.Version,1,MAXSTRLEN(ExcelVersion));

IF (ExcelVersion < '12.0') AND (NoOfRows > 65000) THEN
ERROR(Text032,65000,AnalysisView.FIELDCAPTION("Date Compression"),AnalysisView.TABLECAPTION);

If the limit is really 65,000. I just don't understand why the file stopped at 17,678 rows, which is 1/4 of the 65,000 limit? If 65,000 is not the limit, are there any work around so we can import this file? Any ideas, suggestions? Thanks!

Answers

  • krikikriki Member, Moderator Posts: 9,088
    [Topic moved from 'NAV/Navision Classic Client' forum to 'NAV Three Tier' forum]

    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


  • KarenhKarenh Member Posts: 209
    The easiest thing to do is copy the import file to a second file. In the first file remove records after row 17679. In the 2nd file, remove records before 17680, but not the header row. Import both files.
Sign In or Register to comment.