How do I do a GL Journal Entry Upload from an Excel spreadsheet?

Question:

How do I do a GL Journal Entry Upload from an Excel spreadsheet?

Resolution:

Journal Entry Upload from Excel Spreadsheet

1. Create an Excel spreadsheet that matches the GL Journal Entry File (excluding contra fields).  During the installation of the new programs, a template spreadsheet is provided for your use.
2. Create the journal entries within the Excel spreadsheet.  At a minimum, the following fields must contain data:  Div#, Account# and Amount.  The following fields can be entered into the spreadsheet on a line by line basis OR they (any or all) can be defaulted from the program that uploads the file: Corp#, Accounting Period, JE#/Book Code and Explanation.  The default values will ONLY be assigned to those lines within the spreadsheet that have NO data in those particular fields.  The remaining fields are optional depending on your particular situation: Dept#, Job#, MAD#.  There is no on-line validation of the data in the spreadsheet as that function will be performed once the GL Journal Entry batch is created.
3. Formatting of various fields:  the accounting period must be entered as CCYYPD (century, year and period- 200602).  The explanation field can be entered in upper and/or lower case but will be converted to upper case in the GL batch.  The amount field must be formatted with 2 decimal places. 
4. In order to upload the spreadsheet to the DAPREX GL system, you must first save it as a .csv file, delete the column headings and total rows and save it again as a .csv file.  Now you are ready to run the upload.
5. On your desktop you will see the DAPREX JE Upload icon.  Click on it  and fill in the following information as necessary:

UserID (required) – this is the UserID under which the batch is to be created on the  AS/400. 

Batch Description (optional) – this is the default GL explanation assigned to lines without them.

Corp# (optional) –this is the default Corp# assigned to lines without a Corp#.

Accounting Period (optional) – this is the default accounting period assigned to lines without an accounting period.

JE#/Book Code (optional) - this is the default JE#/Book Code assigned to lines without a JE#/Book Code.

1. Now enter the journal entry directory and file name or click the Browse button and navigate to it.
2. Click the upload button to upload the file and create the Journal Entry Batch.
3. Click the Close button to close the window.
4. Go to the AS/400 session and access the batch via the Daily Processing Menu (if it was created under your UserID) or the Supervisor Menu (if it was created under another UserID).  Continue as you would normally to edit and update the batch.