MV Software Knowledge Bas…  Modules»General Ledger»Upload G/L Entries from a Spre…
  
Page Collections
Attributes
Hierarchy
  • RSS Feed

Last modified on 2/25/2016 4:27 PM by User.

Tags:
Upload, G/L, Entries, Spreadsheet

Upload G/L Entries from a Spreadsheet

Creating the entry:

Manual General Ledger entries can be created in a spreadsheet then imported into Profits Plus G/L module.  Below is an example of how the spreadsheet should be set up.  The spreadsheet must have columns A through I.  The column headings can’t be on the spreadsheet.

 

Column A       B               C               D             E             F           G            H            I

4209001        G/L          121.10           0        07/25/20xx    REF    REF       GA123        X

6150001        G/L              0           121.10    07/25/20xx    REF    REF       GA124        X

4209001        G/L            20.00          0         07/25/20xx    REF    REF       GA123        X

6450001        G/L              0             20.00    07/25/20xx    REF    REF       GA124        X

 

Note:

  1. Column A is the GL number. It may or may not have the dashes -.
  2. Column B is the Source Identifier (3 characters), i.e. G/L, ADJ (for Adjustment)
  3. Column C (debit) and Column D (credit). For ‘cents’, add the decimal (i.e. 121.10), for ‘zero cents’ enter 2000 for $2,000.00.  Commas , can’t be in the dollar amounts.
  4. The date in Column E doesn’t need to be entered, as the date of the transaction is determined in a later step; however DO NOT use Column E for something else…leave it blank
  5. Columns F & G….will be combined into one reference field of each line.  The reference field is 30 characters.   So the total of the two columns shouldn’t be more than 30 characters.
    • If you want just one word to appear in the Reference field on the journal entry, (or if using one column, up to 30 characters long for the reference), enter it in Column G and leave Column F blank.  The reason for leaving column F blank is so the spreadsheet has 9 columns.  The import looks for 9 columns.
    • A reason for using both columns on the spreadsheet: is for an employee, the first name could go in Column F and the second name in Column G.  Columns F & G will be combined into the reference field on each line.
  6. Column H will be the subdivision code. This subdivision code should be setup in the Subdivision master.  This can be alphanumeric. If this column isn’t needed leave it blank.
  7. Column I MUST be an x.  This is an end of line placeholder.

 

 

Save the spreadsheet in lower case, as a .csv file to /u/mvs/dsk0/23000x (with the ‘x’ being the company number from the Company List).  Note the name of the saved file.

 

 

 

Importing the entry:

Go to:

  • General Ledger (Module 4)
    • Manual Trx Entry and Editing (Task 1)
      • Enter (Add) New Transaction (Sub-task 1)

On the first screen, press the Enter key to get the system-generated Transaction Number.  Enter a description of the entry.  Enter the date of the transaction (this is the date that will be used for the entire transaction).  If the transaction is a reversing transaction, enter the date the system should use as the reversal date. 

 

On the second screen, press the F2 key to import the file. 

  • Type in the name of the saved file at the system prompt on the first line, i.e. year_end_adj_2010.csv
  • The system will tell you the # of records found in the spreadsheet. 
  • After the prompt for ‘Any Change’, the system will tell you if you have any G/L account #’s not on file.
    • If you have any G/L account #’s not on file, these will need to be corrected before posting.
  • It will also display the number of ‘detail records’ imported (which should be the number of entries on the spreadsheet).  To review and/or change any of the entries, reply Y to the “Review Line Items? Y/N”.
    • Look for the lines with “**ACCOUNT # NOT ON FILE**” to make corrections.
  • If you get the message “Debits and Credits do NOT balance.  Do you want to leave it that way?  (YES/NO): ___”.  Type in NO to make corrections.  This will need to be corrected before posting.  An edit list may need to be printed to see the reason for the imbalance.
    • Still in Manual TRX entry & editing (Task 1), go into Print transaction edit list (Sub-task 4).  Look for the lines with “**ACCOUNT # NOT ON FILE**”.  Also look for the reason for the imbalance of the debits & credits if necessary.
      • Go into Change existing transaction (Sub-task 2) to make necessary changes.
  • When everything is correct, go to Post Manual & Interface TRX’s.  This will post everything in Manual TRX entry & editing (Task 1) & everything in Modify interface transactions (Task 3).   Make sure the “Out of Bal” is blank & “Errors” is blank on the screen before answering YES the second time.