Importing Batch Budget Transfer Records

The Batch Budget Transfer page's Import item displays the File Import and Data Selection Page. This page enables you to load batch budget transfers from files stored either on your personal computer or network server.

Menu Path: Fund Accounting > Entry & Processing > Budget Ledgers > Batch Budget Transfer> 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 Transfer option, but not defined in the file.
• Saving the mapping that was created to be used for future file imports.
• Generating a list of warnings and/or error messages found during the data validation. These are the same messages a user will receive if manually adding a batch budget transfer record.

File Requirements

Your ability to import a file depends on the following:

• The data in the file's fields must comply with the field requirements in budget transfer 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.
• You have the appropriate security resource to access the Import item.

Creating a File for Import

Users can create the file to be imported into batch budget transfers in any manner they choose. The easiest way is to use a spreadsheet program. The file can contain one or more batches of budget transfers. The database table used to store batch budget transfers is dexpledgr. The file created can contain any of the following fields in any order. Once the file is created, it needs to be saved with the extension of *.xml, *.exml, *.csv, *.txt, or *.unl.

• dexpledgr.trn_no (Transfer Number) - Required
  Required, must be included in the file or entered in the Static Column Value Mapping page of the wizard. If your organization is set to Auto Assign Budget Transfer Numbers, set this field to "AUTO" in the file 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 budget transfers with the same control number, the actual transfer number must be included in the file. "AUTO" cannot be used in this situation. "AUTO" would assign all records in the control number to the same transfer number.

• dexpledgr.hdr_desc (Budget Transfer Description)
  When the budget transfer description is in the file, all lines for a budget transfer must have the same description for the file to be imported.
• dexpledgr.description (Description)
  This is the description of each individual line of a budget transfer. When the line item description is in the file, each individual line of the budget transfer may contain different line item descriptions.
• dexpledgr.key_orgn (*Budget Unit*) - Required
• dexpledgr.account (Account) - Required
• dexpledgr.amount (Adjustment Amount) - Required
  A negative amount indicates that this will be the Transfer From account.
  A positive amount indicates that this will be the Transfer To account.

• dexpledgr.batch (Control Number) - Required

  

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

• 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 transfers 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 transfers.
• 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 transfers 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 transfers.
• dexpledgr.app_group (*Approval*) - Required
  Required, if Budget Transfers/Adjustment Approvals field on the Fund Accounting Profile is selected. If importing multiple transfers 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 transfers.

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 Transfer page.

Importing Batch Budget Transfer Records

1. Select Fund Accounting > Entry & Processing > Budget Ledgers > Batch Budget Transfer to display the Batch Budget Transfer 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 have the file appear 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. 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.
   
   • 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 lines, 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 all 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. In addition, non-required fields that are in the database table and not in the file can be selected using the Append Row button. When the dexpledgr.yr (Fiscal Year) and dexpledgr.period (Period) are mapped statically, the values will default. For details, refer to the Creating a File for Import section. Values entered in the Static Column Value Mapping page will be used for every line number in the file being imported.
12. Once the static mapping is complete, click Next to display the Save Import Mapping page.
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.

    
    

    

    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 budget transfers imported into Batch Budget Transfers. Click OK to complete the process.
    The budget transfers will now display in the Batch Budget Transfers page. The budget transfer's Hold Status will be set to On Hold.

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.

Fields

File Import and Data Selection Page

This page allows users to select the file and records to import into Batch Budget Transfer. Also initiates the Import Wizard.

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