Journal Entry Import Setup
Settings > General Ledger > Journal Entry Imports
Data can be imported from outside systems into SGA as a journal entry batch. This can be used for payroll, sales systems, etc. Journal entries, A/P invoices, and other data can also be imported from a spreadsheet. The import format must be in place before importing data into SGA. It is maintained under the menu Settings > General Ledger > Journal Entry Imports. The following are the settings for various types of import formats.
Name: The name that will be displayed as the name of the import.
General: See import connections for help setting up a connection to import files.
Source: The JE source when importing to a journal entry.
Attachment: You can add the attachments of templates or Schema needed to import.
Account: You can import the account# as just one or multiple columns. The account# column(s) in Excel must be formatted as Text to import correctly. The formatting must be done before the entry is made, or Excel will interpret the account# as numeric instead of text, which will not import correctly into SGA.
Relative Account: Defines what account number to relate to from the system being imported from. If the account number structure being imported doesn’t relate to the SGA structure, then you will need to use an Alias or cross-reference. When importing or validating, a log will be displayed with the accounts if any account is not found or inactive.
Chars Before: Specify any characters to add before the column.
Column: The column for the account or account division. If the account# is in just one column, then choose that column for each division and specify the start and length for each division.
Start/Length: You can substring the column if you wish; otherwise, leave this blank.
Pad Char: Fills the text with this character for the length specified.
Justify: Used for the pad character only.
Chars After: Specify any characters to add after the column.
Columns
Amount format: Determines how amounts are imported.
One column per row: Amounts are contained within a single column. Credits are determined as negative numbers. The negative symbol must be on the left side of the amount.
One column per row: another column dictates sign: Amounts are contained within a single column. However, another column determines if it is a debit or credit. Debits are identified by a D or Debit. Credits are identified by a C or Credit.
Separate columns for debits and credits: The amounts are divided into two columns: one for debits and the other for credits.
Specify date: Check this if you have a column with a date so that it uses that date. Otherwise, it will use today’s date.
Specify period: Check this if you have a column with a period so that it uses that period. Otherwise, it will use the current period, but you can change it when posting.
Comments: Specify the comments columns if you have one that you’d like to use. If not, you can specify a comment for all line items in the comments before.
Update
Update rows imported: If you need to update rows imported in the original file, check this box. You will do this if you want to make sure you don’t import the same records twice. However, this can only be done if the file allows it because it needs a posted status column or something.
For example, say you have a column called Posted with a value of 0 or 1. In your SELECT command text, you would include the following:
SELECT * FROM Transactions
WHERE Posted = 0
Then, in the UPDATE command text, you would include the following:
UPDATE Transactions
SET Posted = 1
Command text: The UPDATE command should include the SET clause. This should not include a WHERE clause because the WHERE clause from the SELECT command will be appended to it.
If you are importing entries with multiple lines per account and wish to summarize your journal entry, you can do so. Just key something like this in the command text:
SELECT Account, SUM(Amount), MIN(Comments) AS Comments, Date, Month
FROM MySourceTable
GROUP BY Account, Date, Month
The menu option to import is Daily > Journal Entry Batches > New/Import.
- Click the Import button.
- Click the Edit button to select the file to import.
You may click the Validate button before performing the actual import. When you click the Import button, the data will be imported onto the screen for review. The batch can then be reviewed and edited if necessary before posting.