[NAV2015] Importing Excel file with Config Package Fails

yukileigh

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!

kriki

[Topic moved from 'NAV/Navision Classic Client' forum to 'NAV Three Tier' forum]

Karenh

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.