ctcLink Reference CenterFinanceGeneral Ledger JournalsUploading a Journal Spreadsheet as a Text File

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), click the Edit button.
Spreadsheet Journal Import
  1. On the Edit Journal Sheet page, select your journal and click the OK button.
Edit Journal Sheet
  1. The spreadsheet journal opens. Click the Change Import Status button
Spreadsheet Journal Import
  1. Select the Import radio button and then click the OK button.
Change Import Status
  1. Click the Save button in Excel to save the spreadsheet.
  2. Click the Home button to return to the Spreadsheet Journal Import control page.
Excel Spreadsheet example
  1. From the Spreadsheet Journal Import Control page, click the Write File button.
Spreadsheet Journal Import
  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. Click the OK button.
Write Journals to File

Importing Spreadsheet Text Files

Navigation: Main Menu > General Ledger > Journals > Import Journals > Spreadsheet Journals

  1. Enter an existing Run Control ID or click the Add a New Value tab on the Spreadsheet Journal Import page to add a new value.
    1. Enter a meaningful Run Control ID in the Run Control ID field.

Note: A Run Control ID is an identifier that, when paired with your User ID, uniquely identifies the process that you are running. The Run Control ID defines parameters that are used when a process is run. These parameters ensure that when a process runs in the background, the system does not prompt you for additional values.

  1. Click the Add button.
Spreadsheet Journal Import Request
  1. 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. Click the Add button, navigate to the text file located on your computer and upload in order to attach. Once 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. Click the Run button.
Spreadsheet Journal Import Request
  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. Click the OK button.
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. Click the Process Monitor link.
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”.

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

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

Navigate: Main Menu > General Ledger > Journals > Journal Entry > Create/Update Journal Entries

Importing Spreadsheet Text Files

  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. Click the Search button.
Create Update Journal Entries
  1. Scroll down to locate your journal and then click on the Journal ID link to open.
Search Results
  1. Click the OK button to acknowledge the message.
  1. Review the Header tab and then click the Lines tab.
Header tab
  1. Follow the steps as outlined in QRG Creating and Posting a Manual Journal Entry to edit and submit the journal for approval.


Add your comment

E-Mail me when someone replies to this comment