Description

Add journal entries in bulk through an xlsx excel file, with up to 20,000 rows per upload. Often times there may be an export from a different source (e.g. a sub-ledger, or an expense platform) that can be added to a second tab and converted into the upload template to efficiently and formulaically create Journal Entries.

Download Bulk Journal Upload xlsx Excel Template Here

Important Considerations:

The Journal.number is solely specific to this excel document, and is used as a means to group line items together (e.g. debit/credit lines) to make a full Journal
- The number for each Journal will be auto assigned by system on upload
Once you have added in custom dimensions, you can use them in bulk uploads by adding a column to the bulk journal, invoice, and bill upload templates with the name of the custom dimension followed by ".id" (such as having Property.id as a column if you have Property as a Custom Dimension)
All Document level fields needs to be the same per Journal Entry (see the table of fields below for details)
Within the template, the fields highlighted in yellow are required.
All Journal Entries from an Upload will be given a System Job ID value, which can be used to filter in the Journals table to find and bulk edit/delete uploaded entries.
Max file size is 5MB, about 20,000 rows
Similarly, one journal entry (connected through the journal.number field) can have up to 1,000 rows to make up a balanced journal entry

File Upload Fields

Required Fields

These fields are always required on upload

Journal.number - Unique ID specific to this excel document that groups line items together to form one journal entry (this will not be the Journal ID within the system, that ID is auto assigned by the system).
transactionDate - Transaction date of entry (this cannot be in the future, as this is the date used for foreign currency exchange rates). Format for this date should be YYYY-MM-DD
postedDate - Posted date of entry. Format for this date should be YYYY-MM-DD (this is often considered the accounting date, as it drives which reports a journal line will fall into)
Journal.sourceLedger - Source Ledger of entry (this field is case sensitive, must be one of "Financial" "AR" "AP" "Inventory"), where we recommend using "Financial" for most use cases
Journal.reference - 255 character text field for this entry
Location.id - Location ID value for this line item. You can set and review Location IDs under the Financial>Locations table
LedgerAccount.number - Ledger Account number for this line item (Omit any leading zeros)
debit - debit amount of entry
credit - credit amount of entry

Optional Fields

These fields are optional, but may become required depending on the type of entry you create. For example, if you want to create an InterCompany Journal Entry, then the IC fields will become required

Currency - the currency of the journal. This is required if no Currency is selected for the full file on the file upload page.
fxrate - the foreign currency exchange rate from posted currency to the location of the journal's reporting currency. This is required if the Currency set is a Custom Currency.
Description - 255 character line item text field.
eliminate - This can be set to either "true" or ""false", with the lines marked as true creating elimination lines for the line item of the journal (note that certain lines of a single journal can eliminate while others do not). If the System Setting to Allow for Eliminations is Enabled, this will default to creating elimination lines for any journal lines that have intercompany location and account information included.
ICLocation.id - Intercompany Location ID value for this line item. You can set and review Location IDs under the Financial>Locations table.
ICAccount.number - IC Ledger Account number for this line item. Omit any leading zeros. Required if ICLocation.id is set. If entering a debit value line item, the IC entry in the IC location will be the credit side. If entering a credit value line item, the IC entry in the IC location will be the debit side.
entryType - Entry type for this Journal Entry (must be one of "Standard" or "Reversing", if reversing, reverseDate is required, and note this field is case sensitive).
reverseDate - Date Journal entry should be reversed (must be on or after postedDate).
Journal.notes - Long form text for this entry.
Customer.id - Customer ID for this line item.
Vendor.id- Vendor ID for this line item.
CostCenter.id - Cost Center ID value for this line item.
Product.id - Product ID value for this line item.
Job.number - Job number for this line item.

Details on `fxrate` field

The fxrate field on the upload works the same as when creating a journal entry in the application UI. See some details:

fxrate is required using a custom currency.
fxrate must be the same for all lines of a journal.number
fxrate can't be provided for inter-company journals (if ICLocation.id is set)
fxrate should be the posted to reporting currency rate for the transaction date (if an EUR Journal is posted to a USD Location, the fxrate for that row will be the EUR to USD rate for the transaction date)

File Upload Process

Select the saved upload file by clicking Choose File.
Optionally select the Currency for ALL journal entries in the file, OR provide a currency per journal entry within the 'currency' field of the file itself.
The file will either upload with a 'completed' status, or return errors in a 'failed' status (Depending on the number of entries, this could take several minutes to upload).

Troubleshooting errors:

If there is one or more errors. A table of errors will appear. The first column will either read "CSV Row" or "Journal Number"

CSV Row - signifies the row in the uploaded file causing the error(s) in the second column
Journal Number - signifies the Journal.number value causing the error(s). Usually a result of debit/credits not balancing

After resolving the errors, re-upload the file to complete the process.