Uploading Excel Spreadsheet Data

The Budget Administrator center's Upload Budget option enables you to load data from an Excel spreadsheet into Expenditure, Revenue, and Project ledger worksheets.

The upload process involves four primary pages:

Import File	Enables you to upload Excel spreadsheet data from a file on your personal computer or network server. This is the first page to display when you select the Upload Budget option.
Add Spreadsheet Template	Enables you to create a template for importing spreadsheet data. The template tells the system how the spreadsheet is set up so it can "read" its data. To display the Add Spreadsheet Template page, click the Add New on the action bar in the Import File page.
Upload Template	Enables you to review or change a template. To display the page, click the Import File page's Template Details item, after identifying a template in the Template field.
Import Status	Enables you to review any errors detected by the system in the spreadsheet being imported. You must fix any errors reported before you upload the spreadsheet's data.

In addition, the upload involves pages for selecting files and table records. Follow standard procedures in using these pages.

Setting Up Your Spreadsheet

Before uploading data into Budget Preparation, set up your spreadsheet as follows:

Store your data in any rectangular set of cells the spreadsheet from columns A through AZ. Do not leave any blank rows or columns in the import area, and do not include any data that does not apply to Budget Preparation.
In the section being imported, each row must contain data for a valid budget unit/account. If the system detects any errors, the file cannot be uploaded.
Organize the data in the spreadsheet by column, so that each column corresponds to the related column defined in your spreadsheet template. The order of the columns does not matter, since template correlates the columns with the proper fields in Budget Preparation.
Two columns are required in both the spreadsheet and template: Budget Unit and Account. You can include other columns, as long as they correspond with the fields in the following pages, depending on the ledger affected:
Update Expenditure Budget - Administrator
Update Revenue Budget - Administrator
Update Project Budget - Administrator
You do not need to format the spreadsheet's cells, for example, by setting the number of decimal places or defining the cell's width. The system determines the type of data from the template and formats the data accordingly.

Saving Your Spreadsheet

While working on a spreadsheet, you can save it with Excel's standard .xls file extension. However, to upload data into Budget Preparation, you must save the file under one of the following extensions:

File Extension	File Format	File Type
.csv	Comma Delimited	CSV
.txt	Tab Delimited	Text

The file type, which determines the file format, is set in Excel's Save As page. To display the page, select File > Save As from Excel's top menu, and then use the drop-down list in the Save as Type field to select the file type.

If you originally saved your file under an .xls file extension, Excel automatically changes the extension when you select a different file type. For example, if your file is named Budget.xls, it will be renamed Budget.csv, when you select CSV as the file type.

Important

When you save a spreadsheet as a delimited file, Excel eliminates any blank columns or rows both outside and within the spreadsheet's data area. Therefore, you should display the delimited file after creating it and make note of the row numbers where the data begins and ends. These numbers should be entered in the Start Row and Stop Row fields of the Import File page. Also verify that the columns in the file correspond with those in your template.

Setting Up Spreadsheet Templates

A spreadsheet template is needed to tell the system the type of data being imported from Excel, as well as the file format of the spreadsheet.

Creating a spreadsheet template

Select the Upload Budget option in the Budget Administrator center.
In the Import File page, click the ribbon bar's New item.
In the Add Spreadsheet Template page, complete the following fields, all of which are required:

Template Name

Enter a name to identify the template, using up to 25 characters.

Template Type

Select one of the following:

Budget Prep Expenditure/Revenue Ledger

Budget Prep Project Ledger.

These selections designate the Budget Preparation ledgers where the data will be loaded.

File Format

Select one of the following to specify the spreadsheet's file type and extension:

C - Comma Delimited (.cvs file extension)

T - Tab Delimited (.txt file extension)

If you are adding two or more templates, select the Continuous checkbox.
After you complete the Header Information section, you can access the Column Information section. The section's Column Name fields default the following field names. These fields are required and cannot be deleted.
In the Column Number fields, select the spreadsheet columns that correspond to the following default names: Budget Unit and Account Code.
In the Column Name fields, select titles as needed for additional columns as needed, and then select the corresponding spreadsheet column in the adjacent Column Number field
Click OK to save the template.

Importing data from an Excel spreadsheet

Select the Budget Administrator center's Upload Budget option to display the Import File page.
In the Import File page, complete the following fields:

File Location	Select Client PC (default) if the spreadsheet is on your personal computer or Server if it is on your network server.
File Name	Enter the spreadsheet's directory path and file name. If you selected Client PC as the File Location, click the Lookup button to display the Choose the File page. After you find the file, select it, and then click Open to add the file's directory path and name in the File Name field. If you selected the Server, you cannot use the Lookup button. Instead, the system displays a default directory path and file name (uploadbp.txt). You may change the path and name as needed.
Start Row	Enter the number of the row in the spreadsheet containing the first budget entry.
Stop Row	Enter the number of the row in the spreadsheet containing the last entry. Rows from Start through Stop cannot be blank.
Template	Select the template from the drop-down list that contains the format for importing your spreadsheet.

3 In the Save Iteration checkbox:
- Select the field to save your current worksheet data as an iteration.
- Leave the field black if you do not want to save the worksheet data.
  Important
  If you choose the Create New Budget option in the next step, the upload will replace the data currently displayed in your worksheet. If you choose not to save the worksheet data, any additions or changes made since you last saved the worksheet will be lost.
In the Upload Type field, select one of the following:

Create New Budget	Replaces all of the data displayed in your worksheet with data from the spreadsheet.
Update Existing Budget	Updates accounts in your current worksheet with the data uploaded from the spreadsheet. With this setting, the upload updates data in existing accounts and adds data from new accounts.

Click OK to display the Import Status page.
- The Number of Errors field indicates how many rows in your spreadsheet contain errors. Certain errors are acceptable. For example, if rows appear above spreadsheet entries but you identified the proper Start and Stop rows, Line ignored messages display for rows that will be skipped.
- the Number of Successes field indicates how many rows do not contain errors.
In the Display Results field, select one of the following, and then click OK.

Show Errors Only	The lines in the List section will indicate the types of errors found. If no errors are detected, the list section will be blank.
Show All	The list section will display the types of errors found, as well as validation messages for rows in the spreadsheet that do not contain errors.

Click Import. This item only displays if the spreadsheet is error free.
In the Confirmation, click Yes to import the file. Another display appears indicating the import ran successfully.

Fields

Following are descriptions of the fields in the Import File, Upload Template, Add Spreadsheet Template, and Import Status pages.

Field

Description

Import File Page

All of the fields in this window are required.

File Location

Radio buttons for identifying the computer where the spreadsheet you want to import is stored: Client PC (default) or Server.

File Name

Spreadsheet's directory path and file name.

If you selected the Client PC radio button, you can use the Lookup button to search for and select the spreadsheet file in the Choose the File page. When you locate the file, select it, and then click Open.
If you selected the Server radio button, you cannot use the Lookup button to search for a file. In this case, the system displays a default directory path and default file name (uploadbp.txt). You may change this as needed.

Start Row

Number of the first row to be imported, as identified by the line number to the left of column A in the spreadsheet. The default is 1, but be sure to change this if your data begins on another row.

Stop Row

Number of the last row you want to import, as identified by the number to the left of column A. The field's default is 0, but you must change this. The number you enter must be greater than the one in the Start Row field.

Template

Name of the template you want to use for importing the spreadsheet data. Select a template from the field's drop-down list.

Use the action bar's Add New to display the Add Spreadsheet Template page and create a new template. For more information on templates, refer to the previous Setting Up Spreadsheet Templates section, as well as the field descriptions in the following Add Spreadsheet Template Page section.

Save Iteration

Checkbox indicating whether your current budget worksheet data should be saved as an iteration before replacing it with data from the spreadsheet.

Select the field to save the worksheet data as an iteration.
Leave the field blank if you do not want to save the worksheet data.

Important

If you choose the Create New Budget option in the next step, the upload will replace the data currently displayed in your worksheet. If you choose not to save the worksheet data, any additions or changes made since you last saved the worksheet will be lost.

Upload Type

Selection indicating whether you want to replace or update the data stored in your current worksheet. Select one of the following:

Create New Budget	Replaces all of the data in your current worksheet with data from the spreadsheet.
Update Existing Budget	Updates your current worksheet with the data uploaded from the spreadsheet. With this setting, the upload updates data in existing accounts and adds data from new accounts.

Replaces all of the data in your current worksheet with data from the spreadsheet.Create New Budget

Update Existing Budget

Updates your current worksheet with the data uploaded from the spreadsheet. With this setting, the upload updates data in existing accounts and adds data from new accounts.

Add Spreadsheet Template Page

For additional details on these fields, refer to the previous section entitled Create a Spreadsheet Template.

Field

Description

Template Name

Name identifying the template. Character/25

The name you enter will display in the drop-down list of the Import File page’s Template field.
If you are changing a template in the Upload Template page, the Template Name field is display only.

Template Type

Selection identifying the type of ledger where the spreadsheet data will be applied. The field's drop-down selections include:

	Ledger	System
Budget Prep Expenditure/ Revenue Ledger	Expenditure and Revenue Ledgers	Budget Preparation
Budget Prep Project Ledger	Project Ledger	Budget Preparation

File Format

Selection identifying the file type used to save the Excel spreadsheet:

File Types	File Extensions	File Name Examples
C -Comma Delimited	cvs	Budget.cvs
T - Tab Delimited	.txt	Budget.txt

You set the file type and its extension when you save your spreadsheet in Excel using the File > Save As option. Note that before saving the file, you need to change the setting in the Save as Type field in Excel's Save As page.

Column Name

Field that will receive the data from the spreadsheet. Following are the default column names that display when you are adding a template:
Budget Unit
Account
These fields are required and cannot be deleted. The following field names can be added below the defaults:

Requested Base	Approved New	5th Year
Requested New	2nd Year	Freeze
Recommended Base	3rd Year	Budget Unit Title
Recommended New	4th Year	Account Title
Approved Base

Do not duplicate any names.

Column Number

Spreadsheet column that will be the source of the imported data. Columns are identified by the letters at the top of the spreadsheet, for example, B or AC.

The Column Number field's drop-down selections are numbered from 1-A through 26-Z and 27-AA through 52-AZ. As a result, 1-A references the first column in a spreadsheet, while 52-AZ references the 52nd column.

If blank columns precede your spreadsheet data, the columns are eliminated when you save the spreadsheet as a comma-delimited or tab-delimited file.

Import Status Page

Following are descriptions of the field in the Import Status page. This page shows the status of the lines in your spreadsheet in relation to the system's ability to import them.

Field

Description

Number of Errors

Number of lines in the spreadsheet that contain errors. Display only.

Some items included in this count might not be legitimate errors, for example, if a Line ignored condition applies. Legitimate errors are indicated by messages that begin with ERROR.

Number of Successes

Number of lines in the spreadsheet that do not contain errors. Display only.

Display Results

Selection that determines the messages that will display in the Import Status page's list section:

Show Errors Only	The lines in the List section will indicate the types of errors found. If no errors are detected, the list section will be blank.
Show All	The list section will display the types of errors found, as well as validation messages for rows in the spreadsheet that do not contain errors.

Line

Number indicating the line in the spreadsheet that corresponds with the message in the Status field. Display only.

Status

Error or validation message associated with the line identified in the Number field. Display only.