Batch Journal Entry File Import and Data Selection Page

The Import item on the Batch Journal Entries page's Action Bar allows you to load batch journal entries from files stored on either your personal computer or network server.

Menu Path

From the Fund Accounting menu, select Entry and Processing. From the General Ledger menu, select Batch Journal Entries. From the action bar menu, select Import.

Import Wizard

The Import item launches a wizard that takes you step-by-step through:

• Loading the file.
• Mapping the columns on the file to the fields in the database table.
• Mapping static columns that are required by the Batch Journal Entry option, but not defined in the file.
• Saving the mapping to ensure that it can be used for future file imports.
• Generating a list of warnings and error messages encountered during the data validation. These are the same messages a user receives when manually adding a batch journal entry record.

File Requirements

Your ability to import a file depends on the following:

• You must have the proper security permission to access the Import item.
• The data in the file's fields must comply with the field requirements in journal entry records. For example, if a field requires numeric data and the file in the external file contains characters, the system cannot import the file.
• The file must be formatted as XML Spreadsheet 2003, Comma Separated (CSV), Tab-Delimited (TDF/TXT) or Pipe-Delimited ( | ).
• The extension of the file to be imported must be *.xml, *.exml, *.csv, *.txt, or *.unl.

Creating a File for Import

Create the file to be imported into batch journal entries in any manner you choose. The easiest way is to use a spreadsheet program. The file can contain one or more batches of journal entries. The database table used to store batch journal entries is djournal.
The file created can contain any of the following fields in any order. Once the file is created, it must be saved with the extension *xml, *exml, *.csv, *.txt, or *.unl. Fields marked Required must be included in the file or entered in the Static Column Value Mapping page of the wizard.

Fields

• djournal.je_number (Journal Entry Number) - Required
  If your Fund Accounting Profile is set to Auto Assign Journal Entry Numbers, set this field to AUTO or do not include this field in your file. This field will default to AUTO in the Static Column Value Mapping page.
  

  

  When importing multiple journal entries with the same control number, the actual journal entry number must be included in the file. AUTO cannot be used in this case, as it will assign all records with this control number to the same journal entry number.
• djournal.description (Description)
  When the journal entry description is in the file, all lines for a journal entry must have the same description for the file to be imported.
• djournal.item_desc (Line Item Description)
  When the line item description is in the file, each individual line of the journal entry may contain different line item descriptions.
• djournal.key_orgn (Budget Unit) - Required
• djournal.account (Account) - Required
• djournal.project (Project)
• djournal.proj_acct (Project Account)
• djournal.debit_amt (Debit Amount) - Required
• djournal.credit_amt (Credit Amount) - Required

• djournal.batch (Control Number) - Required
  

  

  If you are importing multiple journal entries that should have different control numbers, the control numbers must be in the file. When the control number is set on the Static Column Value Mapping page, all journal entries will be linked to the same control number.
• djournal.yr (Fiscal Year) - Required
  When the fiscal year is in the file to be imported, the same fiscal year must be used for all journal entries in the file. If the fiscal year is not in the file, the fiscal year will default on the Static Column Value Mapping page. If you are in the Batch Journal Entries option, the default is from the Year field on the Fund Accounting Profile. If you are in the Batch Last Year Journal Entries option, the default will be the Year field on the Fund Accounting Profile minus 1. The fiscal year field cannot be changed in the Static Column Value Mapping page.
• djournal.period (Period) - Required
  If importing multiple journal entries in the same file that will be in different periods, the period must be in the file. When the period is in the file to be imported, the same period must be used for each line item of an individual journal entry. If the period is not in the file, the period will default on the Static Column Value Mapping page. If you are in the Batch Journal Entries option, the default is from the Period field on the Fund Accounting Profile. This can be changed to any period 1 through 13. If you are in the Batch Last Year Journal Entries option, the default period is 13 and cannot be changed.

22.4 and later.

If the Full Account Format option is enabled in the Fund Accounting Profile, then:

• The import file must include the account number as defined in the translation table. The account number field will replace the djournal.key_orgn and djournal.account fields.
• The account number is mapped to the Account Number field. The label for the Account Number field is pulled from the Fund Accounting profile.
• If the Use Fund Only option is enabled in the Fund Accounting Profile, the total debit amount must equal the total credit amount by fund.
• If the Use Fund Only option is not enabled in the Fund Accounting Profile and additional level for balancing is not null, the total debit amount must equal the total credit amount by fund and the additional level.

Control Buttons

The Import Wizard displays the following controls for navigating from page to page:

• Next - advances you to the next page.
• Previous - returns you to the previous page.
• Cancel - returns you to the Batch Journal Entries page.

Importing a File into Batch Journal Entries

1. Click Browse in the File to Import field to locate the file that you want to import from your personal computer or network server.
2. Once the file is located, select the file, and click Open to display the file in the File to Import field.
3. Click Load to load the data from the file into the Import File Data section.
4. In the Import File Data section, select the line that will be used for the Column Titles, and then click the following:
   • Set Column Titles - to display the data selected in the column headings of the Import File Data section.
   • Reset Column Titles - to put the line back into the list section, if you selected the wrong line. After clicking this button, select the correct line and then click Set Column Titles again.
5. Once the column titles are set, select the lines of the file to be imported.
   
   • To select a range of consecutive lines, select the first line, hold down the <Shift> key, click the last line, and then click Select All.
   • To select lines individually, hold down the <Ctrl> key, click each line, and then click Select All.
     Note that selecting blank rows prevents the import process from completing. To remove a blank row, hold down <Ctrl>, and then click on the row.
6. When the lines have been selected, click Next.
7. In the Define Column Mapping page:
   
   • Select a mapping in the Saved Import Mapping field.
     or
   • Tab into the Table Field column for the first Import File Column to map the file column to the appropriate field in the database table.
     The Field Description column will default to a user friendly name and cannot be changed.
8. After mapping the columns in the files to the fields in the database table, click Next.
   • If there are errors where the data in the file does not match the requirements of the database table, the Import Errors page displays. You cannot proceed until the file or the mapping is corrected.
   • If there are no errors with the requirements between the data in the file and the database table, proceed to Step 11.
9. In the Static Column Value Mapping page:
   
   • Required fields from the database table that are not mapped on the Define Column Mapping page will display.
   • Non-required fields that are in the database table and not in the file can be selected by clicking Append Row. When the djournal.year (Fiscal Year) and djournal.period (Period) are mapped statically, their values will default.
     Values entered in the Static Column Value Mapping page will be used for every line number in the file being imported. For details, refer above to the Creating a File for Import section.
10. Once the static mapping is complete, click Next.
11. In the Save Import Mapping page:
    • To identify the mapping you defined, assign a unique name.
    • To allow the mapping to be used by anyone who can import a file, select Public. If needed, users will be able to modify the mapping.
    • To limit the use of the mapping to yourself, select Restricted. With this setting, only the owner can update the saved mapping.
12. Click Finish to exit the Import Wizard.
13. If there are any errors found during the data validation of the file being imported, the Import Status page displays error and warning messages:
    
    • The Line # column represents the line number in the actual file to help you find the data that needs to be corrected before it can be imported.
    • The Summary Information section displays the number of Errors, Warnings, and Successes. If there are no Successes, the Import item will not appear on the page.
    • In the Display Results field, you can choose to display Warnings Only, Errors Only, Warnings/Errors Only, or Show All.
    • You can print the listing of errors by clicking Print.

    You can choose to import any successes by clicking Import, or you can choose to correct the file and run the import process again. To exit without importing any records, click Back.
    

    

    A line in the file that gives an Error will prevent all lines attached to the same journal entry number from being imported into the Batch Journal Entries option. You will be able to import lines that are a success and contain only warning messages.
14. If no errors are found in the data validation or if the Import item is selected in the Import Status page, a confirmation dialog displays.
    Click Yes to import the file and display a final confirmation dialog, which lists the journal entries imported into Batch Journal Entries. Click OK to complete the process.
    The journal entry will now display in the Batch Journal Entry page with its Status set to Complete.

Fields

File Import and Data Selection Page

This page allows users to select the file and records to import into Batch Journal Entries.

Define Column Mapping Page

This page allows users to map the columns in the file to be imported with the fields in database table.

Import Errors Page

This page generates a list of errors where the data in the file does not match the requirements of the fields in the database table.

Static Column Value Mapping Page

This page allows users to statically map required fields in the database table to a value. This value is used for all lines of the file to be imported.

Save Import Mapping Page

This page allows users to save the mapping created in the Define Column Mapping and Static Column Value Mapping pages.

Import Status Page

This page allows users to see error and warning messages generated during the data validation of the Import to Batch Journal Entries.