Create an ADP Journal Entry Import Batch

You can create an ADP Journal Entry Batch through Excel if the Juris ADP utility does not work for your type of GLI file. Here's how:

  1. Open Microsoft Excel and select File > Open.
  2. Change the "Files of Type" (at the bottom of the window) to "All Files (*.*)"
  3. Find your ADP export file (the GLI file). An example name would be; "GD1234P.GLI"

  4. The Text Import Wizard opens.  

    • In Step 1, accept the defaults.
    • In Step 2, make sure that the box for "Comma" is selected.
    • In Step 3, click Finish.

  5. Your payroll information will be imported into Excel.  Now you must add and remove information in order to format the data to match Juris rules for Journal Entry Import. See overview below:

    • The first column must contain the Juris Chart of Accounts Account Number.  Insert a blank column at the beginning to enter this information. Don't forget to include the Profit Center if your firm uses Profit Centers.
    • Enter a blank column to the right of the column above for the date.  You may enter the transaction date once and copy to all of the cells in the column.
    • Enter a blank column to the right of the column above for the Source.  You can leave this blank, or you may choose to populate all cells with the text "ADP Import" to remind you that the Journal Entries were created from an ADP Import batch.
    • Enter a blank column to the right of the column above for the Journal Number. Leave these fields blank.
    • Enter a blank column to the right of the column above for the Journal Sequence Number. Leave these fields blank.
    • Enter a blank column to the right of the Journal Sequence Number column for the Document Number. You may leave these fields blank - or you may enter an identifying number for item up to 15 characters in length, or you may copy an identifying column of data from the ADP file and paste into this column as long as not item exceeds 15 characters in length.
    • The Amount column is next.  This column will exist in your ADP file,  Copy or move this column to the right of the Document Number Column.
    • Enter another blank column to the right of the column above for the Correcting Entry Flag.  Type the letter N into all fields in this column.
    • Enter another blank column to the right of the column above for the Reverse Next Period Flag. Type the letter N into all fields in this column.
    • The last column is the Reference column. You may leave these fields blank - or you may enter a Narrative of each Journal Entry up to 200 characters in length, or you may copy a Narrative of Journal Entry from the ADP file and paste into this column as long as no narrative exceeds 200 characters in length.
    • Delete any other information that may exist in the page.

  6. Now you should have a spreadsheet with 10 columns (A, B, C, D, E, F, G, H, I, J) The information should be arranged as follows:

    • Column A: Juris Chart of Accounts Number (with Profit Center)
    • Column B: Transaction date
    • Column C: Source (Blank or "ADP Import")
    • Column D: Journal Number (Must be Blank)
    • Column E: Journal Sequence Number (Must be Blank)
    • Column F: Document Number (May be Blank or May be an ID number up to 15 characters long)
    • Column G: Amount of Entry
    • Column H: Correcting Entry Flag (Must be 'N')
    • Column I: Reverse Next Period Flag (Must be 'N')
    • Column J: Reference (May be Blank or May be a Narrative up to 200 characters in length)
  7. In Excel, select File > Save As.  
  8. In the "Save as Type" option at the bottom of the Save As window, select CSV (comma separated variable file).  
  9. Give the file an appropriate name and save in an appropriate location for archive purposes.
  10. Now you can import the file into Juris.

NOTE: This can be a tedious process the first time it is done, but once you have created your first export file using Excel, you can use that file as a base to create future import files. Create a copy of the prior month's CSV file and open in Excel. Then just delete the contents of Column B (Date) and replace with the correct date.  Delete the contents of Column G (Amount) and replace with the values contained in the current GLI payroll file. If you entered information into Column F and Column J, then you may need to update those columns with values from the current GLI file if there were any changes to those items. The remaining columns should not change from month to month - but it is always recommended to review the information for accuracy before importing.

Related topics