9.2 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 journal importing and to 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:

  1. 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.
  2. 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.

Importing a Journal Spreadsheet

Prerequisites: The Spreadsheet Import Tool files, JRNL1_WS.xlsm and JRNLMCRO_WS.xlam, have been pre-configured and you know where they are located.  Contact ERP Support at [email protected] if you don't.  Both files must be saved to the same folder on your computer.

  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_WS.xlsm – This is the journal workbook that you use to create and import journals.
      • JRNLMCRO_WS.xlam – This is the Visual Basic code library and dialog control.

Note: Use the JRNL1_WS.xlsm file to enter your journals. 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_WS Excel file. The Spreadsheet Journal Import page will displays.
    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.
  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 & Defaults – Select 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 – Select 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 Sheet – Select 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 Sheet – Select to edit one journal sheet in the workbook.
    3. Delete Sheet – Select to delete one or more journal sheets in the workbook.
    4. Copy Sheet – Select 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 – Select to initiate online import of one or more journal sheets. The system imports only journals that are marked as import.
    2. Write to File – Select 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.
Spreadsheet Journal Import page

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, select 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
  2. 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 (RECOMMENDED).

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.

It is most common to use NEXT for the journal ID.

  • Skip If Journal Has Error – 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. Configure 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.
Define Options and Defaults dialog box
  1. Within the ChartField Configuration box, optional to format the Columns and control its appearance. You can alter the contents of your spreadsheet only one column at a time. The column that you intend to edit is highlighted.
    • Select Column section selects the column:
      • Left Yellow Arrow icon - moves the selected column (grayed out) field to the left.
      • Right Yellow Arrow icon - moves the selected column (grayed out) field to the right.
    • Column options to perform actions to the column:
      • Insert down arrow icon - inserts column
      • Right Arrow icon - shifts selected column to the right
      • Left Arrow icon - shifts selected column to the left
      • Width plus [+] or minus [-] icons - widen or narrows the selected column
    • Select Exit button to close and save changes.
Chartfield Configuration for columns

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

  1. Within the Chartfield Configuration box, optional to 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. Optional to locally update.
    • Format– Specify the cell format.
    • Apply– You must select to save this format for the journal sheet.
    • Select Exit button to close and save changes.

The Product field name is actually the Appropriation Index in ctcLink PeopleSoft. Do not change the Field Name but recommended to update the label name to 'App Index' or 'AI'.

Field format config
  1. Return to the Spreadsheet Journal Import control page by selecting the X in the upper right corner of the Chartfield Configuration page, then the Home icon on the sheet.
Creating a New Journal Spreadsheet
  1. Select the New Sheet 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. Select OK.
New Journal Sheet window

Journal Import sheet functions.

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

Journal 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. Select 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.
      • Select OK.
Select the Plus icon in the Journal Lines section

Journal 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.
Journal Lines section
  1. Select the “+” in the Journal Lines section to add the first line to the spreadsheet journal.
Spreadsheet Journal Import
  1. Select the "paper with a plus icon" in the Journal Lines section to add multiple lines to the spreadsheet journal. The Insert Multiple Lines box will open. Enter the number of lines to add and select Insert.
Select the Paper with plus icon
  1. Select the "x" icon in the Journal Lines section to delete one line.
Select the X icon
  1. Select the "paper with an x" icon in the Journal Lines section to delete multiple lines.
Select the Paper with an X icon
  1. Populate the journal line data using the Tab key or arrow keys to advance from one cell to the next.
    1. Continue to select the “+” in the Journal Lines section to add one additional line to the spreadsheet journal, or select 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. Select Save in Excel to save the spreadsheet.
    4. Select Home to return to the Spreadsheet Journal Import control page.

PRO-TIP:  Use the 'Reference' and 'Description' fields to enter in your comments about the specific line item or the entire journal description.

Spreadsheet Journal Import
Importing a Journal Spreadsheet
  1. Select Import Now icon located on the right side of the Journal Entry Sheet 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. Select OK to import the journal.
Select Import Now icon

Optional to select Import Now functionality from the main Spreadsheet Journal Import screen.

Select applicable sheet and select OK
  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.
  2. Next step would be to review/edit the Journal ID (i.e. 0000000635). Please refer to QRG 9.2 Viewing and Updating Journal Entries.
  3. Process complete.

Video Tutorial

The video below demonstrates the process actions described in steps listed above.  There is no audio included with this video.  Select the play button to start the video. - coming soon!

Video Tutorial via Panopto

View the link to Importing a Journal Spreadsheet. This link will open in a new tab/window. - coming soon!


Add your comment

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.