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
https://docs.microsoft.com/en-us/dynamics-nav-app/across-about-the-data-exchange-framework
https://docs.microsoft.com/en-us/dynamics-nav-app/across-how-to-set-up-data-exchange-definitions
“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
Before Import
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
-
Name IMPORT_PAYROLL
-
Column Count Number of column existing in Import File
DEFINE COLUMNS
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”
FIELDS MAPPING
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!
Hello, excellent post, I am doing all the steps but when I try to import I always get this error: “The Account Number column is assigned in the PAYROLL_IMPORT format to the BigInteger field, which is not supported.”
Any ideas?
Hello,
I have the same Issue.
Did you find a solution for this ?
Thank you!
Best regards,
Ivan
Hi, Roberto. Thank you so much for this tutorial. That help me a lot. I just have one question that is scaring me for a month already: how can I map a dimension value. I tried so many ways but I’m not getting done. Can you help me, please?
Has anyone got this working for non-IIF files?
Hi John
I have got this working in a UK tenant.
I had to personalise the General Ledger setup to show the payroll import field then I had to uninstall the Quickbooks payroll import extension, then in journal, go back into personalise and show the Import payroll action (hidden when extension removed).
Now it is letting me use that to import CSV file.
🙂