Excel Buffer Patterns for Import or Update NAV Data from Excel Files

Today I want to present how to use Excel Buffer functionality in order to update/import data in NAV/D365 Business Central based on data from Excel files.

Example of what we want to achieve

To show the concept I made a very easy example, so let’s suppose we have the following customers with Location Codes:

And we need to create a report that will use an Excel file like this to change the Customer’s Location Codes:

So, the result of running the update report looks like this:

What we can use and challanges

When we need to work with Excel files in NAV, we have the Excel Buffer table. This table comes also with some built in functionality (standard Microsoft) in order to be very easy to fill the NAV Excel Buffer table:

OnPreReport()
ExcelBuffer.LOCKTABLE;
ExcelBuffer.OpenBook(ServerFileName,SheetName);
ExcelBuffer.ReadSheet;
AnalyzeData;

Functions “OpenBook” and “ReadSheet” are standard Microsoft functions and after you use them, the data is imported into Excel Buffer table in a tabular format, like this:

Now, a small challange is how we can use the data in that format ? We have 3 rows in Excel Buffer that we need to use to Update/Insert only 1 row in NAV/D365 Business Central. Basically, for every row in the Excel file, in Excel Buffer table there are as many rows as the number of columns in the file.

An idea of a simple solution, but not so performant

I saw some examples(ex: Report 81 Import Budget from Excel), but didn’t really like them because I wanted something more simple and quick to create. So I created my own method/pattern:

The idea is that in function “AnalyzeData” we loop through the records from Excel Buffer and if the Row Number is greater than 1 (meaning is the actual data, not the header), we set a filter on Excel Buffer with the Row Number and then we call another function “UseExcelRowToUpdateCustomer” passing the Excel Buffer by reference in order to have the filter. Here we copy the filter to a local variable and we loop through the filtered rows (3 in this example) and use the data to Update customer. Then, in “AnalyzeData” function, within the loop, we go to the Last record with that row and remove the filter on Row Number. When the Next function is called, the next record in the loop is the first record with the next Row Number and then we repeat the process until we reach the end of Excel Buffer 🙂

LOCAL AnalyzeData()
Window.OPEN(
  Text001 +
  '@1@@@@@@@@@@@@@@@@@@@@@@@@@\');
Window.UPDATE(1,0);

TotalRecNo := ExcelBuffer.COUNT;
IF ExcelBuffer.FINDSET THEN
  REPEAT
    RecNo += 1;
    Window.UPDATE(1,ROUND(RecNo / TotalRecNo * 10000,1));
    IF ExcelBuffer."Row No." > 1 THEN BEGIN
      ExcelBuffer.SETRANGE("Row No.",ExcelBuffer."Row No."); //filter on current row
      CheckExcelRow(ExcelBuffer);
      UseExcelRowToUpdateCustomer(ExcelBuffer);
      ExcelBuffer.FINDLAST; //skip other records with the current row. The next record will be the first of next row
      ExcelBuffer.SETRANGE("Row No.");
    END ELSE
      VerifyExcelBufferHeader(ExcelBuffer);

  UNTIL ExcelBuffer.NEXT = 0;

ExcelBuffer.DELETEALL;
Window.CLOSE;

LOCAL UseExcelRowToUpdateCustomer(VAR ExcelBuffer : Record "Excel Buffer")
ExcelBuffer2.COPYFILTERS(ExcelBuffer);
IF ExcelBuffer2.FINDSET THEN
  REPEAT
    CASE ExcelBuffer2."Column No." OF
      1: CustomerNo := ExcelBuffer2."Cell Value as Text";
      2: CurrentLocationCode := ExcelBuffer2."Cell Value as Text";
      3: NewLocationCode := ExcelBuffer2."Cell Value as Text";
    END;
  UNTIL ExcelBuffer2.NEXT = 0;

IF Customer.GET(CustomerNo) AND (Customer."Location Code" = CurrentLocationCode) THEN BEGIN
  Customer."Location Code" := NewLocationCode;
  Customer.MODIFY;
END;

Comparison with the standard solution

As you might noticed, in my above code we issue a Findset statement for each row in the excel file (in function UseExcelRowToUpdateCustomer). So, if we discuss about performance, this is not the best solution, but I presented it because I like it and the performance should be good enough for regular excel files (maximum 5000-10.000 rows, 1-3 columns). I tested it for example with 7500 rows and 3 columns and it ran in 6 seconds on my local machine.

In case of Excel files with more rows, you should use the standard way of doing this (see example in Report 81 “Import Budget from Excel”).

In this idea we use global variables for every value you want to use from the Excel file (Ex: CustomerNo, CurrLocationCode, NewLocation Code). Then, we will also store in a Temporary Excel Buffer, in field Comment information about what each column number represents. Finally, when we loop through the Excel Buffer records, we check what the column number is (using the Temporary Excel Buffer) and we populate the global variables accordingly. When all global variables are populated we can use them to update the Customer record. When the row changes, we need to reset the values of the global variables.

The simplified version of the code (just to get the idea) is this one:

LOCAL AnalyzeData()
Window.OPEN(
  Text001 +
  '@1@@@@@@@@@@@@@@@@@@@@@@@@@\');
Window.UPDATE(1,0);

TotalRecNo := ExcelBuffer.COUNT;
IF ExcelBuffer.FINDSET THEN
  REPEAT
    RecNo += 1;
    Window.UPDATE(1,ROUND(RecNo / TotalRecNo * 10000,1));
    
     IF ExcelBuffer."Row No." <> OldRowNo THEN BEGIN
      OldRowNo := ExcelBuffer."Row No.";
      CustomerNo := ''; CurrLocationCode := ''; NewLocationCode := ''
    END;

    IF ExcelBuffer."Row No." > 1 THEN BEGIN
      TempExcelBuffer.GET(1,ExcelBuffer."Column No.");
      CASE TempExcelBuffer.Comment OF 
        'CustNo': CustomerNo := ExcelBuffer."Cell Value as Text";
        'CurrLoc': CurrLocationCode := ExcelBuffer."Cell Value as Text";
        'NextLoc': NewLocationCode := ExcelBuffer."Cell Value as Text";
      END;
      UseExcelRowToUpdateCustomer(ExcelBuffer);
    END ELSE
      CreateExcelBufferHeader(ExcelBuffer);

  UNTIL ExcelBuffer.NEXT = 0;

ExcelBuffer.DELETEALL;
Window.CLOSE;

LOCAL CreateExcelBufferHeader(ExcelBuffer : Record "Excel Buffer")
TempExcelBuffer := ExcelBuffer;
CASE ExcelBuffer."Column No." OF
  1: TempExcelBuffer.Comment := 'CustNo';
  2: TempExcelBuffer.Comment := 'CurrLoc';
  3: TempExcelBuffer.Comment := 'NextLoc';
END;
TempExcelBuffer.INSERT;

LOCAL UseExcelRowToUpdateCustomer(VAR ExcelBuffer : Record "Excel Buffer")
IF (CustomerNo = '') OR (CurrLocationCode = '') OR (NewLocationCode = '') THEN
  EXIT;

IF Customer.GET(CustomerNo) AND (Customer."Location Code" = CurrLocationCode) THEN BEGIN
  Customer."Location Code" := NewLocationCode;
  Customer.MODIFY;
END;

*In production code use function FieldCaption instead of the hardcoded values, this is only to understand the concept !
This solution is much faster than the first one, but to my opinion not so elegant 🙂

The report has also a Request Page where the user needs to select the Excel File and the Sheet with the data to use for the update/insert (in case the file has multiple sheets):

Sample files and other things you can use

In order to see more about how this works, I uploaded sample files to GitHub repository:
https://github.com/andreilungu/Utilities/tree/master/NAV/Excel%20Buffer%20Usage%20Pattern
* please note that I exported the files from a NAV 2018 Chronus database.

PS: You can also use the Multi Column Log Layout created using DotNet List if you want to display in a layout eventual errors that can occur because of invalid data input by users in the Excel file:
–> C/AL Version;
–> AL Version.

If you like this or you want to suggest a better way for doing it, please write a comment with your opinion.