Import Payrolls in Business Central
Business Central DEF (Data Exchange Framework)
Hi, you can use Business Central DEF to import Payrolls on COGE registration batches easily.
Data Exchange Framework is present in several versions of Dynamic NAV\Business Central, I point it out because it could still be useful, especially for importing Payrolls (some have asked me this); today with Microsoft Flow I can achieve the same result with fewer steps, but even here in Business Central has its dignity and in this post I want to talk about this great feature.
You have EDI in Business Central! …use EDI!
You will just need to:
- A New Configured Data Exchange
- An Input (CS, XML)
DATA EXCHANGE ARCHITECTURE
“HOW-TO” – IMPORT PAYROLLS
Create a new “Data Exchange Definition” to manage payroll import data flow.
Configure the Payroll Import Card
You have to define:
– File type Variable Text
- Type Payroll Import
– File Encoding WINDOWS
- Column Separator Tab
To import data, a codeunit for reading / writing or an XMLport for reading / writing is sufficient.
In this case I choose the XMLport and select in the field “Reading / Writing XMLport” an XMLport already ready inside the system “1220 – Data Exch. Import – CSV”
CREATE A NEW LINE DEFINITION
Insert a new line in “Lines Definition”
- Line Type Header
- Code IMPORT_PAYROLL
Column Count Number of column existing in Import File
Definire le colonne che compongono il file CSV in “Definizione colonne”
- Column No. Number of column (positional) In CSV\XML File
Name Column Name for Input Field
Data Type Data type for input field
Data Format For Date fields
Data Format Culture Example for Italy “it-IT”
Click on “Fields Mapping” You can associate columns in the data file with the Business Central Fields
The only mapping to be done is the one related to the “Field Mapping” in which it is indicated, respecting the order of the columns in the CSV file, the fields that must be valued in table 81 and that correspond to the CSV file.
Example of utilization:
- Table ID = 81 (General Journal Line) the data will be loaded in this table, you can assign also table in each line and assign table and target fields.
Mapping Codeunit (not necessary, only field mapping)
- In this case is not necessary to define a mapping codeunit.
HOW-TO IMPORT A PAYROLL FILE – STEP BY STEP
Activate Payroll Import in “General Ledger Setup”
Open “General Ledger Setup” and assign to “Payroll Trans. Import Format” field the Data Exchange Code defined before for Payrolls Import.
IMPORT SAMPLE FILE (PAYROLLS CVS FILE)
Sample of Payroll File:
Open “COGE” and Select “Import Payroll File”
N.B. if Import Payroll File Import Action is hidden you can personalize the bar (like in General Ledger Setup Page)
You can select your Payroll File example “PAYROLL.TXT”, a TXT file with <TAB> separator
After this, lines will be imported in the Batch
The lines are ready to post! …and now you can Post!