Data Import Templates for Select/Acquire

OLE import templates allow you to import any number of accounting lines, endowment transaction lines, purchasing line items, and other types of data from a comma-delimited (.csv) file. This section contains a table that lists types of e-docs for which templates are available in the default system and provides a link to the corresponding template for each document type.

Following the table is a process overview that includes complete instructions for using templates to import multiple lines of data. Additional subsections contain detailed information about each template.

Data import templates by document type

Document Type

Description

Advance Deposits (AD)

Disbursement Voucher (DV)

AC_CR_CCR_DV_SB_Import.xls

Budget Adjustment (BA)

Year End Budget Adjustment (YEBA)

If an amount is erroneously entered in the Base Budget Adjustment Amount column, it is imported to the accounting line of the e-doc. An error is displayed when the data is validated via saving or submitting the e-doc.

Distribution of Income and Expense (DI)

Pre-Encumbrance (PE) - encumbrance lines only

Transfer of Funds (TF)

Year End Distribution of Income and Expense (YEDI)

Year End Transfer of Funds (YETF)

DI_YEDI_IB_TF_YETF_PE_Encumbrance_Only_Import.xls

General Error Correction (GEC)

Year End General Error Correction (YEGEC)

GEC_YEGEC_Import.xls

Pre-Encumbrance (PE) -disencumbrance lines only

Disencumbrance lines only

Purchase Requisition (REQS) and Purchase Order (PO) account import

PURAP_Account_Import.xls

The REQS and PO documents have no Accounting Lines tab, but clicking setup distribution in the Items tab opens an Accounting Lines section. This template imports account data into the Accounting Lines section.

The import lines button is located in the upper right corner of the appropriate tab (for example, the Accounting Lines tab). This button allows you to import a .csv file containing multiple lines of data into the tab.

Different types of e-docs use different import templates, but in some cases multiple e-docs use the same template.

Note

The layout and format of the .csv import template files varies by document type. Using the incorrect format for the .csv file causes the import to fail. Note that, for financial transactions, a separate file is needed for each Accounting Lines tab section (for example, the From and To sections).

OLE provides a template for each document type (in some cases, multiple document types use the same template), for example:

The format of the .cvs file varies depending on the document type. The desired template file for the document may be downloaded from Google Docs (PURAP_Account_Import, PE_Disencumbrance_only_Import, BA_YEBA_Import).

  1. Please find the import rules and format requirements in the Guide to the OLE Select and Acquire Module.

  2. In Google Docs, open the template file that corresponds to the type of financial transaction document you are working on. You may "Download As" from the File menu of Google Docs to save the template to your desktop, please do not try to edit the document within Google Docs/Drive.

  3. Save the template to your computer as an Excel workbook.

  4. Add data to the file.

    Note

    Required field headings are marked with an asterisk and appear in red.

  5. If the file is to be used again in the future, save the file as an Excel workbook (don't delete the headers yet). If the file is not to be used again, skip this step.

  6. Delete the header rows at the top.

  7. Save the file again, with a new name, in .csv format.

  8. Click . In OLE the import lines button is located in the upper right corner of the applicable tab.

    The system displays additional buttons such as Browse, add, and cancel import.

  9. Click and locate the file you have created from your computer.

  10. From the Choose File window, select the .csv file to be imported and click

  11. Click.

    The screen displays the imported data.

  12. When finished, archive or delete the .csv file.

When the file is needed again, follow these instructions:

  1. Open the saved Excel workbook with the data from Step 5 above.

  2. Edit the file as necessary.

  3. Follow steps 6 - 12 above.

The AC_CR_CCR_DV_SB_Import Google Doc template applies to two OLE document types:

  • Advance Deposits Document (AD)

  • Disbursement Voucher (DV)

AD_CR_CCR_DV_SB_Import template format

Column

Field Name

A

Chart Code*

Note

If your institution has set the ACCOUNTS_CAN_CROSS_CHARTS_IND parameter to NO, delete this column before using the template

B

Account Number*

C

Sub-Account Number

D

Object Code*

E

Sub-Object Code

F

Project Code

G

Organization Reference Id

H

Line Description

I

Amount*

The BA_YEBA_Import Google Doc template applies to two document types:

  • Budget Adjustment

  • Year End Budget Adjustment

BA_YEBA_Import template format

Column

Field Name

A

Chart Code*

Note

If your institution has set the ACCOUNTS_CAN_CROSS_CHARTS_IND parameter to NO, delete this column before using the template

B

Account Number*

C

Sub-Account Number

D

Object Code*

E

Sub-Object Code

F

Project Code

G

Organization Reference Id

H

Current Budget Adjustment Amount*

I

Base Budget Adjustment Amount

J

Month 1 Budget Amount

K

Month 2 Budget Amount

L

Month 3 Budget Amount

M

Month 4 Budget Amount

N

Month 5 Budget Amount

O

Month 6 Budget Amount

P

Month 7 Budget Amount

Q

Month 8 Budget Amount

R

Month 9 Budget Amount

S

Month 10 Budget Amount

T

Month 11 Budget Amount

U

Month 12 Budget Amount

Note

If an amount is erroneously entered in the Base Budget Adjustment Amount column, it is imported to the accounting line of the e-doc. An error is displayed when the data is validated via saving or submitting the e-doc.

The DI_YEDI_IB_TF_YETF_PE_Encumbrance_only_Import Google Doc template applies to several document types.

  • Distribution of Income and Expense (DI)

  • Pre-Encumbrance - encumbrance lines only

  • Transfer of Funds (TF)

  • Year-End Distribution of Income and Expense (YEDI)

  • Year-End Transfer of Funds (YETF)

The basic format of the template is shown below.

DI_YEDI_IB_TF_YETF_PE_Encumbrance_Only_Import template format

Column

Field Name

A

Chart Code*

Note

If your institution has set the ACCOUNTS_CAN_CROSS_CHARTS_IND parameter to NO, delete this column before using the template

B

Account Number*

C

Sub-Account Number

D

Object Code*

E

Sub-Object Code

F

Project Code

G

Organization Reference Id

H

Line Description

I

Amount*

The GEC_YEGEC_Import Google Doc template applies to the General Error Correction and Year End General Error Correction document types. The basic format of the template is shown below.

GEC_YEGEC_Import template format

Column

Field Name

A

Chart Code*

Note

If your institution has set the ACCOUNTS_CAN_CROSS_CHARTS_IND parameter to NO, delete this column before using the template

B

Account Number*

C

Sub-Account Number

D

Object Code*

E

Sub-Object Code

F

Project Code

G

Organization Reference Id

H

Reference Origin Code*

I

Reference Number*

J

Line Description

K

Amount*

The PE_Disencumbrance_only_Import Google Doc template applies to the Pre-Encumbrance (disencumbrance lines only) document type. The basic format of the template is shown below.

PE_Disencumbrance_Only_Import template format

Column

Field Name

A

Chart Code*

Note

If your institution has set the ACCOUNTS_CAN_CROSS_CHARTS_IND parameter to NO, delete this column before using the template

B

Account Number*

C

Sub-Account Number

D

Object Code*

E

Sub-Object Code

F

Project Code

G

Organization Reference Id

H

Reference Number*

I

Amount*

The PURAP_Account_Import Google Doc template applies to both the Requisition document type and the Purchase Order document type. It is used to import accounting lines into a requisition or purchase order.

The basic format of the template is shown below.

PURAP_Account_Import template format

Column

Field Name

A

Chart Code*

Note

If your institution has set the ACCOUNTS_CAN_CROSS_CHARTS_IND parameter to NO, delete this column before using the template

B

Account Number*

C

Sub-Account Number

D

Object Code*

E

Sub-Object Code

F

Project Code

G

Organization Reference Id

H

%*

Prev Up Next
 Home