Import Data TemplatesImports
Import a general journal into Business Central from Excel or CSV
Payroll runs, allocations and adjustments, posted from the file you receive instead of typed into the journal.
Every month the same files arrive: the payroll summary from the payroll provider, allocation and accrual entries prepared in Excel, corrections from the accountant, entries exported by other systems, and more.
With Import Data Templates, the file becomes journal lines in the batch you choose when the import starts. The template maps the columns once: dates, accounts, amounts, dimensions. Values the file does not carry, such as the account type or the document number, are filled by default values; values that change every run, such as the posting date, are asked when the import starts.
What the file can look like
One row per journal line, in whatever layout the sender produces: an Excel sheet with named columns, a CSV export, or the fixed-width text file some payroll systems still write, where each value lives at a fixed position of the line. Amounts can arrive as one signed column or as separate debit and credit columns, in the sender's decimal format; a transformation rule inverts signs or multiplies where needed, and a dimension column, such as a cost center, is written to the dimension of the line.
The sample below is the payroll example shipped with the app: G/L accounts, amounts and a cost center per line, with the account type and the document number set by the template.
Sample file: EX-PAYROLL.xlsx
- Sample file: EX-PAYROLL.xlsx
How the import works
-
Load the template
The ready-made payroll template,
EX-PAYROLL, is created from the Template Examples page;EX-FIXED-PAYROLLis its fixed-width twin. For your own file, fill in the template with the structure of the file, use Read Structure from File to build it from the column headers, or let Copilot propose it from a sample file and review it before keeping it.
The EX-PAYROLL template: posting date, account and amount from the file, account type and document number as default values. -
Start the import from the journal
Open the General Journal, pick the batch, choose Import Template, then the template and the file, and answer the import parameters, for example the posting date. The check step validates every value, accounts and dimensions included, and lists the errors with the line of the file that caused them before any line is written.
The flow on the General Journal: Import Template, the template, the file, the imported lines. -
Review and post
The lines are in the batch, exactly as if they had been typed: balanced against the file, ready to be reviewed and posted, or posted directly by the template when it is configured to. The import history records the file, the user, the date and the number of lines created.
The batch after the import: the payroll lines, checked by the journal check.
Beyond the basic file
The details that make journal files awkward are handled in the template:
- The file has no account type or document number: default values fill them on every line.
- The posting date is not in the file, or must be chosen at each run: an import parameter asks for it when the import starts.
- The sender's account numbers differ from the chart of accounts: a mapping rule translates them during the import.
- Amounts come sign-inverted, in cents, or split over debit and credit columns: transformation rules invert, multiply and combine them.
- A cost center or department column becomes a dimension on the line, global or additional.
- The same layout arrives from several companies or entities: the same template is exported and imported across companies and environments.
- The file lands in a SharePoint, Azure Blob Storage or Azure File Share folder: the automatic imports pick it up without a user, or the API receives it from Power Automate.
The related examples cover the neighbouring journals: EX-VENDOR-PAYMENTS (payment journal, applied to invoices), EX-CUST-RECEIPTS (cash receipt journal) and EX-BANK-STATEMENT (payment reconciliation), all on the examples page.
Questions
Can the lines be posted automatically after the import?
Yes. A line table action posts the journal lines at the end of the import; without it the lines stay in the batch for review.
What about a payroll file with no column headers?
Fixed-width files without headers are supported: the template states the position and length of each value, as in the EX-FIXED-PAYROLL example.
Can one template serve several journal batches?
Yes. The batch is chosen when the import starts, so the same template works for any batch of the journal, and the batch can also be asked as an import parameter.
Import Data Templates is available on Microsoft AppSource with a one-month free trial. Licenses are assigned per user in the Microsoft 365 admin center.