General Journals
Business Value
Allows you to import General Journals from an Excel file in the format you receive the file. Use Excel Importer to import expenses, payroll entries, adjustments, internal cost reallocations or any other entries that get posted to the general ledger. The general journal supports entries that post to your bank ledger, customer ledger, vendor ledger, fixed asset ledger, employee ledger and inter-company entries. You can also use Excel Importer to import opening entries for new companies.
Possible Scenarios
Here are some possible scenarios where you might use this feature to import journals:
- Expenses from a different system like Fraedom or Gorilla Expense.
- Petty cash expenses that you have recorded in Excel.
- Payroll entries that you have exported from your payroll system.
- Auditor's adjustments or IFRS adjustments.
- Internal cost reallocations - Look at our example below, where we reallocate expenses from our Admin department to Sales and Production using formulae that we added to our Excel spreadsheet.
- Consolidation entries for subsidiaries not using Business Central.
- Create opening balances for new companies in Business Central. This feature can import general ledger, customer, vendor, bank, and fixed asset journals. Our example below shows how to import opening customer entries.
- Import statistical entries using external data
Feature Details
The general journal import can be accessed from inside the journal using the Import action.
Supported Journals
You can find the Import action in the following journals:
| Journal | Description |
|---|---|
| General Journal | Used for month-end adjustments, payroll expenses, bank journals, etc. |
| Sales Journal | Process journals related to customers. |
| Purchase Journal | Process journals related to vendors. |
| Cash Receipt Journal | Process receipts from customers. |
| Payment Journal | Process payments to vendors. |
| Fixed Asset G/L Journal | Process journals related to fixed assets. You use this for FA Posting Types that are integrated to the general ledger. |
| Fixed Asset Journal | Process journals related to fixed assets. You use this for FA Posting Types that are **not **integrated to the general ledger. |
| Intercompany General Journal | Process entries that relate to intercompany parties. |
| Recurring General Journal | Process journals that recur regularly or accruals that need to be raised and automatically reversed in the next period. |
| Project G/L Journal | Process general ledger journals related to projects |
| Statistical Account Journal |
In Business Central, recurring journals do not get deleted after posting, so you don't always need to import them - it depends more on the setup of the recurring journal lines. If you have lines where the amounts are cleared out and need to get recaptured in the following period, consider importing the entries using Excel Importer.
Settings
One Document No. per Batch
This setting lets you specify that you want Business Central to use the same document number per batch. This setting overrides the standard Business Central behaviour, where the number is incremented for each balanced transaction.
| Value | Description |
|---|---|
| No | The standard Business Central behaviour will apply when the document number is incremented for each balanced transaction. |
| Yes | The app will use the first document number assigned from the number series specified on the batch for all imported lines. |
The app will ignore this setting if you import a document number from the file.
Exclude lines with zero amounts
The purpose of this setting is to allow you to exclude lines with zero amounts.
| Value | Description |
|---|---|
| No | Imports all lines from the file, including lines that have a zero amount |
| Yes | Does not import lines that have a zero amount |
Dimension Code linked to Amount columns
This setting allows you to specify a range of columns that contain an amount for a different dimension. This feature is useful when performing cost reallocations based on formulae that you have defined in a spreadsheet. Here is an example of the type of file you can import.
The setting value you specify will be the dimension (e.g. DEPARTMENT). In our example, we have a department dimension, and we are allocating expenses from department "ADM" to departments "SALES" and "PROD". Refer to the example below to see how this file gets configured.
The app disables concatenation for the Amount field when you enable this feature on a template.
Check for Empty Batch
This setting specifies that the batch you're importing into must be empty before the file can be imported. It is beneficial when using the API to import and post a journal, as it helps prevent accidentally posting lines that were not part of the import.
The app does not raise an error if the journal contains empty lines—it deletes them instead. It uses Business Central's standard logic to determine whether a line is empty.
Post after Import
This setting lets you specify whether the journal should be posted automatically after importing.
| Value | Description |
|---|---|
| No | The journal will not be posted automatically. This default option allows you to manually review and post the journal. |
| Yes – Save Lines on Error | The journal will be posted automatically after import. If an error occurs during posting, the entire posting is rolled back, but the imported lines remain in the journal. You can then fix the errors and post manually. |
| Yes – Remove Lines on Error | The journal will be posted automatically after import. If an error occurs during posting, the entire import is cancelled, and no lines are saved in the journal. |
- Importing large journals (e.g., thousands of lines) may cause record locking for other users. Selecting Yes – Save Lines on Error helps reduce this risk. Smaller imports are less likely to cause issues.
- If you post the journal automatically, the app will delete any empty lines before importing. This helps prevent posting errors. The app uses Business Central's standard logic to determine whether a line is empty.
- Use the Check for Empty Batch setting to ensure that only data from the import file is posted.
- Select Yes – Remove Lines on Error when integrating via the API to ensure a clean import and avoid manually opening and posting the journal.
Approval Workflows
If there is an active workflow, the app will create the Restricted Records when you import journal lines by running the standard Business Central logic. Therefore, you will be able to post the journal once it is approved when this is applicable.
Some of the examples below show how you can import opening transactions. These examples might also include mapping to set the System-Created Entry to Yes. Please remember that this is only intended for an initial take-on, so you don't have to change the direct posting setting for accounts. If you specify that the journal is system-created, Business Central will not create restricted records as expected.
Custom Values
Custom values can be used to add custom features to imports. The app includes a custom action for General Journals to set the balancing account of the journal to the control account setup for the customer, vendor, bank account, employee or fixed asset. You can read more about this here.
Statistical Account Journals
You can import Statistical Account Journals from the Statistical Account Journal page.
The template also includes a Custom Value that allows you to import more than one statistical value from the same line.
Examples
Example 1: Import for opening customer entries
We import opening entries for a new Business Central company in this scenario. The company previously used a different accounting system. The entries got exported from the previous accounting system.
Excel Mapping Template
Excel File
Imported Journal
The Account/Dimension Mapping is beneficial if the accounts and dimensions in the previous accounting system have different codes from those in Business Central. Our employee expense example below uses the Account/Dimension Mapping in a slightly different context but illustrates the possibilities.
Example 2: Import for Dimensioned Amount Columns
Excel Mapping Template
- The field First Data Row has been set to 3 because, in the Excel worksheet, that is the first line we want to import
- In the settings fact box, we have specified DEPARTMENT for "Dimension Code linked to Amount columns."
- The Amount field is mapped three times - an amount column representing each department
- We are required to add a mapping line for Dimension DEPARTMENT. You will receive an error when importing if you do not specify this. You will also notice that we specified a constant value on this line. You must specify a valid dimension value for the constant value, but it does not matter which one you enter here.
Excel File
Imported Journal
We created three journal lines for each line in the Excel worksheet - one for each dimension.
You might have noticed that the journal has document numbers, but we specified no document numbers in the Excel worksheet. Excel Importer uses the number series selected for the batch to generate document numbers.
Example 3: Employee Expense Import using Account/Dimension Mapping
Here we import employee expense claims. The expense claims got exported from a different system, and the employee codes, dimensions, and expense accounts do not match what we set up in Business Central. You will see how we use the Account/Dimension Mapping in this relatively complex scenario to handle this.
- The columns that reference Account/Dimension Mapping are highlighted with the red box.
- We are only getting values from the file for B, E and F - you will notice that the Mapping Type is Parent for all of these.
- The blue boxes highlight where the Mapping Type is Child. These are associated with a Parent, which is the Account/Dimension Mapping that is used to get the value for that field. This feature of the Account/Dimension Mapping allows you to copy values to multiple fields even with only one mapped column.
Account/Dimension Mapping
Excel File
Imported Journal
Example 4: Defaulting the control account for opening entries
This example is a variation of the previous customer entries example. The difference is that we have the app assign the control account. This feature is useful when importing opening entries for many customers and you have set up several control accounts.
You can use a similar approach to import opening entries for vendors.
Excel Mapping Template
Regarding the above image:
- Here, we selected the custom value CONTROLACCOUNT. You will notice no mapping to the Excel file and no Constant Value.
- We added a default for the System-Created Entry field to allow the direct use of the control account, which you usually configure to prevent direct posting.
This template is included with the other sample template when you use the setup guide.
Excel File
Imported Journal
Example 5: Using Account/Dimension Mapping with Global and Shortcut Dimensions
In this example, we want to import a payroll file containing account numbers and dimensions in a single string and use the account dimension mapping to transform them.
Excel File
The file below contains a cost centre column, and we need to use this column to map to Business Central G/L Accounts and Dimensions.
Account/Dimension Mapping
In the image below, the Department and Customer Group are global dimensions, and the Business Group is a shortcut dimension.
The Account/Dimension Mapping also supports all dimensions - even those not specified as either a Global or Shortcut dimension.
Excel Mapping Template
In the image below, you will notice one:
- The same Mapping Template Code is used to map the Account Type and No. fields as well as the Dimensions.
- The Excel Column ID is B for all these fields as it is that column that contains the value I want to map.
- The Mapping Field Caption is used to specify which value to use for the Account/Dimension Mapping.
- There is only one template line for Dimensions, and we have selected the Map to Dimension Set field. This indicates that we want to use all the dimensions specified on the Account/Dimension Mapping.
Imported Journal
You will notice that the app has mapped the accounts and dimensions using account dimension mapping.
