Note: This is only for Microsoft Dynamics NAV, NOT Business Central. While the principle behind it is the same, Business Central uses different objects to achieve the intended result. What we are focused on in this post is how to import data from an excel spreadsheet to NAV (example, transfer data from excel to cash receipt journal records). This will be a brief guide on the import aspect.
Variables Required
- ExcelBuffer – Record “ExcelBuffer” – need to declare as a global variable
- FileManagement – Codeunit “File Management”
- ServerFileName – Text
- SheetName – Text
- TotalRows – Integer
- RowNo – Integer
- And any other variables to store the excel cell data
Supporting Function
// Get Value At Cell Function
LOCAL GetValueAtCell(RowNo : Integer;ColNo : Integer) : Text
IF ExcelBuffer.GET(RowNo,ColNo) THEN
EXIT(ExcelBuffer."Cell Value as Text")
ELSE
EXIT('');The above function is required to get the value from a specific cell in the excel spreadsheet based on the Row and Column of the cell and return the result as a string. You will have to do the necessary conversion to other data type if you want a numeric value and so on.
Import Function
First we will need to create a function (i.e. in a codeunit) that can be called from a page to trigger this import function. In this function we will trigger the dialog for the user to select the excel spreadsheet to import and also to select which sheet to import (if there are multiple excel sheets). The code snippet for this is as shown below
// Sample Import Call Function that takes in some parameters
ImportItemJournalExcel(JournalBatchName : Code[10];JournalTemplateName : Code[10])
// Get Import File
ServerFileName := FileManagement.UploadFile('Please select import File','File');
IF ServerFileName ='' THEN
EXIT;
// Get Sheet
SheetName := ExcelBuffer.SelectSheetsName(ServerFileName);
IF SheetName = '' THEN
EXIT;Additional Information: It might be possible to import a zip file of excel spreadsheets for processing and/or also to process multiple sheets in an excel file. But we will not be covering this here.
Next step is to open the excel document and get the total number of rows of data.
ExcelBuffer.LOCKTABLE;
ExcelBuffer.OpenBook(ServerFileName,SheetName);
ExcelBuffer.ReadSheet;
ExcelBuffer.RESET;
ExcelBuffer.SETCURRENTKEY("Row No.");
IF ExcelBuffer.FINDLAST THEN
TotalRows := ExcelBuffer."Row No.";Once we know the total number of rows, we need to identify which row to start taking data from and start looping the spreadsheet to extract the data. And in this loop, you can add the codes to insert the data to a record (example Gen. Journal Lines)
FOR RowNo:=3 TO TotalRows DO BEGIN // Start from Row 3
// Get Cell Data
SampleLocationData := GetValueAtCell(RowNo,2); // Column Number 2 : Location Code
SamplePONo := GetValueAtCell(RowNo,8); // Column Number 8 : PO Number
SampleProdCode := GetValueAtCell(RowNo,13); // Column Number 13 : Product Code
SampleProdDescr := GetValueAtCell(RowNo,16); // Column Number 16 : Product Description
// Do any logic here (i.e. insertion)
CustomRecord.INIT;
CustomRecord.LocationCode := SampleLocationData;
CustomRecord.PONo := samplePONo;
// continue adding data as needed
CustomRecord.INSERT();
END;Finally to end it off.
ExcelBuffer.CloseBook;
MESSAGE('Excel Import Completed');That’s all that there is to it.
You must be logged in to post a comment.