Coding, Day to Day

NAV2016 – How to Import Excel Data into NAV

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.