ctcLink Reference Center9.2 Finance9.2 FIN - General LedgerJournals: Create & Import9.2 Uploading a Journal Spreadsheet as a Text File (Write to File)

9.2 Uploading a Journal Spreadsheet as a Text File (Write to File)

Purpose: Use this document as a reference for how to upload a journal spreadsheet as a text file or 'Write to 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.

You must have at least one of these local college managed security roles:

  • ZZ GL Journal Entry

You must also set these User Preference Definitions:

If you need assistance with the above security roles or User Preference Definitions, please contact your local college supervisor or IT Admin to request role access.

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.

Uploading a Journal Spreadsheet as a Text File

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_WS.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. The Change Import Status window opens. 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 to 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: 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. In the Journal Processing Options section, select the Edit Journal(s) box.
    3. 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 Journal Import with Edit then 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 View Log/Trace link to check for any errors.

Note: Once errors are addressed, the View Log/Trace log will read import successful and you can move onto the next step.

Process Detail
  1. Verify that the process completed and that the journal imported successfully.
Message Log
Reviewing Journal Imported Spreadsheets

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

Navigation:  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.
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.

0 Comments

Add your comment

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