Dynamics NAV Excel Import
In Dynamics NAV I can use the Excel Buffer functions to import an Excel Worksheet into the Excel Buffer table and then use the data to insert or update records in the database, looking at the existing Reports in the Cronus database to import from Excel, they use a Case Statement to insert or update the data.
Existing Example
In this piece of code they are using a Case Statement and the Excel Buffer field “Cell Value as Text” to decide what to do with the data.
In this piece of code they are using a Case Statement and the Excel Buffer field “Comment” to decide what to do with the data.
Custom Excel Imports
When creating custom imports and exports to Excel, I know what data should be in what column, I want the system to error if the data is wrong, I could use a case statement on the “Column No.” to decide what to do with the data, but often the column sequence does not mirror how the data would be manually input which makes it harder to Validate the data on import.
The way I often construct the Excel import is different to the Cronus Samples, I want much more control over the data, the ability to loop by “Row No.” and return the column data in any sequence when and as I want it.
I will be using the Sales Price table in my example, the Sales Price data is used to return different prices for different “Sales Types” for an Item or Variant with a “Starting Date” and “Minimum Quantity”, also a “Currency Code” or “Unit of Measure Code” can be added into the data.
Table: 7002 Sales Price
- Primary Key: Item No.,Sales Type,Sales Code,Starting Date,Currency Code,Variant Code,Unit of Measure Code,Minimum Quantity
- Field: “Sales Type”, Options = Customer,Customer Price Group,All Customers,Campaign
Page: 7002 Sales Prices
If I run the Sales Prices page from the designer adding the “Currency Code” field, there are only two records in the Cronus data one is in LCY the other in the EUR Currency, both have a “Starting Date” but neither have a “Unit of Measure”, as the “Sales Type” is for “All Customers” there is no “Sales Code”, the sequence of the columns is how I would input the data manually from “Sales Type” across to “Starting Date”.
Exporting the Data
I want to export the Primary Key fields and the “Unit Price”, offer two new columns to trigger the Import of the Sales Prices, to do this I created a report to export the columns I want the data in an order where the Buyers can easily update the Price and send the Workbook back to be imported.
This report is a simple report that can be filtered and exports a header row plus nine fields and two new columns from each record, the export report is in the download at the end of this post.
When the report is run in “Cronus UK” it produces an Excel Workbook with just two Sales Price lines.
Importing the Data
When importing the data into the Excel Buffer the thinking would be eleven columns and three rows would be thirty three entries, but if I import the Workbook I only have twenty four entries, this is because empty Text or Code cells do not import but Zero numeric values do, as I can see when I filter Row 3 and columns 3,6,7 and 10 are missing an entry.
I have see a few reports fall over as a simple loop has been used by the developer with no validation or checking for the “Column No.”, a blank “Currency Code” could cause an import to fail if there is no checking or validation code, another common Issue is code fields with leading zero’s, when Excel opens these are formatted as General and Excel strips the leading zero’s.
C/AL Code
On Pre-Report Trigger Code
- Prompt the User to select a Workbook, if none is selected exit the report.
- If there is more than one Worksheet then prompt the User for the Worksheet Name.
- Empty the Excel Buffer Table.
- Open the Workbook.
- Import the Worksheet cell values into the Excel Buffer table.
AnalyzeData (Function) Code
- If the TempExcelBuffer is empty then exit the report .
- Find the last TempExcelBuffer record, if it only has the Header Row one then exit the report.
- Open the Dialogue Window and set the Progress Bar to zero.
- Set the TotalRowNo to the last row number in the Excel Buffer.
- Set the FOR TO Loop from the second to last row number, the first row holds the column headers and is not imported.
- Update the Dialog Window Progress Bar.
- Only if the Excel Buffer record has a value in column 11 the “New Unit Price” then begin the Import for that row number.
- Clear the Sales Price table variable ready for the buffer data.
- Un-conditionally get the buffer record for the Sales Type field, this is mandatory and an error will be raised if the record is missing.
- Evaluate the Sales Type option with the “Cell Value as Text”,an error will be raised if the value is not one of the available options.
- Validate the Sales Type to run any Trigger Code.
- The Sales Code field, this is only mandatory if the Sales Type is not “All Customers”.
- Un-conditionally get the buffer record for the Sales Code field, an error will be raised if the record is missing.
- Validate the Sales Code to Run Any Trigger Code or raise an Error for bad data.
- Un-conditionally get the Buffer Record for the Item No. Field, this is mandatory and an error will be raised if the record is missing.
- Evaluate the Item No. with the “Cell Value as Text”.
- Validate the Item No. to run any Trigger Code or raise an error for bad data.
- If a buffer record exists for the Variant Code.
- Evaluate the Variant Code with the “Cell Value as Text”.
- Validate the Variant Code to run any Trigger Code or raise an error for bad data.
- If a buffer record exists for the Unit of Measure Code.
- Evaluate the Unit of Measure Code with the “Cell Value as Text”.
- Validate the Unit of Measure Code to run any Trigger Code or raise an error for bad data.
- If a buffer record exists for the Starting Date.
- Evaluate the Starting Date with the “Cell Value as Text”, an error will be raised if the data is bad.
- Validate the Starting Date to run any Trigger Code.
- If a buffer record exists for the Currency Code.
- Evaluate the Currency Code with the “Cell Value as Text”.
- Validate the Currency Code to run any Trigger Code or raise an error for bad data.
- If a buffer record exists and the Minimum Quantity (Cell Value as Text) <> 0
- Evaluate the Minimum Quantity with the “Cell Value as Text”, an error will be raised if the data is bad.
- Validate the Minimum Quantity to run any Trigger Code or raise an error for bad data.
- Un-conditionally get the Buffer Record for the Unit Price field.
- Evaluate the Unit Price with the “Cell Value as Text”,an error will be raised if the value is not numeric.
- Validate the Unit Price to run any Trigger Code.
If a record with the same primary key exists then update it else create a new record in the database.
Results
If I add values to the New Unit Price the record will be Inserted or Modified, the New Starting Date is an Optional Field to maintain a History or allow for future prices.
After Running the import with the Workbook I can now see three new records with the “New Unit Price” and “New Starting date” from the Workbook.
Variation
If I was using this report for a Client that wanted to review the Buyers Prices or confirm the changes, I would populate the Table: 7023: “Sales Price Worksheet” and run the Page:7023 “Sales Price Worksheet”, to enable a review of the “New Unit Price” before Implementing the Price Changes.
