OLE Data Import Templates

Owned by Nora Roggeveen-Sams (Unlicensed)
Last updated: Jun 30, 2014
Contents

This material was adapted from , documentation for the KFS 4.x releases.

Work in Progress

This information will continue to be updated.

Importing Data to OLE

OLE allows users to import multiple types of records.  In Select/Acquire, financial data can be inverted from spreadsheets (.csv files).  Bibliographic records, Orders, Patrons, and Locations can be imported with the Batch Process using Batch Process Profiles.  The first part of this page describes how to import data using templated spreadsheets.  The second part gives users sample data to modify and use for batch processing.

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.

Links to Default Data Import Templates

The following table lists the types of e-docs for which templates are available in the default system along with links to the corresponding templates.

Data import templates by document type

Document Type

Usage Notes

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.

Pre-Encumbrance (PE) -disencumbrance lines only

Disencumbrance lines only

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

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.

Process Overview

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.

(warning) 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).

Creating a .csv File

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 Driver's Manual (Guide to Managing Funds), found under OLE 0.8 User Documentation. In a future release, you will be able to access the templates through online help, the question mark icon , however this functionality is not yet working in OLE.

     

  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.
    (warning) 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.



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




    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.

BA_YEBA_Import.xls

The template applies to two document types:

  • Budget Adjustment
  • Year-End Budget Adjustment

The basic format of the template is shown below in two parts.

BA_YEBA_Import template format

Column

Field Name

A

Chart Code*
(warning) 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

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.

PE_Disencumbrance_Only_Import.xls

The 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*
(warning) 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*

PURAP_Account_Import.xls

The 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*
(warning) If your institution has set the ACCOUNTS_CAN_CROSS_CHARTS_IND parameter to NO, delete this column before using the template.

B

Account*

C

Sub-Account

D

Object Code*

E

Sub-Object Code

F

Project

G

Org Ref ID

H

%*

Sample Data

The Batch Process on the Admin tab, along with the Batch Process Profile, allows OLE users to import several types of records into OLE.

To learn more about Batch Process and the Profile, see the Batch Framework section of the OLE Guide to System Administration.

Patron

Patron Sample Data - singlePatron.xml

Patron Schema - Patron v2.xsd.xml

Circulation

Circulation loan policy - deliver2013.05.20.xml (Not imported via Batch Process but XML Import)

Location

Location Sample Data from IU - iu librariesSampleLocations.xml

 

Operated as a Community Resource by the Open Library Foundation