Beginning Monday, November 6, 2023, departments must adopt the new General Ledger (GL) Journal Spreadsheet Uploader files attached to the bottom of this solution article, and follow the steps as outlined below. Note that previous versions downloaded and submitted after 12 p.m. on Friday, November 3, 2023 are invalid and will result in an error. For a quick look at the differences between the current GL Spreadsheet Uploader and the previous version, please see our Highlighted Changes in the General Ledger Spreadsheet Uploader - November 2023 solution article.


This solution article provides the procedure to enter GL journal entries in an Excel spreadsheet template and import the journal data into SF Financials.  

Why use the spreadsheet journal import template?

  1. Entering journal data is easier in an excel template compared to entering directly in SF Financials.
  2. Journal data can be copied and pasted to/from other excel spreadsheets. 
  3. Journal data can be saved, replicated, and updated for future journal entries.
  4. One or more journals can be imported with a single click from Excel to SF Financials.

The key topics involved to create and import journal(s) using the spreadsheet journal template:

  • Topic 1: Download the New SF Financials GL Journal Import Spreadsheet files - One Time Download
  • Topic 2: Setup your General Setup and Default Configurations - One Time Setup 
  • Topic 3: Create a New Journal Sheet 
  • Topic 4: Enter Journal Header Information in Journal Sheet 
  • Topic 5: Enter Journal Line Information in Journal Sheet 
  • Topic 6: Import the Journal(s) Directly via Import Now or Write to File (flat file) 
  • Topic 7: Login to SF Financials and Validate that the Journal(s) was Imported 
  • Topic 8: Edit (Validate) and Budget Check the Journal(s) in SF Financials



Topic 1: Download the New SF Financials GL Journal Import Spreadsheet Files - One Time Download


Step 1. Download all 4 new spreadsheet journal import files attached at the bottom of this solution article to your local or personal network drive. You must download all 4 new files in order for the new GL Uploader to function properly. Please create a NEW dedicated folder for this (for example, “GL_Uploader_2023”). 


The 4 required files are listed below:

  1. JRNL1_WS.xlsm - This is the journal workbook that you use to create and import journals with Excel 2007 and versions above 
  2. JRNLMCRO_WS.xlam - This is the Visual Basic code library and dialog control used with Excel 2007 and subsequent versions 
  3. JrnlLog.xlsx - Journal log file 
  4. GLLOG.xlt - Message log template


Step 2. Open the JRNL1_WS.xlsm file from your folder. If Excel has a security macro warning, click the Enable Content button. Make the spreadsheet a “Trusted Document” if requested. 


Step 3. Additional Macro Error Resolution: https://support.microsoft.com/en-us/topic/a-potentially-dangerous-macro-has-been-blocked-0952faa0-37e7-4316-b61d-5b5ed6024216

 
Follow these steps for each of the 4 files if there still remains a Macro issue after Step 2. The steps are referenced in the Microsoft link above:

1. Right click on the file and choose Properties from the context menu.

2. At the bottom of the General tab, select the Unblock checkbox and click OK.

3. If you don't see the Unblock checkbox in properties, then

a. The file has already been unblocked, or 

b. Follow the additional steps documented by Microsoft in the link above, or

c. Contact the SF Employee Portal Support Team.

Step 3a. For additional troubleshooting steps for the initial setup of the GL Spreadsheet Uploader, please refer to solution article "Initial Setup: Troubleshooting General Ledger Spreadsheet Uploader" 

Step 4. Beginning November 6, 2023, the new GL uploader and associated files you downloaded from the bottom of this solution article replace the previous GL journal import and associated files. Please delete your previous uploader files with the green background, as they will no longer work with the current upgraded version of SF Financials. You may want to consider saving (or copying and pasting) your previous journal data.


Step 5. You have completed the one time process for downloading the new GL uploader and associated files that departments must adopt beginning Monday, November 6.



Topic 2: Set Up your General Setup and Default Configuration - One Time Setup

You will need to configure your journal entry spreadsheet prior to your first use.


Step 1. Under General Section, click Setup and Defaults. 


Step 2. Under the Header Defaults section, enter the Business Unit, Journal Date, Ledger Group, and Source. The Journal Date in the sheet’s journal header will override the date you enter here.



Step 3. Ensure that the fields reflect the following values in the section under Online Import Control:

  • Address = Address should reflect: https://con-fin.sfgov.org/ 
  • Database = Database field should reflect 'fsprd'. If you see a different value, change it to 'fsprd'.
  • Skip if Journal already exists = ensure this checkbox is selected.


Step 4. Leave all other fields as default. Click OK to save. 


Step 5. Important: We recommend that you keep the defaulted column order. Please do NOT Configure, or re-arrange the column order as doing so may disable the macro, cause errors, and/or result in the chartfields not mapping correctly to SF Financials.

Note that the Edit Journals and Submit Journals for Approval checkboxes are currently disabled in this version. Therefore, the journal will need to be Edited, Budget Checked, and Submitted for Approval in SF Financials after the import. 


Step 6. You have completed the one time process for setting up your general setup and configuration of your journal entry spreadsheet prior to your first use.



Topic 3: Create a New Journal Sheet 


After completing your initial setup of the journal spreadsheet, you are ready to create journals in Excel.

 

Step 1. The Journal Sheets area is used to create and maintain journal entries. The buttons in this area work as noted:

  • New Sheet - creates a new journal spreadsheet
  • Edit Sheet  - modifies existing journal spreadsheet 
  • Delete Sheet - deletes existing journal spreadsheet 
  • Copy Sheet - copies existing journal spreadsheet to a new sheet (with a different sheet name) for modification and/or import 


Step 2. Click the New Sheet button. The New Journal Sheet dialog box appears. Enter the name for this journal sheet and click OK. Follow Excel formatting requirements, so do not use special characters in the journal sheet name.



Step 3. You have successfully learned how to create a new journal sheet.



Topic 4: Enter Journal Header Information in Journal Sheet

After creating and naming a new journal sheet, the Journal Entry sheet will open. This is where you will input your journal entries. 


Step 1. There are two areas on the spreadsheet, the Journal Header area and the Journal Lines area. It’s much like the journal creation in SF Financials, where you create the header before you create the journal lines tab.


Step 2. The first item to create for each journal is the header. Press the + (plus sign) in the journal header area to add a new header. The new Journal Header text box appears.

New Feature: Source – New feature as of November 2023, Journal Source header is visible on Journal Header.



Step 3. Enter the journal header information. This is similar when you create an online journal entry. You must enter business unit in the Unit field, Journal Date, and Ledger Group.


Step 4. You do not need to enter a journal ID if you use NEXT. Using NEXT will default the next (system generated) journal ID available in SF Financials at the time you import your journal.


Step 5. When you press OK, the GL Spreadsheet Uploader will prompt you: “Sync Template For This Session?” Click Yes.



Step 6. Enter your credentials after clicking Yes to Sync. Click OK.



Step 7. You will receive a confirmation message that the Template Synced.




Step 8. The different buttons associated with the Journal Header are detailed below.


BUTTON FUNCTION
Creates new a new Journal Header with a unique Sys ID (Sys ID is a system generated sequential number that is unique to each journal created across all journal sheets within a spreadsheet journal workbook file.)
Deletes the Journal Header and associated Journal Lines
Edits the Journal Header fields
Copies the Journal Header and its associated Journal Lines to a new Journal in the spreadsheet template
Selects the Journal Header for the Journal Lines on which you are working
Changes the import status of a Journal - leave at default



Step 11. When you've completed the Journal Header information, click OK.



Topic 5: Enter Journal Line Information in Journal Sheet

After creating a Journal Header, you are now ready to enter journal detail lines.


Step 1. Below is an example of a 4-line journal.


The Journal Lines area has two columns labeled Alt Account and Speed Type that are locked and cannot be moved. 

Important: There is now a space between the Account and Fund ChartField columns. If you have a template different from the GL Spreadsheet Uploader, you need to alter it when copying and pasting into the uploader template.


Step 2. The different buttons associated with the Journal Lines are detailed below.


BUTTON FUNCTION
Adds one Journal Line row with a matching Sys ID for the Journal Header that's displayed. Sys ID is a system generated sequential number that is unique to each journal created across all journal sheets within a spreadsheet journal workbook file.
Position your cursor on the row you wish to delete. The button deletes the Journal Line row based on the position of your cursor
Selects the Journal Header for the Journal Lines on which you are working
Adds a block of Journal Lines based on the value you enter in the prompt box. For example, entering '4', will insert four lines
Deletes a block of Journal Lines based on the values in the Journal Line column (from Line X to Line Y)
Check the Amount field with the number of decimal points that you have set up. The default number of decimal points is 2.


Step 3. Enter or copy and paste your journal entries in each row. Please ensure that the column headers correspond to the correct chartfields and data.


Step 4. Enter Debits (DR) as positive numbers and Credits (CR) as negative numbers under amount.


Step 5. Save your spreadsheet when you complete your entry.


Step 6. Name the workbook and save it to your local drive or your network folder.


Step 7. Note that the spreadsheet does not validate chartfields. Validation of chartfields occurs at the time of import.


A note about the column order: In this new spreadsheet, columns N - Fund to Y - Jrnl Line Ref remain in the same order as in the previous version of the Journal Sheet. However, column L - Alt Account and column M - Speed Type, are locked between column K - ChartFields Account and column N - Fund. Please be aware of the two new columns when you populate the columns between Account and Fund ChartFields.



Topic 6: Import the Journal(s) via Import Now or Write to File ("Flat File")

After entering the journal entry information, you can now import the Journal. There are two ways to import the Journal:


  • Option 1 - Import Now Online Mode: Data is sent as XML documents and immediately imported into SF Financials.
  • Option 2 - Write to File Batch Mode: Creates a flat file (txt format) and runs a batch import process (Run Control) in SF Financials to import one or more journal files. Use this option if you experience issues with the Import Now online option. You can also use Write to File to import large volume journal lines.


Option 1 - Import Now Online Mode

Step 1. Log into SF Financials to use the Import Now online mode to import the journal entries you created. If your Active Directory (AD) or network login are different from your SF Financials credentials, you will not be able import the journal entries you created. Load the journal directly in SF Financials from the Journal Line page or the homepage.

From the Journal Line Page

Uploading from the Journal Line page will upload on the selected journal on the sheet.



From Homepage

If you access Import Now from the homepage, you can select one or more journal sheets to import.



Step 2. Enter your SF Financials user ID and Password. Click OK.


Step 3. SF Financials will load your journal and display a message box with the status of the journal (fail, success, etc.). In addition, a JrnlLog spreadsheet will open automatically if you enabled this option to show the status of the import.


Step 4. By default, the "Skip if journal already exists" checkbox in Setup & Defaults is selected. Therefore, a journal that already exists will be skipped and will not be imported during the process. If you wish to override a journal, e.g., to update the amounts, departments, etc., make sure you deselect the “Skip if journal already exists” checkbox in Setup & Defaults. Doing so will allow you to replace the journal previously imported. You can override a previously imported journal as long as it hasn’t been posted.


Step 5. Review the log for success or fail with errors on the log. The errors that will prevent a journal from importing may include:

• Invalid ChartFields - account, business unit, ledger, fiscal year and period 

• Invalid journal source on the header


Step 6. Correct any errors and reimport the journal. Once your journal is successfully loaded into SF Financials, you must then log into SF Financials  to edit (validate), budget check, and submit the journal for approval.


Step 7. File attachments/supporting documents for your journal - If you have attachments for your journal, open the journal from SF Financials and attach them at this time. You cannot import attachments from the Excel journal uploader.

Two ways to add attachments to the journal in SF Financials:

1. Attach on the Journal itself

2. Use the Multiple Journal Attachments Page, if you have multiple journals to which you need to attach documents.


Option 2 - Write to File Batch Mode

Step 1. Click the Write to File button from the homepage.


Step 2. Select the sheet(s) to import.


Update/override the file name and file location. Make sure the file name contains a .txt extension. Remember this location and file name. You will need this information to attach and import the file into SF Financials. We recommend you change the file name for subsequent flat files so you do not overwrite previous files.


Click the OK button.




Topic 7: Login to SF Financials and Validate that the Journal Import

This topic provides instructions on how to import the txt file from the Write to File option for importing a Journal. Skip this topic and proceed to Topic 8 if you imported using Import Now.


Step 1. From your Financials homepage, click the General Ledger tile, and from General Ledger Navigation Collection (Nav Collection), navigate to:

Journal Transactions > Spreadsheet Journal Import




Step 2. Add a new Run Control ID or open your existing Run Control ID. Leave the default parameters as shown below. Note that the “Edit Journal(s)” Journal Processing Option will NOT do a budget check. Use the separate edit process to do the edit and budget check.



Step 3. Click Add to attach the flat file (txt) that the spreadsheet template’s Write to File generated.


Step 4. If there is an existing file attached, delete the existing file before attaching the new file.


Step 5. Retrieve the txt file from the location you noted in Topic 6, Option 2, Step 2. Select the txt file and click the Upload button.



Step 6. Verify that the file is attached and click RUN.


Step 7. The Process Scheduler Request screen will open. Make sure the checkbox for the Application Engine (app engine) is selected.



Step 8. Click OK to run. Note that the app engine process is new in this updated process.


Step 9. Go to the Process Monitor to check its status.


Step 10. After the app engine process runs successfully, verify the successful import of the journal by going to Create/Update Journals under your General Ledger Nav Collection.


Step 11. File attachments/supporting documents for your journal - If you have attachments for your journal, open the journal from SF Financials and attach them at this time. You cannot import attachments from the Excel journal uploader.

Two ways to add attachments to the journal in SF Financials:

1. Attach on the Journal itself

2. Use the Multiple Journal Attachments Page, if you have multiple journals to which you need to attach documents.



Topic 8: Edit and Budget Check the Journal in SF Financials

After validating your journal's successful import into SF Financials (Create/Update Journal Entries), Edit and Budget Check your journal by using one of the two options below.

  • Option 1: Edit and Budget Check in Journal Lines Page
  • Option 2: Edit and Budget Check using Process Journals (recommended for high volume journal lines)


Option 1 - Edit and Budget Check in Journal Lines Page

Step 1. Go to the Lines tab of your Journal Lines page.


Step 2. Select Edit Journal from the Process drop-down and click the Process button.


Step 3. After the process runs successfully, the status should change to 'V' (valid) for both Journal Status and Budget Status.



Step 4. If a status of 'E' (error) appears, investigate and make the necessary updates to resolve the error.


Step 5. Re-edit to validate again.


Step 6. Submit the journal for approval.


Option 2 - Edit and Budget Check using Process Journals (recommended for high volume journal lines)

Step 1. Access your General Ledger Nav Collect and go to Edit Journals to create a new Run Control ID or open an existing Run Control ID.


Step 2. Update the journal parameters as indicated in the highlighted areas below. Make sure the Budget Check box is checked to perform both Edit and Budget Check. 

Important: Make sure to enter journal IDs for your own journals only. Other fields are optional.

Step 3. 

Step 3. Save your run control.


Step 4. Click RUN.


Step 5. Check and Refresh the Process Monitor until the process is complete.



Step 6. Verify the status of the journal as Valid for both Journal Status and Budget Status.

 

Step 7. Go to the Lines tab of your Journal Lines page to see if your Journal Status and Budget Status are valid or contains errors.




Step 8. If a status of 'E' (error) appears, investigate and make the necessary updates to resolve the error. Run the Edit process again.


Step 9. If the Journal Status and Budget Status are Valid, Submit the journal for approval.



You have the reached the end of Journal Entry and Uploader process.



Related Solution Articles:



Questions?

Please reach out to the SF Employee Portal Support Team if you have questions regarding the Journal Entry and Uploader process.