Import Journal Entries

You can import journal entries. When importing journal entries, you must use an import format. The import format specifies the information that is in each column in your import file.

Configure the GL Posting Import Format

Two default import formats are preconfigured during implementation. These are named USI GL Import Template and USI GL Import Template - No Control. Although these USI templates cannot be edited, you can copy or create your own import format to add or edit the detail fields/columns.

GL Posting Import formats generally only need to be configured once.

To create your own import format:

  1. Navigate to the GL Posting Import Formats page and click Add. The Add GL Posting Imports Header page opens.
  2. Enter the following information:
    • Description: Name of the import format. 
    • Control: Determines if the import file uses a control value. A control value means the first line in the import file is used to help determine what fields to use when importing the journal entries. Control is required if you are importing GL Period To Date amounts to specify the Fiscal Period. 
      • No: No control value is used in the import file. Select if using an import file with a delimiter.
      • Yes: Control information is configured in the Batch Control tab in step 4.
    • Fixed: Used with files that do not use comma or tab as delimiters.
      • No: Select if using a comma (csv) or tab (txt) delimited file.
      • Yes: Select if the import file is not delimited and the field length is determined using the Start and End Positions in the import format.
      • Quoted: Select if the delimiters used in your import file are quotation marks.
    • Delimited: Determines how each field is separated (comma or tab) in the import file. 
      • CSV: Select if using a csv (Excel) file.
      • None: No delimiter is used in the file and the file is a fixed length.
      • Tab Delimited: Select if using a .txt file.
    • Export/Import File Name: Default file name for the import file.
    • Skip First Row: If checked, the import process ignores the first row in the import file. Check this check box if the first row of your import file is column descriptions.
  3. Select the Batch Control tab if you selected Yes in the Control drop-down. If you selected No in the control drop-down, skip to step 8.
  4. Click Add. The Add GL Posting Import Detail window opens.
  5. Enter the necessary information: 
    • Order: Numeric value to represent the order the line is in the file. 
    • Column Label: Column name for the column.
      • <Empty Field>: Indicates no information is in the column.
      • <Specify Constant>: Indicates a specific value is in the column. You enter the value into the Value field.
    • Fill Factor: Determines how empty values fill if there are not enough digits in the import file. Although multiple options are available, the below are usually the only ones used for importing files:  
      • Blanks: A blank value is used for missing digits. Typically used for text fields.
      • Blanks +/- Left: No zeros are added for missing digits and a positive or negative sign shows to the left of the value (+100 or -100). Typically used for decimal values.
      • Zeros: Values show without any positive or negative signs or decimal points. Typically used for non-decimal numeric values.
      • Zeros +/- Left: Zeros are used for missing digits and a positive or negative sign shows to the left of the value (+000000100 or -000000100). Typically used for decimal values.
    • Value: Value for the <Specify Constant>. Only available if <Specify Constant> is selected in the Column Label drop-down.
    • Start Position: Beginning point for the value. For example, the import file contains the number 123456789. If you enter 3 into this field, the import begins with the number in the third position. In this case, 3.
    • End Position: Ending point for the value. For example, the import file contains the number 123456789. If you enter 6 into this field, the import ends with the number in the sixth position. In this case, 6.
    • Show Decimal Point: If checked, include the decimal point in numbers.
    • Number of Decimals - Number of decimals in the value.
  6. Click OK.
  7. Select the Details tab. This is is where you map each column in your import file to the appropriate field. You must complete steps 9 - 11 for the journal entry import format details. You can add additional columns as needed for the import file format.
  8. Click Add. The Add GL Posting Import Detail page opens.
  9. Enter the necessary information: 
    • Order: Enter 10. You must have this line as the first line in the import file format detail.
    • Column Label: Select <Specify Constant>.
    • Value@ Enter EBMSGL101.
  10. Click OK.
  11. Click Add. The Add GL Posting Import Detail window opens.
  12. Enter the necessary information: 
    • Order: Numeric value to represent the order the line is in the file. 
    • Column Label: Column name for the column.
      • <Empty Field>: Indicates no information is in the column.
      • <Specify Constant>: Indicates a specific value is in the column. You enter the value into the Value field.
    • Fill Factor: Determines how empty values fill if there are not enough digits in the import file. Although multiple options are available, the below are usually the only ones used for importing files:  
      • Blanks: A blank value is used for missing digits. Typically used for text fields.
      • Blanks +/- Left: No zeros are added for missing digits and a positive or negative sign shows to the left of the value (+100 or -100). Typically used for decimal values.
      • Zeros: Values show without any positive or negative signs or decimal points. Typically used for non-decimal numeric values.
      • Zeros +/- Left: Zeros are used for missing digits and a positive or negative sign shows to the left of the value (+000000100 or -000000100). Typically used for decimal values.
    • Value: Value for the <Specify Constant>. Only available if <Specify Constant> is selected in the Column Label drop-down.
    • Start Position: Beginning point for the value. For example, the import file contains the number 123456789. If you enter 3 into this field, the import begins with the number in the third position. In this case, 3.
    • End Position: Ending point for the value. For example, the import file contains the number 123456789. If you enter 6 into this field, the import ends with the number in the sixth position. In this case, 6.
    • Show Decimal Point: If checked, include the decimal point in numbers.
    • Number of Decimals: Number of decimals in the value.
  13. Repeat steps 12 and 13 for each column in the import file. See below for a common import file setup.
    import_file.png
  14. Click OK.

Journal Entry Import

To import journal entries:

  1. Navigate to the Journal Entries page, click the Add button. The Select General Ledger Source page opens.
  2. Select Manual Journal Entry from the drop-down.
  3. Click Continue. The Add Journal Entry page opens.
  4. Enter the necessary information:
    • Description: Name for the journal entry.
    • Year - Period: Fiscal year and period for the journal entry.
    • Entry Number: Journal entry number. If *AUTO is entered into this field the number will be assigned as the next available.
    • Transaction Date: Date for the journal entry.
    • Currency: Currency of the journal entry.
  5. Click Save (disk). The page updates to Edit Journal Entry.
  6. Click Tools.
  7. Select Import Journal Entry. The GL Import window opens.
  8. Select the import template from the Format ID drop-down.
  9. Click Choose Files to select the file from your local drive. You must close the import file before you can import it. The Open window opens.
  10. Select the file to import.
  11. Click Open. You return to the GL Import window.
  12. Click OK. 

Related articles

Comments

13 comments

Date Votes
  • Sophie Bossard

    When processing with the GL import and you get this message 'The record identifier GL010 must be in positions 1-9':

    error_importing_detail.png

    Try  deleting all rows below the last record (even though there is nothing in them) and save the file again.

     

     

    0 upvotes

  • Kelvin Lin
    • Edited

    Hi,

    I'm encouter the issue "The record identifier EBMSGL101 must be in position 1-9" during the importing process although  the cvs file does not have extra (blank) row as I did delete it.

    0 upvotes

  • Americas Ungerboeck Consulting

    The file would need to be reviewed for any other issues. I have resolved that issue in the past by highlighting the data I wish to import and copy/pasting into a new worksheet and saving the csv.

    0 upvotes

  • Mary Holmes

    Hi,

    I am trying to use the GL Posting Import to import a journal with GST however am getting the following error - 'One or more records have invalid GL Detail Taxes'. I have set the Tax Type as one of the columns and have entered the tax resource type here for the lines which are taxable. I have tried to enter zeros in the other lines and again without zeros and still get the same error. Is there something I'm missing? Do I need to bring in the Tax Code also?

    Thanks

    Mary 

    0 upvotes

  • Irene Tjandrasa

    Can you please try bring in the tax code? and if  you still getting the same error, you can submit a case and we will look into it. Thanks

     

    0 upvotes

  • Mary Holmes

    Thanks for your reply Irene. I've tried that now but am still getting the same error. We've submitted a case. Thanks

    0 upvotes

  • Kris Corrigan

    Question with Journals. If I cancel posted journals, will it have the same impact as reversing them and posting them that way?

    0 upvotes

  • Irene Tjandrasa

    Hi Kris,

    Posted journals cannot be cancelled. You have to reverse the journal.

     

    Thanks

     

    0 upvotes

  • Kris Corrigan

    Hi 

    Is it possible to add a label to the import, upon trying I am met with the message below?

     

    I would like to add the Transactional Analysis Code which is in the journall entry detail

     

    Thanks

    Kris 

    0 upvotes

  • Chelsea Dolman

    Hi Kris,

    The export format you are trying to change is the USI import template. This cannot be changed, as it is intended to be kept as an un-edited example. You can copy it and save your own version for editing (right click -> copy).

    Once you have have added your own copy, you should be able to add/edit the import detail lines, as required. Be sure to inform any users that you have made changes and that they should use the new version when selecting an import format.

    I will update the article above, to clarify the restrictions around editing the USI templates.

    Many thanks,

    Chelsea

    0 upvotes

  • Stacey Hurley

    I noticed that it is not possible to select the Units column on a journal entry import template. Is it possible to make an enhancement request for this? Thank you

    0 upvotes

  • Sean Tame

    Hi Stacey,

    Is it the "G/L Quantity" field?

    1 upvotes

  • Stacey Hurley

    Hi Sean Tame - it is indeed! Thank you :)

    0 upvotes

Please sign in to leave a comment.