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

  • How to integrate Shopify with Business Central Standard Shopify Connector for a powerful and streamlined e-commerce management experience?"
    November 30, 2023
  • Send Email via Power Automate when record is getting inserted in Business Central custom Table Including a blob field.
    November 24, 2023
  • Media and Mediaset Data Types for Image Storage instead of BLOB in Business Central.
    November 20, 2023
  • E-VAT integration for GRA in Dynamics 365 Business Central.
    November 15, 2023
  • How XMLports used in Dynamics 365 Business Central for import and export the operations.
    November 10, 2023
 

    © Copyright 2020 | All Rights Reserved By MADHDA