NOTE For general information on using imports, see Imports FAQ.
The following tables list the format for importing a header or trailer record in Journal Entry Import.
|
Column |
Excel/Delimited Starting Position |
ASCII Starting Position |
Field |
Maximum Length |
Format |
Required |
Description |
|
A |
1 |
1 |
Line Type |
1 |
H |
Yes |
Enter H to specify a header record. You must have exactly one header record per journal entry. |
|
B |
2 |
2 |
Comment |
8 |
Character |
Yes |
Enter the comment that appears next to the journal entry number in inquiries and reports. |
|
C |
3 |
10 |
Posting Year |
4 |
9999 |
No |
Enter the general ledger accounting year against which the journal entry is being made. The posting year must be open. If this field is blank, it defaults to the current general ledger year. |
|
D |
4 |
14 |
Posting Period |
2 |
99 |
No |
Enter the general ledger accounting period against which the journal entry is being made. The posting period must be open. If this field is blank, it defaults to the current general ledger period. |
|
E |
5 |
16 |
Sum of all Trailer Credits |
18 |
>>>,>>>,>>>,>>9.99 |
Yes |
Enter the sum of all trailer credits. The value for this field must be positive. This amount must match the actual trailer credit amount imported, excluding statistical accounts. |
|
F |
6 |
34 |
Count of all Trailer Records |
5 |
Integer |
Yes |
Enter the number of trailer records associated with the header record. The count must match the actual number of trailer records in the import file before the next header record. |
|
G |
7 |
39 |
Remark |
60 |
Character |
No |
Enter a remark that appears in inquiries and reports. |
|
H |
8 |
99 |
Reversing Entry |
10 |
Character |
No |
Enter one of the following to indicate if you want to generate a reversing entry for the journal entry:
If this field is blank, it defaults to none. |
|
I |
9 |
109 |
Reversing to Year |
4 |
9999 |
No |
Enter the year for the automatic reversal (if you are creating one). If this field is blank, it defaults to the year for the next period. |
|
J |
10 |
113 |
Reversing to Period |
2 |
99 |
No |
Enter the period for the automatic reversal (if you are creating one). If this field is blank, it defaults to the next period. |
|
K |
11 |
115 |
Currency |
8 |
Character |
No |
Enter a valid currency for the journal entry. If this field is blank, the journal entry is created in the default general ledger currency. |
|
L |
12 |
123 |
Override Currency Exchange Rate |
5 |
Yes/No |
No |
Enter Yes or Y to manually enter the currency exchange rate for the journal entry. Leave this field blank if you entered the default general ledger currency for the Currency field (or left it blank). |
|
M |
13 |
128 |
Override Currency Exchange Rate Value |
14 |
99999.99999999 |
Yes, only if Yes or Y is entered for the Override Rate field |
Enter the currency exchange rate for the journal entry if it is different than the rate set up in Currency Exchange Rate Maintenance. Leave this field blank if you entered the default general ledger currency for the Currency field (or left it blank). |
|
N |
14 |
142 |
VAT Date |
10 |
99/99/9999 |
No |
Enter the VAT date used to determine the exchange rate for the journal entry. If this is blank, it defaults to the current date. |
|
O |
15 |
152 |
Transaction Date |
10 |
99/99/9999 |
No |
Enter the date of the transaction for the journal entry. If this is blank, it defaults to the current date. |
|
Column |
Excel/Delimited Starting Position |
ASCII Starting Position |
Field |
Maximum Length |
Format |
Required |
Description |
|
A |
1 |
1 |
Line Type |
1 |
T |
Yes |
Enter T to specify a trailer record (credit or debit). You must have at least one trailer record per header record, with a maximum of 5,000 trailer records per header record. |
|
B |
2 |
2 |
Credit/Debit |
2 |
Character |
Yes |
Enter CR for a credit or DR for a debit. The sum of credits must equal the sum of debits, excluding statistical accounts. |
|
C |
3 |
4 |
Amount |
18 |
>>>,>>>,>>>,>>9.99 |
Yes |
Enter an amount for the credit or debit. The amount must be positive. |
|
D |
4 |
22 |
General Ledger Account |
38 |
Character |
Yes |
Enter the general ledger account number for the credit or debit. This field must be formatted with hyphens, decimals, spaces, etc., according to your general ledger setup. The account number must already exist. |
|
E |
5 |
60 |
Project Number |
15 |
Character |
Yes, only if the general ledger account entered for the General Ledger Account field requires a project number |
Enter a valid project number against which the credit or debit applies. |
|
F |
6 |
75 |
Remark |
100 |
Character |
No |
Enter a remark associated with the credit or debit. |
|
G |
7 |
175 |
VAT Entry |
5 |
Yes/No |
No |
Enter Yes or Y if the credit or debit is a VAT entry. This field defaults to Yes if you leave it blank for a credit or debit associated with a VAT account and the Assume VAT Account Entries Taxable check box is selected in Journal Entry Import. This field defaults to No if you leave it blank for a credit or debit that is not associated with a VAT account. |
|
H |
8 |
180 |
Input VAT/Output VAT |
6 |
Character |
No |
Enter Input for an input VAT entry or Output for an output VAT entry. If you enter an input VAT or output VAT account in the G/L Account field, you can leave this field blank and it will default based on the account. |
|
I |
9 |
186 |
VAT Class Code |
20 |
Character |
No |
Enter the VAT class code for the journal entry. For information on setting up VAT classes, see VAT Class Maintenance. Note Leave this field blank if you enter No or N for the VAT Entry field. |
|
J |
10 |
206 |
Country Code |
10 |
Character |
No |
Enter the country for the journal entry. For information on setting up countries, see Country Maintenance. |
|
K |
11 |
216 |
Bank Reconciliation Complete |
5 |
Yes/No |
No |
Enter Yes, Y, True, or T if bank reconciliation is already completed for the credit or debit, and you do not want the journal entry to appear in Bank Reconciliation. This field defaults to No if you leave it blank. |
|
L |
12 |
221 |
VAT ID |
16 |
Character |
No |
Enter your company's VAT ID for the credit or debit. For information on setting up VAT IDs, see VAT ID Maintenance. If this is blank, it defaults to the VAT ID for the company account element of the first entered general ledger account. |
Before using this import, the following must be set up:
general ledger accounts
Any record beginning with a ";" in column 1 is treated as a comment and ignored, so you may add comments to the import file.
The trailer lines (T) for a single journal entry must immediately follow a single header line (H). Do not group all your header lines (H) for different journal entries together.
The import file may have a maximum of 5,000 trailers for a single header.
There must be a record of blank space(s) at the end of an import file.
If you are importing from a text file with fixed lengths for each field, use the ASCII Starting Position column.
If you are importing from Excel or a caret-delimited text file, use the Excel/Delimited Starting Position column.
If you are importing from an Excel file, you must add the Excel path for your user ID in User Profile Maintenance.
The maximum length for each field is automatically enforced, and additional characters are not imported. For example, if you import 20 characters for a field with a maximum length of 8 characters, only the first 8 characters are imported.
The following table depicts how the journal entry import should be set up in an Excel spreadsheet:
|
|
A |
B |
C |
D |
E |
F |
G |
H |
|
1 |
H |
Sample |
2020 |
1 |
1,000.00 |
2 |
Sample Journal Entry Remark |
none |
|
2 |
T |
DR |
1,000.00 |
10.1105 |
|
|
No |
|
|
3 |
T |
CR |
1,000.00 |
10.1305 |
|
|
No |
|