Downloading and Using the Journal Import Tool

This job aid contains instructions on how to download the Journal Import tools for General Journals and Internal Transfers, creating journals in the import tools, and importing journals into ARC.

Last Revision: December 2021

For more information on the process of journal entry creation in ARC please refer to this training guide: Creating and Approving Internal Transfer Journal Entries in ARC 

Details

  1. Create a new folder on your workstation. Give the folder a recognizable name, such as “ITF Journal Uploads” or “General Journal Uploads”. 
  2. Log in to https://my.columbia.edu/, click ARC, and scroll down to the Resources section. Click the Journal Entry Spreadsheet Upload tab. 
Click the Journal Entry Spreadsheet Upload tab.
  1. Right click on the Macro and Journal Import links and select Save link as… from the menu. 
  2. Save each file in the folder you created. Both the JRNLMCRO_WS.xlam and the JRNL1_WS_ITEF.xlsm (internal transfers) or the JRNLMCRO_WS.xlam and the JRNL1_WS_JE.xlsm (General Journal) files must be saved in the same folder. 

    If you are using both ITF and General Journal upload templates, the files for each must be stored in separate, respective folders. 

After download, the folder should contain the following two files for Internal Transfers: 

After download, the folder should contain the following two files for Internal Transfers

Or, the following two files for General Journals: 

Or, the following two files for General Journals
  1. Open the folder and double click on JRNL1_WS_ITEF file to open the Journal Import Tool for Internal Transfer Journals or JRNL1_WS_JE file to open the Journal Import Tool for General Journals.

    Note: The first time you open these files you will be prompted to Enable Editing and to Enable Content. Click those buttons on the
    top of the spreadsheet. 
     
  2. From the Home screen click the New button. 
From the Home screen click the New button.
  1. Type a New Journal Sheet Name and click OK. 
  2. In the new Journal Sheet, click the + button in the Journal Header Section. 
In the new Journal Sheet, click the + button in the Journal Header Section.

The New Journal Header window appears. 

The New Journal Header window appears.
  1. Enter a journal Description and click OK. You can edit Business Unit, Journal Date, Ledger Group, if required. 
  2. Click the Add Line or Add Lines buttons to add Journal Lines to the journal. If you clicked the Add Lines button you will be prompted to Insert the Number of Lines. 
Click the Add Line or Add Lines buttons to add Journal Lines to the journal.
If you clicked the Add Lines button you will be prompted to Insert the Number of Lines.
  1. Enter ChartField values into the journal rows. This can be achieved either through copying and pasting or manually typing in the values. 
Enter ChartField values into the journal rows.

Note: When copying and pasting ChartStrings from another Excel file, make sure each column in your source spreadsheet is aligned with the journal template columns and that the values are pasted as values. 

Make sure the ChartFields Deptid, Activity, Initiative, Segment, Sit, Fund, and Function are formatted as text in your source so that you can copy and paste the leading zeroes into the template. Changing the formats of the cells in the template will result in errors when you import the journals. 

  1. Click the Write File button on the Journal Sheet or, click the Write to File button the on the Home Page. 
Click the Write File button on the Journal Sheet
Click the Write to File button the on the Home Page.

The Write Journals to File window appears with the Journal Sheet Name(s). 

The Write Journals to File window appears with the Journal Sheet Name(s).
  1. Select the Journal(s) for which you want to create an upload file and click Save As. You can use your Shift or Ctrl keys to select multiple journals.

    A Write to File Explorer window appears with the filename defaulted to whatever you named the Journal Sheet, which will be saved in Text (.txt) format. 
A Write to File Explorer window appears with the filename defaulted to whatever you named the Journal Sheet, which will be saved in Text (.txt) format.
  1. Navigate to the Folder where you want to save your file and click Save. 

Note: The last location you selected to save the upload file will become the default folder for the next time you save a Journal file. 

Other Journal Upload Template Functions 

Other Journal Upload Template Functions screenshot.
  • Edit Sheet – edit an existing journal sheet and make changes to it 
  • Delete Sheet – delete any journal sheets that you have previously saved in the template 
  • Copy Sheet – copy a previously saved journal sheet into a new one 
  1. Log into ARC and click the Journals tile and the Upload Journal Entries menu. 
The Journals tile in ARC.
The Upload Journal Entries menu tab.

Or, navigate to NavBar >Columbia Specific > General Ledger > Processes > Upload Journal Entries. The Upload Journal Entries page appears. 

  1. If you are a first time user, click Add a New Value to enter a new Run Control ID and click Add. If you are a returning user, click Find an Existing Value. 
Click Add a New Value to enter a new Run Control ID and click Add.
If you are a returning user, click Find an Existing Value.

Enter your previous Run Control ID and click Search. 

Note: You should not create a new run control for each journal you are uploading. An existing run control can be used indefinitely. 

  1. Click the matching Run Control ID to open the Spreadsheet Journal Import Request page. 
Click the matching Run Control ID to open the Spreadsheet Journal Import Request page.

Note: If you previously uploaded a journal file, you must click the Clear File button to remove it before you can add a new file for upload. 

  1. Click the Add File button. The File Attachment window appears. 
The File Attachment window appears.

If the window does not appear, check that the pop up blocker in the browser is disabled for this site. 

  1. Click Choose File and navigate to the location of your saved upload file. 
Click Choose File and navigate to the location of your saved upload file.
  1. Select the file and click Open. 
  2. Click Upload in the File Attachment window. The file information appears and the Import File button becomes active. 
Click Upload in the File Attachment window. The file information appears and the Import File button becomes active.
  1. Click Import File and click OK in the Message window that appears. 
Click Import File and click OK in the Message window that appears.
  1. Click Check Import Status to refresh the upload status until the process is complete. 

After the process is complete, information from the process message log will appear in the Import Status section. Link(s) to the new journal(s) will appear next to the Check Status button. 

Link(s) to the new journal(s) will appear next to the Check Status button. 

Journal Error Messaging 

If the journal upload process failed, a link to the error log appears next to the Check Import Status button so you can get more information on why the journal failed to import. 

Click the Upload Failed: Go to Log link. 

Click the Upload Failed: Go to Log link.

An error log window appears showing the reason for the import failure. 

An error log window appears showing the reason for the import failure.

If the journal upload process ran successfully but there are other issues with the journal rows, such as invalid or missing ChartField values, the errors will appear in the log messages box. 

If the journal upload process ran successfully but there are other issues with the journal rows, such as invalid or missing ChartField values, the errors will appear in the log messages box. 
  1. Click the link to the journal that appears on the Spreadsheet Journal Import Request Page after a successful upload. The journal appears. 
  2. Run the Edit Process and Submit the journal into workflow. 
The journal appears.

PRINTABLE VERSION

View and download the Downloading and Using the Journal Import Tool job aid.