Microsoft Dynamics 365 Business Central: How to use Excel Buffer to Import data With Multiple Sheets?

Microsoft Dynamics 365 Business Central: How to use Excel Buffer to Import data With Multiple Sheets?

Hi Readers,

Previously, I posted a blog about How to use Excel Buffer to Export data in Business Central. Today in this post we will discuss about how to import excel data With Multiple Sheets to BC SaaS.

Before it, As you all are aware we can use Configuration Packages to import or export without customization.

In order to explain import function from excel simply and clearly, For Demo, let’s assume we would like to Import data To Sales Header Table with associated Sales Line entries.

1. Add new action button to import data from excel In SO PageExt.
   
   
   

2. Create New Procedure “ImportExcel” In “Sales Header” TableExt.
   
   
   

3. Variables:

• For Import Data using Excel Buffer, create a global variable ExcelBuffer which should always be set to Temporary as shown below.
  
  
• For Reading SheetName, create a global variable NameValueBufferOut which should always be set to Temporary as shown below.
  
  
  
  

4. Refer an “SelectFile” Procedure In “ImportExcel” Procedure, Create New Procedure.

• You need to use streams in Business Central SaaS.
  For Example:
  IStream: InStream;
• You can also get the file name of Excel. (FileName)
• GetSheetsNameListFromStream This procedure stores the all sheets name from the excel file to the NameValueBufferOut Table.
  
  

5. Continue “SelectFile” Procedure…

• From the NameValueBufferOut Table we can store SheetName in variable & Write Repeat Until loop for every SheetName.
• MaxRowNo :- Get last Line Number of new table and Row Number of excel.
  
  
  
  
  
  
  
  

6. Create Procedure “GetValueAtCell”.

• In this procedure we can read the value of fields which are stored in excel sheets.
  
  
  
  

7. Create “ImportHeaderData” Procedure.

• In this procedure we will import “Header Data” sheet’s data to “Sales Header”
  
  
  
  

8. Create “ImportLineData” Procedure.

• In this procedure we will import “Line Data” Sheet’s data in to associated SalesLine.
• If you need to validate the value, you can add validate under Evaluate
  
  
  
  
  
  

9. Create ExcelFile which having two different sheets, one is for header & second is for line data import.

• Header Sheet:
  
  
  
  

• LineData Sheet:
  
  
  
  

Ok, Lets Publish & Test It.

10. Open Record & Click “Import Excel” Action:
    
    
    
    
    

• Choose The File:
  
  

11. Now you can see data are successfully imported in SalesOrder.
    
    
    
    
    

END.

• This is just a simple demonstration. You can also put the function in the report and add some parameters to control the actions when importing.
  
  
  Hope this will help.
  
  

For more details, please visit our website www.madhda.com or contact us on Contact@madhda.com

Thanks to all.