Import Excel File Using Excel Buffer

Hi All,

This article is based on a friend request. The Requirement was to import the data in a table From an Excel File.

Most of us know this feature is there in standard objects of Microsoft Dynamics NAV from a long time.

If you are one of those who don't know about it please go through the article.

Just for the Demo suppose i have a table where with following fields as shown below -

And the file that i want to import looks like something as shown below -

The Target is to import the data from excel in the Navision Table.

I have Created a Page to view imported entries and on the same page there is an action of Import From Excel.

For Importing data from Excel we will be using Standard ReadSheet Function in Excel Buffer Table in a Report. The Report will be Processing Only and will have options to Add the Entries or Replace the Entries.

Below is the code that have been used to achieve desired result -

First the Report Checks the option that user selected in Request Page of Replacing Entries or Adding Entries.

Then using Standard Function of Excel Buffer the Excel File Selected by User is Opened.

Then Using ReadSheet Function Excel Buffer Reads the Data in Selected Excel File.

Then System using a function try to get No. of Rows and Column in Selected Excel Sheet.

As First Row in Our Case Contain Caption, so it start inserting data in table from Row No 2 To Last Row

What system does in Function GetLastRowandColumns -

What system Does in Function Insert Data -

System read the data in a row and assign it to actual table fields.

To Open Excel File and Sheet Selection Code on Request Page is -

Now let's try to execute the process from the Page -

1. Select Import From Excel and Select Option to Add / Replace Entries. Once Clicked Ok System will ask for the Excel File to Import, as shown below -

Once File is selected and Excel File have multiple sheets, System will prompt to select the sheet also.
If everything goes well a confirmation message will appear and the data will be updated as shown below.

Hope you like to article and it being used by you in any business scenarios.

Comments

Improved performance features of Business Central 17

In the training I’ve done to some partners last week, when talking about performances I shared an example of an extension with 3 features that I think are not so well known but that have a significant impact on how your code performs, expecially on a SaaS environment. That extension used the following features: Partial record loading Temporary Tables QueryCategory The Partial record capability is a new feature available starting from Dynamics 365 Business Central 2020 Wave 2 (v17) and I think it’s one of my top personal desiderata from years. This feature permits you to load only the needed fields of a recordset (when accessing a data source) instead of loading the entire set of fields. This is particularly useful on reports and OData pages because you can avoid to do a SELECT * (plus JOINS with all the extension’s tables) and instead doing a SELECT of only the fields you need. Now you can do something like: procedure TestPartialRecord(): Decimal ...

Custom sheet name in RDLC Reports

Hi Readers, In this article we are going to discuss how to change the excel sheet name while using base Navision SAVEASEXCEL functionality. Applicable for RDLC reports of Navision and SSRS reports. Let’s say we have the following report that shows total sales by product category by territory: When we export this report to Excel, we’d like each territory to appear in its own worksheet and each worksheet named after its territory: How do we make this work? Easy! 1) Put every group on its own page. 2) name each page using the same field the group uses. Step 1: Put each group on its own page To put each group on its own page, open the group’s property window. Then, in the Page Breaks category, put a check mark in the Between each instance of a group check box. Click OK to complete this step. Step 2: Name the pages of the group With the group selected in the Row Group s panel, press F4 to open the Properties window. Next, expand the Group ...

UPGRADE CUSTOMIZATIONS TO V2 EXTENSION (Application and Data)

From Navision 2018 Onwards, Navision only support v2 Extension. Dynamic Navision Extension will make upgrade easier. If customizations are converted into V2 extension that means v2 Extension consist upgraded Data as well as customizations, also it will be easy to upgrade into new version of Navision and apply CU updates. Below are the detailed steps to upgrade customizations into Navision 2018 v2 Extensions . 1) you have Customized database in Microsoft Dynamics Navision 2018. 2) Create the new table for each table that is customized or also for customized field in standard table but while creating table for standard table, add the primary key and customized field with same ID and Name in new table. For E.g.: I have created 1 table “Person” and 2 Customized Field (Test, Doc No) in “Sales Header”. Now create 2 New tables in 2018. · Upg Sales Header -> Two base field (PK) and customized field -> Same ID and add primary key. Refer be...

Microsoft Dynamics 365 Business Central

Search This Blog