G/L Transactions (aka Journal Entries) Import

System Requirements: Connected Version 11.1 or greater is required.

Importing G/L Transactions (aka Journal Entries) has numerous uses.  For example, recording transactions from a POS system, accountant year end entries, beginning balances, or just any financial transaction from a third party source.

Article Contents

• Import G/L Transactions Window
• Importable fields include (in default field mapping order):
• Using Manual Field Mapping
• Notes
• Possible Errors

Import G/L Transactions Window

To access the G/L Transaction Import, select Import from the File menu and choose G/L Transactions to display the following screen.

NOTE: Import file format must be a tab delimited text file.

TIP: Click the icon, as shown below, to export an import template with field names and definitions.

Importable fields include (in default field mapping order):

Transaction ID - required; 15 characters max; related import records should have the same value; see note below

Date - journal entry date; required; must be in an open accounting period; related import records should have the same value

Account No - required; must be a valid G/L account number

Debit - required; must be a positive number; see note below

Credit - required; must be a positive number; see note below

Journal Description - 1024 characters max; related import records should have the same value

Analysis Code - must be a valid General Journal analysis code; related import records should have the same value

Account Description - 80 characters max; if blank in the import file, the G/L account description is used.  Suggested use could be additional description for the entry.  Example, type of bank charge (Wire fee, credit card usage fee, Paper statement fee, etc.)

Using Manual Field Mapping

Manual field mapping is helpful when the import file does not match the the default order. Fields can be mapped using a drag and drop interface. 

To manually map an import file:

1) Select the "Use Manual Mapping" radio button.

2) Click the "Load Import File Header" button, as shown below. 

3) Select a text file that contains the column headings of the data to be imported and click ok to populate the "Unmapped Import File Fields" column, on the left side of the window.

4) Drag the fields, from the left, to the appropriate Connected fields on the right side of the screen.  Ensure that all (required) fields are been mapped.

NOTE: Manual mapping will be remembered, even when Connected is closed, unless "Use Default Mapping" is selected and the warning agreed to.

Importing Data

1) After the fields have been mapped, click on the "Step 2: Preview and Import Data" tab at the top of the window.

2) Select the checkbox to the left of "Import File contains a Header Row", if applicable.

3) Click the "Preview from File" button to review the data that is to be imported.  Previewed data can be imported or cleared, using the "Import Previewed Data" and "Clear Preview" buttons.

4) If no preview is required, the text file can be imported directly by clicking the "Import from File..." button and selecting the import text file.  

5) Any Messages or Warnings and Errors will appear in the sections outlined below.

Notes

 - Transaction IDs are used to identify account allocations belonging to the same journal entry.

 - A debit or credit amount is required for each account allocation.

 - For each transaction, the total debits must balance the total credits.

Special Character Handling: Descriptions containing single and double quote characters and carriage returns cannot be imported directly. Using a text editor application, these characters must be removed and substituted with the following strings prior to import. During the import process, the substitute strings will then be replaced with the original characters.  Substitute /dqt/ for double quotes, /sqt/ for single quotes and /rtn/ for carriage returns/line breaks. The forward slashes must be included.

Possible Errors

• Field mapping is incorrect
• Required fields do not match or are missing
  • Debits do not equal credits
    
  • Journal date is outside of the fiscal year, or in a closed period
  • Invalid G/L Account number
  • Invalid Analysis Code