ctcLink Reference Center9.2 Finance9.2 FIN - General Ledger Journals9.2 Uploading a Journal Spreadsheet as a Text File

9.2 Uploading a Journal Spreadsheet as a Text File

Purpose: Use this document as a reference for how to upload a journal spreadsheet as a text file in ctcLink.

Audience: Financial staff

Occasionally, you will find a need to convert your journal spreadsheet into a text file for manual upload into ctcLink.  When the journal has a large number of lines, it is advisable to use this method to load your journal to avoid system time-out issues.

Prerequisite: You should have already created and saved your journal spreadsheet using the Spreadsheet Import Tool as described in Quick Reference Guide Importing a Journal Spreadsheet.

This process begins with the Spreadsheet Import Tool and your journal already created and saved.

Saving the Spreadsheet as a txt File

  1. Before you can import a journal flat file, you must take your journal spreadsheet that was created using the Spreadsheet Import Tool, edit it, change the import status and save it. From the Spreadsheet Journal Import Control page  (JRNL1.xlsm file), select the Edit button.
Spreadsheet Journal Import control page
  1. On the Edit Journal Sheet page, select your journal and select the OK button.
Edit Journal Sheet
  1. The spreadsheet journal opens. Select the Change Import Status button.
Spreadsheet Journal Import
  1. Select the Import radio button and then select the OK button.
Change Import Status
  1. Select the Save button in Excel to save the spreadsheet.
  2. Select the Home button to return to the Spreadsheet Journal Import control page.
Excel Spreadsheet example
  1. From the Spreadsheet Journal Import Control page, select the Write File button.
Spreadsheet Journal Import control page
  1. Use the Write Journals to File page to select the Excel file to write to a text file for external upload to ctcLink:
    1. Select the appropriate journal in the Select Sheet field.
    2. In the File Name field, the menu path defaults in the computer location to which the new file will be located once the file is written. Write over this portion of the string with the name of the new file that you want to import into ctcLink, i.e. the name of your journal (my new File name for the screen capture below looks like this: C: Users\SVenable\Documents\GLUpload.txt).
    3. Select the OK button.
Write Journals to File

Importing Spreadsheet Text Files

Navigation:  NavBar > Navigator > General Ledger > Journals > Import Journals > Spreadsheet Journals

  1. The Spreadsheet Journal Import run control ID search page displays.
  2. Select the Add a New Value tab.
  3. Select Add.
Spreadsheet Journal Import Add a New Value tab
  1. The Spreadsheet Journal Import Request page displays.
  2. In the Process Request Parameters section, enter the file processing parameters.
    1. Leave the defaulted values as is for these fields:
      • Number of Data Files
      • Character Set
      • If Journal Already Exists
      • If Journal is Invalid
    2. Select the Add button, navigate to the text file located on your computer and upload in order to attach. After this step is complete, the Attached File name appears on the Spreadsheet Journal Import Request page.

If you are re-using a run control, you will have to delete the current file before you can add a new file.

  1. Select Run.
Spreadsheet Journal Import Request page
  1. Use the Process Scheduler Request page to enter or update parameters, such as server name and process output format.
    1. Use the Server Name drop-down button to select the appropriate processing server for the request.

Note: You can leave the Server Name field blank.

  1. Select OK.
Process Scheduler Request

Note: A Process Instance number appears. This number helps you identify the process you have run when you check its status.

  1. Select Process Monitor.
Spreadsheet Journal Import Request
  1. Use the Process List page to view the status of submitted process requests.

Note: The process has finished successfully when the Run Status column indicates “Success” and the Distribution Status is “Posted”.

  1. Select the Details link for the GL_EXCL_JRNL process name.
Process List tab
  1. Select the Message Log link.
Process Detail
  1. Verify that the process completed and that the journal imported successfully.
Message Log

Importing Spreadsheet Text Files

Finally, you should navigate to the Create/Update Journal Entries page to view your journals.

Navigation:  NavBar > Navigator > General Ledger > Journals > Journal Entry > Create/Update Journal Entries

  1. Use the Find an existing Value tab on the Create/Update Journal Entries page to locate your newly posted journal.
    1. If it did not automatically populate, enter or select the business unit in the Business Unit field.
    2. Clear the Journal Header Status field.
    3. Enter ‘EXT’ in the Source field.
    4. Clear the Entered by field.
    5. Select the Search button.
  1. Scroll down to locate your journal and then select the Journal ID link to open.
Create Update Journal Entries Find an Existing Value tab
  1. Select the OK button to acknowledge the message.
  1. Review the Header tab and then select the Lines tab.
Header tab
  1. Follow the steps outlined in QRG Creating and Posting a Manual Journal Entry to edit and submit the journal for approval.
Lines tab
  1. Process complete.


Add your comment

E-Mail me when someone replies to this comment