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
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;
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 + '@[email protected]@@@@@@@@@@@@@@@@@@@@@@@@\'); 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 + '@[email protected]@@@@@@@@@@@@@@@@@@@@@@@@\'); 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 🙂
Sample files and other things you can use
In order to see more about how this works, I uploaded sample files to GitHub repository:
* 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.