File Import and Data Selection Page

On this page:

The Batch Budget Adjustments page's Import item enables you to load batch budget adjustments from files stored either on your personal computer or network server.

Menu Path:  Fund Accounting > Entry & Processing > Budget Ledgers > Batch Budget Adjustments > click Import on the Action Bar

Import Wizard

The Import item launches the Import Wizard, which 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 Budget Adjustment option, but not defined in the file.
• Saving the mapping that was created to be used for future file imports.
• Viewing a list of warnings and error messages generated during the data validation. These are the same messages a user receives when manually adding batch budget adjustment records.

File Requirements

Your ability to import a file depends on the following:

• You must have the proper security resource to access the Import item.
• The data in the file's fields must comply with the field requirements in budget adjustment records. For example, if a field requires numeric data and the field 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 on the file to be imported must be *.xml, *.exml, *.csv, *.txt, or *.unl.

Creating a File for Import

Users can create the file to be imported into batch budget adjustments in any manner they choose. The easiest way is to use a spreadsheet program. The file can contain one or more batches of budget adjustments. The database table used to store batch budget adjustment is dexpledgr.

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.

• dexpledgr.batch (Control Number) – Required
  Required, must be included in the file or entered in the Static Column Value Mapping page of the wizard.

Note

If importing multiple budget adjustments 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 budget adjustments will be linked to the same control number.

• dexpledgr.key_orgn (*Budget Unit*) - Required
• dexpledgr.account (Account) – Required
• dexpledgr.amount (Adjustment Amount) – Required
• dexpledgr.yr (Fiscal Year) – Required
  If the fiscal year is not in the file, the fiscal year will default on the Static Column Value Mapping page. The default is from the Year field on the Fund Accounting Profile. This field can be modified to the prior year if necessary.
  If importing multiple adjustments spanning multiple years, then this field should be included in the file, otherwise it can be entered in the Static Column Value Mapping page of the wizard and that value will be used for all adjustments.
• dexpledgr.period (Period) – Required
  If the period is not in the file, the period will default on the Static Column Value Mapping page. The default is from the Period field on the Fund Accounting Profile. This field can be modified to another period if necessary. 
  If importing multiple adjustments spanning multiple periods, then this field should be included in the file, otherwise it can be entered in the Static Column Value Mapping page of the wizard and that value will be used for all adjustments.
• dexpledgr.app_group (*Approval*)
  Required, if Budget Transfers/Adjustment Approvals field on the Fund Accounting Profile is selected.
  If importing multiple adjustments with different approval groups, then this field should be included in the file, otherwise it can be entered in the Static Column Value Mapping page of the wizard and that value will be used for all adjustments.

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 Budget Adjustments page.

Importing batch budget adjustment records

1. Select Fund Accounting > Entry & Processing > Budget Ledgers > Batch Budget Adjustments to display the Batch Budget Adjustments page.
2. Click Import to display the File Import and Data Selection page.
3. Click Browse... in the File to Import field to locate the file that you want to import from your personal computer or network server.
4. Once the file is located, select the file, and click Open to display the file in the File to Import field.
5. Click Load to load the data from the file into the Import File Data section of the File Import and Data Selection page.
6. 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.
7. 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, and then click the last line.
   • To select lines individually, hold down the <Ctrl> key, and then click each line.
   • To select all of the lines from the import, click Select All.
   Note that selecting blank rows will prevent the import process from completing. To remove a blank row, hold down <Ctrl>, and then click on the row.
8. When the lines have been selected, click Next.
9. In the Define Column Mapping page:
   
   • Select a mapping from the drop down 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.
10. After mapping the columns in the file to the fields in the database table, click Next.
    • If the data in the file does not meet the requirements of the database table, the Import Errors page displays. You cannot proceed until the file or 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.
11. 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 dexpledgr.yr (Fiscal Year) and dexpledgr.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.
12. Once the static mapping is complete, click Next.
13. 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, which allows users to modify the mapping if needed.
    • To limit use of the mapping to yourself, select Restricted, which allows only the owner to update the saved mapping.
    Note
    The ability to add and update an import mapping on the Save Import Mapping page is dependent on security, in addition to the Restricted field.
14. Click Finish to exit the Import Wizard.
15. If there are any errors found during 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 through the import process again. To exit without importing any records, click Back.
16. 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.
17. A final confirmation dialog displays, listing the batch adjustments imported into Batch Adjustments. Click OK to complete the process.
    The budget adjustment will now display in the Batch Budget Adjustments page. The budget adjustment's Hold Status will be set to On Hold.

Fields

File Import and Data Selection Page

This page allows users to select the file and records to import into Batch Budget Adjustments.

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 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 Budget Adjustments.