Import and Export using Excel Buffers

Import and Export using Excel Buffers

olisterr No Comments

Introduction:

Using XMLPort to do the Import and Export using CSV or Tab Delimited .xls can be inconvenient at times. For example when the field value contains a COMMA (First Name, Last Name) or if there are QUOTES(“”) added by Excel when saving the data. Thus the best way I found was using Excel Buffer.

Pre-requisite:

  1. Microsoft Dynamics NAV 2013 R2 and above
  2. Microsoft Office 2010 and above

Theory:

Excel Buffer Table(370) is a table that stores the data from each cell as Row,Column, TextValue, Formula and other fields in a single record. So if Excel Sheet contains 5×5 grid i.e 5 rows and 5columns then the whole data is stored in the form 10 records in Excel Buffer. The Excel sheets can have various types of visual formatting such as Bold, Italic, Underline, Font Color, Background Colour etc.

Demonstration:

1. Exporting using Excel Buffer:

Export Excel Buffer Sample Code

                 Export Excel Buffer Sample Code

i. Var ExcelBufferTable – Type Record -ID 370

ii. ExcelBufferTable.AddColumn(<Value:Text>,<IsFormula:Boolean>,<CommentText:Text>,<IsBold:Boolean>,<IsItalics:Boolean>,<IsUnderlined:Boolean>,<NumFormat:Text>,<CellType:Option>) – adds a colum of required type.

iii. ExcelBufferTable.NewRow – adds a new row to the sheet.

iv. ExcelBufferTable.CreateBookAndOpenExcel(<FileName:Text>,<SheetName:Text>,<ReportHeader:Text>,<CompanyName:Text>,<User:Text>) – creates the Excel Sheet and the Excel Program.

 2. Importing using Excel Buffer:

 

Import1 Excel Buffer

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

i. FileMgmt.UploadFile(<WindowTitle:Text>,<FileExtension:Text>) – Uploaded the file using File Management codeunit function

ii. ExcelBufferTable.OpenBook(<Filename:Text>,<SheetName:Text>) – Open the Excel Sheet passed as Excel File(.xlsx) and read the sheet with name ‘Gift Cards’.

iii. Read the sheet using ExcelBuffer

iv. Count total Rows and Columns in the Excel sheet

v. GetValueAtCell(<RowNo:Integer>,<ColumnNo:Integer>) – Read each cell using function GetValueAtCell

Conclusion:

Thus, this is how I’ve implemented Excel Buffer for Importing and Exporting Excel Sheets. The import export was performed based on the Action Buttons as required and no special objects were created. Thanks for reading and stay in touch 🙂

Setting up a custom Positive Pay Export for Key Bank in NAV 2017

olisterr No Comments

Introduction:

What is Positive Pay Export and Why its used??
Positive Pay Export is a file that is given to the bank to verify the Cheques that have been printed.
Whenever the company wants to issue any payment to Customer or a Vendor, it is done with the help of Payment Journals. After creation of Payment Journal lines, the check is printed through a Report. 

After the creation of the Check in the Bank Account there is an action called as PositivePayExport. This file is a text file and contains the information related to the Cheques printed and it is given to the bank.
Every bank has their format which is used to automatically verify the Cheques that are being issued.

Demonstration:

1. Creating a Bank Account: To issue the cheque setting up Bank Account is mandatory

  1. Setting up the Data Exchange Definitions: Data Exchange Definition is used to setup the format for Positive Pay Export.
    i. Format for Export:

    ii. Create a new Data Exchange Definition:

    iii. Creating Column Definitions: Column Definition is the sequence in which the data is to be generated as per the format.

    iv. Creating a Line Definitions:
    Line Definitions are used to map the System Fields to the fields in the column defintions. In the Line Definition there are transformation rules that are used to modify the data that is exported into the format.

    v. Create a Line Definition and Select Field Mapping:

    vi. Transformation Rules:There are some predefined Transformation Rules as well as new transformation Rules can be created. I’ve created the following transformation rules.

    vii. Creating Transformation Rules: I’ve shown the details of REPLACE transformation rule
  2.  Connecting the Data Exchange Definition with the Bank Account: To link the Positive Pay Export and the Bank Account, goto Bank Accounts and select the Positive Pay Export Code.
  3. Creating Payment Journal Entries, Printing Cheques and Exporting the Positive Pay file.i. Creating Payment Journal Entry

    ii. Printing the Check Report
    iii. Positive Pay Export in Bank Accounts KEY4679

Conclusion:

After Exporting Positive Pay Export the values generated in TXT file as follows

000003296812746790000000012201805150000010200
000003296812746790000000013201805150000010512

This text file is given to the Key Bank to verify check payment.

Building a bottle light for the balcony part -1

olisterr One comment

Densing the climber framework for the garden

olisterr No Comments

Cleaning the window hanging money plant

olisterr One comment

Linux From Scratch – 12. Installation of GCC Compiler for the LFS final build

olisterr 9 comments

Linux From Scratch – 11. Installation of the packages for the LFS final build.

olisterr No Comments

Project Liter of Light

olisterr No Comments

Linux From Scratch – 6. Compilation of GCC-6.2.0 Pass 2 Part – II ,Tcl Core, Expect and Check

olisterr 12 comments

Linux From Scratch – 7. Installation of DejaGNU, Expect, Check, Bash, Ncurses and CoreUtils

olisterr No Comments

www.000webhost.com