ctcLink Reference CenterGeneral Ledger JournalsImporting a Journal Spreadsheet

Importing a Journal Spreadsheet

Purpose: Use this document as a reference for utilizing the Spreadsheet Import tool to create and import   a multi-line journal spreadsheet in ctcLink.

Audience: Financial Staff

Use the Spreadsheet Journal process to streamline the journal import and simplify journal data entry using Microsoft Excel. Spreadsheet Journal Import uses a front-end interface that provides you with an easy-to-follow menu for entering data, specifying defaults, and importing journals.

After you have created your spreadsheet, the Spreadsheet Journal utility uses one of the two import methods:

  • The online import method uses XML link technology and sends data over the internet, processes the import request, and then replies to the Microsoft Excel interface.
  • The batch import method requires that you write the data to a file, submit and upload one or more files through the browser, and then initiate the Spreadsheet Journal Batch Import process to process the import.

Prerequisites: The Spreadsheet Import Tool files, JRNL1.xlsm,  JRNLMCRO.xlam, and GLLOG.xlt,have been pre-configured and are located at __________.  Both files must be saved to the same folder on your computer.

Utilizing Journal Spreadsheets

  1. To utilize the Journal Upload process the following files must be present on your workstation:
    1. For Microsoft Excel 2007 (and subsequent versions)
      • JRNL1.xlsm – This is the journal workbook that you use to create and import journals.
      • JRNLMCRO.xlam – This is the Visual Basic code library and dialog control.
      • GLLOG.xlt – This is the Message log template.

Note: Use the JRNL1.xls or the JRNL1.xlsm file to enter your journals, depending upon which version of Excel you are using. You must set up Microsoft Excel to accept macros, by navigating to File > Options > Trust Center > Trust Center Settings > Macro Settings and selecting either Disable all macros with notification, or Enable all macros.

  1. Open the JRNL1 Excel file.
    1. If prompted, choose to Enable Macros.
    2. The control buttons on the Control page are grouped into three group boxes:
      • General - Setup workbook defaults, configure ChartFields, and rearrange columns.
      • Journal Sheet - Maintain the journal sheets in the workbook.
      • Import Journals - Import the journal sheets from the workbook.
Spreadsheet Journal Import
  1. The General group box enables you to define options and defaults for this workbook and to enter any notes or calculations concerning imports.
    1. Setup– Click to set up access to the Define Options and Defaults dialog box. Use to set journal header defaults, message logging options, document sequencing options, online import controls, and so on. You can also access the ChartField Configuration dialog box from here.
    2. Notes– Click to access a scratch pad in the workbook. Use the scratch pad for instructions, calculations, notes, and so on.
  2. The Journal Sheets group box enables you to insert a new journal sheet; or edit, delete, or copy an existing journal sheet.
    1. New– Click to insert a new journal sheet. A workbook may contain as many journal sheets as needed, and each journal sheet may contain as many journals as desired.
    2. Edit– Click to edit one journal sheet in the workbook.
    3. Delete– Click to delete one or more journal sheets in the workbook.
    4. Copy– Click to copy one journal sheet to a new journal sheet saved under a new name.
  3. The Import Journals group box enables you to import one or more of the journal sheets and save journal sheets to a file.
    1. Import Now– Click to initiate online import of one or more journal sheets. The system imports only journals that are marked as import.
    2. Write File– Click to save selected journal sheets to a text (txt) file. After saving one or more files, you must run the upload process to complete the text file upload process.

Setting Up Journal Spreadsheets

Before you start entering journals, you must specify the options, defaults, and settings for the journal sheets in your workbook. From the Spreadsheet Journal Import control page, click the Setup button to display the Define Options and Defaults dialog box.

  1. Establish the following Header Defaults:
    1. Business Unit - enter your Business unit
    2. Date - enter a default Journal date.  The date can be overridden on the headers on the individual sheets.
    3. Ledger Group - enter 'ACTUALS'
    4. Source - enter 'EXT'
    5. User ID – enter your user ID for the journal header.
    6. Do not check Enable Multibook or AutoGen Lines
  1. Choose from one of the two Message Options radio button:
  • Log Error Messages Only: The system logs import messages to JRNLLOG.xls only when errors occur.
  • Log Successful and Error Messages: The system logs all import messages to JRNLLOG.xls regardless of the import process being successful.

Note: If you would also like to show import messages online, select the Display Messages Online checkbox. Otherwise, errors go only to the message log JRNLLOG.xls.

  1. Do not select the Enable Document Sequencing checkbox or enter a Default Document Type.
  2. Complete the following fields for Online Import Control:
    • ·Address – the address will already be populated with the correct information.
    • User ID – Enter your logon user ID.
    • After Successful Import –
      • Change Import Status to ‘Do Not Import’: The system changes import status of journals that are imported successfully. This prevents reimport of the journals when you attempt to import them a second time.
      • Keep Import Status as ‘Import’: Select this if you do not want the system to change import status so that you can reimport it later.
      • You can override journal import status at the journal sheet level anytime.
    • Skip If Journal Already Exits – Select this option and online load does not update already existing journals. The option is intended to prevent duplicate journals when a user is unaware that a journal has already been processed. This option is the default and is only applicable for online import using the Import Now functionality.
  3. A message is logged providing details of any journals skipped because they already exist in the system.
  4. If the option is not selected, online load updates already existing journals with valid journal data.

Note: When NEXT is used for the journal ID it is not possible to check if a journal already exists.

  • Skip If Journal Has Errors – Select this option and online load does not load invalid journals. This option is the default and is only applicable for online import using the Import Now functionality.
  • A message is logged providing details of journals that have invalid data.
  • If the option is not selected then the online load does not load either valid or invalid journals for that journal sheet. Online load then proceeds to the next journal sheet and loads all journals in the next journal sheet if all the journals in the next journal sheet are valid.
  1. Configuration button – Use the ChartField Configuration page to access a ChartField Configuration dialog box, in which you can include, exclude, or rearrange columns, as well as alter field labels and column formats for a worksheet.
Charfield Configuration
  1. Setup the Column tab:
    1. You can alter the contents of your spreadsheet one column at a time. The column that you intend to edit is highlighted in blue. The ChartField Configuration– Column page enables you to select a column and control its appearance.
Chartfield Configuration
Arrow explaination
Arrow explaination
Arrow explaination
Arrow explaination
Arrow explaination
Arrow explaination

Note: The configuration dialog box prevents you from deleting or modifying certain system required fields.

  1. Specify details on the Field Format tab:
    • Use the Field Format dialog box to control the content and format of the columns on your spreadsheet.
    • Field Name– You must use a valid database field name from the Journal tables. If you misspell a field name or enter an invalid field name, you will not receive an error message until you attempt to import the journal sheet.
    • Label– Enter the column label for the spreadsheet journal workbook.
    • Format– Specify the cell format.
    • Apply– You must click to save this format for the journal sheet.
Charfield Configuration
  1. Return to the Spreadsheet Journal Import control page by clicking the X in the upper right corner of the Chartfield Configuration, then the Home icon on the sheet..

Creating a New Journal Spreadsheet

  1. Click the New button on the Spreadsheet Journal Import control page to access the New Journal Header page.
    1. Enter an appropriate name for the journal entry spreadsheet you want to create in the Journal Sheet Name field.
    2. Click the OK button.
New Journal Sheet

Journal Import sheet functions.

Sheet Functions
  1. Upload the selected journal to PeopleSoft.
  2. Return to the Home sheet.
Import/Home

Header Functions

  1. Header details.
  2. Add a new Journal.  A sheet can contain more than one Journal.  Each Header is associated with it's corresponding Lines by the Sys ID.
  3. Select a Journal.
  4. Edit a Journal.
  5. Copy a Journal.
  6. Delete a Journal.  
  7. Change Import Status.
Header Functions
  1. Click the “+” button in the Journal Header section to select the spreadsheet header for the spreadsheet journal you want to create.
    1. Verify the header information is correct.
      • Update the Journal ID, if not using 'NEXT'.
      • Entered the desired Journal Date.
      • Enter a Description for the journal.
      • Select the Reversal option.
      • Click OK.
Journal Header

Line Functions

  1. Add one Line.
  2. Delete one Line.
  3. Add multiple lines.

You can define multiple blank lines and then populate them with data from another spreadsheet.

  1. Delete multiple lines.
  2. Check decimal point precision.
Line Functions
  1. Click the “+” in the Journal Lines section to add the first line to the spreadsheet journal.
Spreadsheet Journal Import
  1. Populate the journal line data using the Tab key or arrow keys to advance from one cell to the next.
    1. Continue click the “+” in the Journal Lines section to add one additional line to the spreadsheet journal, or click the "+..." to add multiple lines.  Use the checkboxes above the field names to choose which fields to copy down to the new row(s).
    2. Use the Check button to verify the Amount fields that you have entered contain the proper number of decimal points before you import the journal. The default number of decimal points is 2.
    3. Click the Save button in Excel to save the spreadsheet.
    4. Click the Home button to return to the Spreadsheet Journal Import control page.
Spreadsheet Journal Import

Importing a Journal Spreadsheet

  1. Click the Import Now button on the Spreadsheet Journal Import control page to access the Import Journals Now dialog box.
    1. Select the spreadsheet file for upload in the Select Sheet field.
    2. Enter your password in the Password field.
    3. Click the OK button to import the journal.
Import Journals Now
  1. If the import is successful you will receive the following system message:
Import OK
  1. If the import was not successful, the system message will contain a brief description of the error.  Correct the sheet data and re-import.

0 Comments

Add your comment

E-Mail me when someone replies to this comment