MADHDA Business Solutions
  • Home
  • About Us
    • Privacy Policy
  • Services
    • Implementations
    • Support
    • Training
    • Upgrade
    • Business Analysis
  • Solutions
    • MS Dynamics 365 Business Central
    • MS Dynamics Nav
    • MS Dynamics 365 Sales/CRM
    • Power BI
    • Power Automation
  • Resources
    • Blog
  • Contact Us
  • 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?

5 August 2021

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.


 

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




  1. 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.



 

 

  1. 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.

 

  1. 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.







  1. Create Procedure “GetValueAtCell”.
  • In this procedure we can read the value of fields which are stored in excel sheets.



 

  1. Create “ImportHeaderData” Procedure.
  • In this procedure we will import “Header Data” sheet’s data to “Sales Header”



 

  1. 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





 

  1. 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.

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




 

  • Choose The File:





  1. 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.

 

Previous post: How to modify the Role Explorer (Menu Suites) in Business Central?
Next post: How we can create simple OData Web Service?

Categories

  • Microsoft Dynamics 365
  • Microsoft Dynamics Business central
  • Microsoft Dynamics NAV
  • Uncategorized

Recent Post

  • Business Central : Retrieving Azure AD User information via AL.
    March 20, 2023
  • Change Authentication method from Basic Auth to OAuth2 while accessing Business Central Webservice / APIs.
    February 27, 2023
  • How to use "Dictionary" Object type into Business Central.
    February 17, 2023
  • Post Transaction Using Configuration Package in Business Central.
    February 13, 2023
  • How to Import Image from Excel to BC using Azure function.
    February 6, 2023
 


    © Copyright 2020 | All Rights Reserved By MADHDA