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. This process does not require a User ID and Password however, it requires additional processes within ctcLink.

Audience: Financial staff/supervisors.

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.

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. IMPORT NOW: This 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. This method requires a user ID and password and directly uploads your journal(s) into ctcLink.
  2. WRITE TO FILE: This 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. This method does not require a user ID/password however, there is a few additional steps.

Journal Spreadsheet Setup & Defaults

PREREQUISITES: To utilize the Journal Upload process the following files must be present on your workstation (For Microsoft Excel 2007 and subsequent versions) and be saved to the same folder on your computer. Please see QRG Journal Spreadsheet Template for step-by-step directions how to download the files to your workstation and refer to QRG Customizing Journal Import Template for help with setting up user defaults.

  • JRNL1_WS.xlsm – This is the journal workbook that you use to create and import journals.
  • JRNLMCRO_WS.xlam – This is a Macro; the Visual Basic code library and dialog control.
  • GLLOG.XLT - This is a Macro; the Visual Basic code log control.

Uploading a Journal Spreadsheet as a Text File

Enter Journal Data into the Microsoft Excel Spreadsheet Template

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. Select the New Sheet button on the Spreadsheet Journal Import control page to access the New Journal Header page.
  2. Enter an appropriate name for the journal entry spreadsheet you want to create in the Journal Sheet Name field.
  3. Select the OK button.
Select new sheet
  1. A new Journal Entry Sheet displays. In the Journal Header section, select the plus [+] icon to open and edit the new journal header.
  2. Update the criteria as needed, i.e. add a Description.
  3. Select the OK button to close out the New Journal Header dialog box and generates a Sys ID.
add header
  1. In the Journal Lines section, select the plus [+] icon to manually add a line(s). Enter field values to complete a valid ChartString for each line(s);
    • Non-Grant/Project Criteria: Business Unit, Amount, Account, Oper Unit, Fund, Approp Ind, Class, Dept ID, State Pur. Optional: Program.
    • Grant/Project Criteria: Business Unit, Amount, Account, Oper Unit, Fund, Approp Ind, Class, Dept ID, State Pur, PCBU, Project, Activity, Analysis.

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

Add a line
  1. To add multiple lines, 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 the Insert button. Optional to select checkbox to 'Copy data from line' and enter the line number in the box.
  2. The lines will be added below. Copy and paste values/data into appropriate added columns fields or use the copy down function. Delete any unused lines by using the Journal Lines 'X' icon or to delete multiple lines, select the paper with the 'X' icon.
add multiple lines

For help with valid ChartStrings, please see QRGs Common Combo Edit Rule Errors and Common Budget Exceptions and Corrective Actions.

  1. After your ChartStrings are cash-balanced and your journal is complete and ready to import data. Select the Save button on your computer.
Cash balanced journal spreadsheet

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.

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 resave it. From the Spreadsheet Journal Import Control page  (JRNL1_WS.xlsm file), select the Edit Sheet button.
  2. The Edit Journal Sheet dialog box displays. Select your journal and select the OK button.
Select journal
  1. The spreadsheet journal opens. From the Journal Header menu, select the Change Import Status icon button.
select change import status
  1. The Change Import Status window opens. Select the spreadsheet System ID.
  2. Select the Import radio button and then select the OK button. You return to the spreadsheet or Journal Entry Sheet.
Change import status to import
  1. Select the Save button in Excel to save the spreadsheet on your computer.
  2. Select the Home icon button to return to the Spreadsheet Journal Import control page.
select save
  1. From the Spreadsheet Journal Import Control page, select the Write to File button.
  2. 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.
Select the Write to File button
Import Spreadsheet Text File (Write to File) in ctcLink

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

  1. The Spreadsheet Journal Import run control ID search page displays.
  2. If you have run this process or report in the past, select the Find an Existing Value tab to enter an existing Run Control ID and select the Search button. If this is the first time running this process or report, select the Add a New Value tab to create a new Run Control ID and select the Add button. 
    • NOTE: It is important to note that Run Control IDs cannot be deleted. Do not include spaces in your Run Control. We encourage the Run Control ID to have the same process naming convention but unique to the step; because of this, including your institution code and a short process description in the Run Control ID is recommended--e.g., WA020_JRNL_IMPORT (College, Process).
  3. The Spreadsheet Journal Import Request page displays.
  4. 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 (Optional to select 'Update' to reload a non-posted Journal)
      • If Journal is Invalid
    2. In the Journal Processing Options section, select the Edit Journal(s) and Budget Check.
  5. Select the Add button.

If you are re-using a Run Control ID, you will have to select the Delete button to delete the current file before adding a new file.

Spreadsheet import add
  1. Navigate to the text file located on your computer and upload in order to attach; select Choose File button, select TXT file document from computer, select Open, and then select File Attachment Upload.
select file from computer
  1. After the file is attached, mark the Edit Journal(s) checkbox and select the Run button.
select run
  1. The Process Scheduler Request page displays. Select the Journal Import with Edit and select the OK button to run this process.
Select Journal Import with Edit and select Run
  1. From the Spreadsheet Journal Import Request page, select Process Monitor link. Select the Refresh button until the process is complete. Please refer to the Process Schedule Request steps for further instructions.
  2. When the process is finished, select the Details link.

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

Process monitor
  1. The Process Detail window opens. Select the View Log/Trace link to view errors. Select the GL_EXCL_JRNL link to view errors.

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

log trace results
  1. Select the Message Log link. Verify that the process completed and that the journal imported successfully.
message log
Review Journal Imported Spreadsheet(s)

Finally, navigate to the Create/Update Journal Entries page to view your journal, run the Edit Journal Process, and Submit for Approval (or Post).

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

  1. Use the Find an existing Value tab on the Create/Update Journal Entries. Select the Clear button to clear defaults and/or enter values to locate your new journal:
    1. Enter or lookup Business Unit.
    2. Optional to use the Journal Date or other search criteria to refine results.
    3. Enter ‘EXT’ in the Source field.
  2. Select the Search button. Select your journal from results.
search for journal
  1. The journal displays. Review the Header tab and then select the Lines tab.

Optional to select the Attachments link to add backup documentation and/or update the Long Description. Select Save to save changes.

Header tab
  1. The next steps are to Run the Edit Journal Process and Submit Journal for approval. Please refer to QRG Create, Edit Journal, and Submit a Manual Journal Entry for assistance.
Journal Lines tab

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.

Video Tutorial via Panopto

View the link to Uploading a Journal Spreadsheet as a Text File (Write to File). This link will open in a new tab/window.

0 Comments

Add your comment

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