Journal Entry Import
The Journal Entry Import Template is a text file much like the ones used to import records (e.g. Clients, Vendors, Products) into other AV modules. However, this template has a different format that must be strictly adhered to.
There are two sections to this import template:
- GL Summary
- Sub-Account Detail
In the sample below, rows 6-10 make up the GL Summary section and rows 12-43 make up the Sub-Account Detail section.
Note: If your import does not contain Sub-Account data (Inventory, AR, AP, etc), you must still include the Sub-Account header row (row 12 in screen shot below) and the EOF (end of file) tag.
Before you begin:
1. Prepare the Import Template.
1.1. GL Summary section.
In the GL Summary section, you will import the summary balance for each of your General Ledger accounts.
- Cell A1 must contain the special code, AVExtDef20050421. This tells AcctVantage what data is being imported.
- Rows 3 & 4 should be formatted exactly as they appear in the sample. The date in Cell A3 is the Post To Date for this transaction. This is the date that the imported journal entries will be posted as of.
- Row 6 must be formatted exactly as seen in the sample. AcctVantage looks for these headers to validate the data that follows.
- Row 7 will begin your actual data import.
- Enter a row for each GL Account.
- The GL Accounts must be in XXXXX-XXX-XXX format.
- Enter a positive number in the DEBIT or CREDIT column as appropriate. Enter a 0 in the other column. Be sure to only enter positive DEBIT or CREDIT values. Never use negative numbers.
- Your DEBIT and CREDIT entries for the GL Summary section must balance. If they do not balance, the import will be rejected.
- Do not make an entry for your Current Year Earnings account(s). If you are importing Journal Entries to Income and Expense GL Accounts, the Current Earnings will be calculated for you.
Accounts that have related Sub-Accounts require special attention. Examples of these accounts include:
- Accounts Receivable: GL Accounts marked with the Setup Type of A - AR Aging (Row 7).
- Inventory: GL Accounts marked with the Setup Type of A - Inventory (Row 8).
- Accounts Payable: GL Accounts marked with the Setup Type of L - AP Aging (Row 9).
- Client Deposits: GL Accounts marked with the Setup Type of L - Client Deposits (Row 10).
- The balances in these 4 GL Accounts are made up of Sub-Account balances. For example, the balance in the Accounts Receivable account is made up of each Client’s Sub-Account balance.
- The GL Summary entry for each of these accounts must be justified with entries in the Sub-Account Detail section. The DEBITS and CREDITS on these detail lines must equal the amount for this account in the summary section.
- If there is more than one entry to the same GL Account, enter the total Sub-Account DEBITS on one row and the total CREDITS on another row. Do not enter the net amount on a single line.
1.2. Sub-Account Detail section.
The journal entries entered in the GL Summary section for…
- Inventory (no Kits): (i.e. Product quantities and values)
- Accounts Receivable: (i.e. Client Sub-Accounts)
- Accounts Payable: (i.e. Vendor Sub-Accounts)
- Client Deposits: (i.e. Client Sub-Accounts)
…must be justified with entries in the Sub-Account Detail section. The detail for a particular account will have to equal the amounts for DEBITS and CREDITS in the summary section for this same account.
Each row of this section is used to import one of the following:
- An open Accounts Receivable/Accounts Payable document
- A Lot of available Inventory
- A Client pre-payment (i.e. deposit)
The following column headers must be in place exactly as they appear in the sample above for the import to function correctly. It does not matter what row number the Sub-Account section begins on.
- GL Account Code:
- Enter the corresponding master account code for the import item. This will be the AR, AP, Inventory or Client Deposits GL Account Code.
- For the AP and AR detail, be sure that the GL Account you are using here for a particular Vendor or Client matches the account number that show up on the Client or Vendor record on the Account Information tab.
- These accounts are of Setup Type L - AP Aging and A - AR Aging.
- The total DEBIT and CREDIT entries for each GL Account in this section must equal the entry in the GL Summary section for the same account. The GL Summary section will have separate rows for DEBIT and CREDIT sums for each account.
- This column can be used to identify the Sub-Account to which the import item belongs. Enter the Client Name, Vendor Name or Product Name. This must be an exact match to the record in AcctVantage.
- If you have multiple Client or Vendor records with the same Name, then you must also indicate a Client/Vendor Number in the next column or the AV ID in the 4th column. For example, you have 3 different Clients named “Smith”, you would need to provide a second identifier to ensure that the import item is matched with the correct Client.
- This column can be left blank if the Client/Vendor Number or AV ID columns are used.
- Client/Vendor Number:
- This column can be used to identify the Sub-Account to which the import item belongs. Enter the Client Number, Vendor Code or Part Number. This must be an exact match to the record in AcctVantage.
- Since this field is typically unique, it is the most commonly used identifier when preparing imports.
- This column can be left blank if the Client/Vendor/Part or AV ID columns are used.
- AV ID:
- This column can be used to identify the Sub-Account to which the import item belongs. Enter the Client ID, Vendor ID or Product ID found in AcctVantage.
- Since this number is assigned by AcctVantage during import of the corresponding record (rather than coming from your current system) it is the least commonly used identifier.
- This column can be left blank if the Client/Vendor Number or Client/Vendor/Part columns are used.
- Invoice/Lot Number:
- This column is used to identify the import item.
- Enter your Invoice Number from your current system to reference AR or AP items.
- Enter a Lot number for Inventory items.
- This column is required for entry. If you do not currently use Lot numbers for inventory, enter “opening inventory” or a similar reference.
- This column is used to identify the import item.
- Doc Date:
- This column represents the Invoice Date for AR/AP import items. It is not required for Inventory import items.
- The date in the GL Summary section refers to the Post To Date. The Doc Date entered in the Sub-Account section assures proper AR/AP aging.
- Due Date:
- This column represents the invoice due date for AR/AP import items. It is not required for Inventory import items.
- The date in the GL Summary section refers to the Post To Date. The Due Date entered in the Sub-Account section assures proper AR/AP aging.
- Enter the DEBIT value of the import item. AR Invoices and Inventory items are generally DEBIT transactions. Do not enter a negative value. A $0 value is acceptable for import of closed items.
- If the import item has a CREDIT value, enter 0 for the DEBIT.
- Enter the CREDIT value of the import item. AP Invoices, Client Deposits and AR CREDIT Memos are generally CREDIT items. Do not enter a negative value. A value of $0 is acceptable for import of closed items. You cannot enter a CREDIT value for Inventory.
- If the import item has a DEBIT value, enter a 0 for the CREDIT.
- Product Qty:
- Enter the Quantity of Inventory units for this import item.
- You can import multiple Lots of inventory on separate rows if needed. You cannot import negative inventory quantities. Leave this column blank for AR/AP/Deposit items. Products that are a Kit type product would not be included in this detail as you would not have an open quantity of kits (since they are a not an Inventory item and are built only at the time of the sale).
- Enter the Warehouse name for the imported inventory. This must match a warehouse name previously setup in the Administration ➤ Warehouses screen.
- If Warehouses are not being utilized then use the default Warehouse and enter Company Wide in this field. This is a required entry for Inventory imports.
- This column represents the bin location or specific location within your warehouse in which the units are stored.
- This is a required entry only if you are specifying a warehouse other than company wide. If specified, it must match a location record previously set up in the Administration ➤ Warehouse record.
- This column can be used to enter a short note regarding each import item. It is not required for entry.
- An end-of-file tag, EOF, must appear in the first cell immediately after your last data entry row. This indicates to AcctVantage that no further data is expected.
1.3. Save the Import Template.
Be sure to save your GL Import template as a tab-delimited text file. It is highly recommended that you open your import file using a text editor, rather than a spreadsheet program, to ensure valid formatting prior to import.
- If using a spreadsheet program, it is common to lose the format of date fields when saving as text.
- It is also common to have Client/Vendor names encapsulated in quotation marks when saving as text (this is due to commas). Either of these conditions would cause the import to fail.
You can avoid many problems by formatting the entire sheet as text before entering any data.
2. Open the Imports & External Transactions window.
3. Import Journal Entry
3.1. Select the Import Template.
3.2. Processing Transactions.
Once the import starts, it may take from a few minutes to an hour or more to process, depending on the number of transactions involved.
3.3. The Import is complete.
If there were no errors, the Imports & External Transactions screen will indicate RTP (Ready To Post) when processing has completed. If errors were present, you can open the Imports & External Transactions record for a detailed explanation of the error along with the line on which the error occurred. (See below for some common causes of errors during the import process.)
4. Ready to Post the Imported Data.
If your import template is marked RTP, then you are ready to post the imported data to your General Ledger!
5. Posting the Imported Data.
Common mistakes that will create errors during the import process.
- Debits and Credits do not balance in the summary section.
- The detail of a GL Account does not match the Debit and Credit values for the same account in the summary section.
- GL Account Codes are included that have not previously been created in the Chart of Accounts.
- Part/Client/Vendor Numbers are used as the reference but are placed in column B which is for Part Names or vice versa.
- Part/Client/Vendor Name or Part/Client/Vendor Number is used as a reference but more than one record exists in the system with this same identifier.
- Part/Client/Vendor Name or Part/Client/Vendor Number do not exactly match records in the system.
- Current Earnings account is included in the summary section.
- Products with an incorrect Product type are included.
- Products are included that are designated with Kit as the Product Type on the Product record.
- Products are included that are not designated with Inventory as the Product Type on the Product record.
- Inclusion of Products that are Non-Inventory Products.
- Account codes are used for a Client/Vendor that are not used as the AP/AR Aging account. This can be checked on a Client/Vendor record on the Account Information tab.
- Failure to define the Warehouse for Inventory detail.
- Failure to include Lot information for Inventory detail.
- Failure to use a GL Account Code for Inventory detail information that has a Setup Type of A - Inventory.
- Document was saved in MS Excel® and not opened in a text editor to look for and strip quotes that Excel® sometimes puts around certain fields. These quotes are not visible in Excel®.
- Document is not saved as a tab-delimited text file.
- Extraneous information is included in other columns.
- Headers have been removed. These MUST stay in place for this particular import.
- EOF (end of file) tag not included.