When you move to OneUp from another accounting solution you may want to bring journal entries with you, especially if you change solutions during a fiscal year. This guide will give you tips and help you through this process.
Requirement - Match the Chart of Accounts
In order to import journal entries, the Chart of Account structure in OneUp must match that of your prior solution. This can be done by editing your chart of accounts. To conform OneUp’s Chart of Accounts, you may need to:
- Create additional accounts
- Create additional account families
- Change some account numbers
- Remove unnecessary accounts
Once your charts of account match, you can continue. You can also Import Chart of Accounts. If you have any difficulty conforming your chart of accounts, please reach out to our support experts.
Step 1: Retrieve journal entries from your prior solution
Each accounting solution has a different method of retrieving journal entries to be processed by OneUp. Consult the user guide of your solution in order to retrieve transactions in a tabular format, like CSV (comma-separated values).
Do not export ‘forecast’ or ‘draft’ journal entries
All transactions imported will always be considered realized, so do not retrieve forecast or draft entries from your prior solution. If you do not have this option, you may need to remove them in the 2nd step.
Each row in the table refer to one part of the accounting transaction
Make sure you export your entries in a format that has each part of the journal entry on separate rows.
When OneUp imports the entries, one journal entry will always be built from two or more lines in the table. In the example before, you can see that the first two rows of the table comprise the 1st entry, and the third and fourth rows comprise the 2nd entry.
|Transaction ID||Journal Code||Date||Document number||Account number||Entry label|
|1||MO||1/12/2017||IMP-001||11100||First journal entry - debit|
|1||MO||1/12/2017||IMP-001||10100||First journal entry - credit|
|2||MO||1/12/2017||IMP-002||11100||Second journal entry - debit|
|2||MO||1/12/2017||IMP-003||10100||Second journal entry - credit|
Required information in the export
- Date of the journal entry
- Journal code for the accounting entry the entry is recorded in
- One row per debit/credit line in each journal entry
- A Transaction ID for grouping the debit and credit lines
- Amounts formatted with or without negative sign (-) to denote whether the line is a debit or credit
- Amounts must be in base currency
- Account number of each side (debit/credit) of the entry
- Label describing the journal entry
Step 2: Edit the table headers to match the OneUp import format
Using Google Spreadsheet, Microsoft Excel, or your favorite spreadsheet program, open the file coming from your prior solution for editing.
The 1st row of the table must contain headers so OneUp can identify what it contains. Each header must match one of the values below.
ID number of the journal entry. This is used to specify which debit and credit lines comprise a journal entry during import. This number will not be stored in the transaction, as OneUp will generate new transaction IDs upon importing, based on any already-existing transaction IDs in the system.
Short code of the journal this entry is to be recorded in. If the journal does not already exist in OneUp, it will be created automatically.
Date of the journal entry. Must be the same for each debit or credit line comprising the same journal entry.
Document number (optional)
A reference number for the business object that this transaction is related to
A valid account number from the chart of accounts - cannot be a family account
Less than 64 characters
In base currency. Positive for credit, negative (-) for debit... number format must not have a currency symbol included
Pmt Reconc (option)
An optional column for noting which invoice a payment is associated with.
Step 3: Export the table to CSV file
OneUp accepts uploads in CSV (comma-separated-value) format. The importer will correctly determine the delimiter upon upload. (comma, semicolon, or Tab)
Step 4: Import the prepared CSV file
- Choose the date format that matches the file you will upload
- Choose the file from your file system
- Click the “Import” button
Troubleshooting error messages
The OneUp Journal Entry Importer does many checks to make sure you don’t import accounting entries poorly. If ANY errors are encountered, the import is CANCELED and you must correct the errors and try to upload again. Here are some of the error messages you can receive and common solutions to fixing them.
The selected file has to be a CSV file
The filename does not end in “.csv” and the importer is ignoring it.
- Solution: check that you are actually importing a CSV file. If it IS a CSV file and is missing the “.csv” at the end, add that to the file name.
Wrong Header or Headers Missing
The headers listed in the error were not found in the imported file.
- Solution: Check that you set up the file properly.
Journal not found
The data entered on line xxx for the column ‘Journal Code’ doesn’t exist in the OneUp account.
- Solution: Check the journals in the OneUp account and create any that are missing. See your journals in https://app.oneup.com/#journal/list .
Account not found
The account in the given line and column was not found in the Chart of Accounts.
- Solution: Check to make sure that your chart of accounts matches the prior solution. Create accounts, or change account numbers as necessary until you do not encounter this error. See your chart of accounts at https://app.oneup.com/#accounting/search .
Account number not valid
The account number in the given line and column could not be read.
- Solution: Check that the account number to be sure it doesn’t contain any strange characters.
Account number not initialized
Account number is required for each line in the imported file and the given line and column did not contain an account number.
- Solution: check your file to be sure every line has a value in the account column.
Number format error
The value in the ‘Amount’ column on line xxx was not a recognized number.
- Solution: Do not include currency symbols. Be sure the amount is formatted either like ‘1,234.56’ (US Style) or ‘1 234,56’. (French style).
Date format error
The date on line xxx does not match the setting selected in the importing tool.
- Solution: Be sure you selected the correct date setting in the importing tool. If it seems correct, check the CSV to make sure the values are correct. Some spreadsheet programs manipulate date settings when exporting to CSV. Check the “Number” formatting of this column in your spreadsheet app.
Journals are not the same within the same transaction
For the given transaction ID in the source file, a mix of accounting journals was used.
- Solution: every part of an accounting transaction must occur within the same journal. Check this transaction to be sure the same journal is specified on each part.
Dates are not the same within the same transaction
For the given line and column in the source file, a mix of dates was used.
- Solution: every part of an accounting transaction must occur on the same date. Check ID xxx to be sure the same date is specified on each part. Make sure all the dates match the format chosen in the importing tool.
Entries for Accounting Transaction are not balanced.
The sum of debits and credits in the journal entry are not balanced.
- Solution: check the values in the ‘Amount’ column for all parts of this transaction. Adding up all the values should end up in ‘0.00’. Remember that Credits must be positive ‘1,000.00’ and Debits must be negative ‘-1,000.00’
An error occurred during the import: Column 0
You have uploaded a file that OneUp does not recognize. Do not try to upload an XLS (Excel format) file to OneUp.
- You must save the file as .CSV (comma-separated values) instead. If the file you uploaded is a CSV there is probably an error in the file, such as missing characters, or the file is truncated.
ACCESSING THE IMPORT TOOL
There is no link inside of your OneUp account to access the importer. To do the import, follow this link. Proceed with caution!