Importing Fixed Assets
This document describes the procedures for importing fixed asset information into Escape, both prior to system startup and to update an existing system. Also included are procedures for exporting fixed asset data from Escape.
Escape Fixed Assets includes a procedure for importing fixed asset data from a foreign system. The data must be extracted from that system in one of three ASCII formats:
- ASCII
- Fixed Field Length ASCII
- Comma Delimited ASCII
This document describes the file layout and procedures for importing in one of these formats. If you have data, but are unable to match our file layout, call for a price on converting your data to our format.
NOTE: Make a backup of your fixed assets database for beginning this procedure.
Fixed Asset Import Rules
Following is a description of the three supported file formats.
Comma Delimited Format
Fields in the file are separated by comma. Use the lengths in the layout below as a maximum for each field. Other rules:
- Record must end with ASCII Carriage Return (CR) and Line Feed (LF) characters.
- Text fields should be surrounded by double quotes.
- All date fields are to be MM/DD/YY format, eight characters in length. For example, January 1, 2002 would be 01/01/02.
- Currency fields must be numbers only. The fields must be "right justified" and "zero filled." No "punctuation" is allowed and the decimal point is implied. For example, if the unit cost has been defined like this:
UNIT_COST STRING(10) DDDDDDDDdd
For example, a price of $1740.50 would be formatted like this: 0000174050
Fixed Field Format
The file must contain fixed field and record lengths, with no delimiters. Other rules are:
- NO packed or binary information!
- You may utilize any or all of the fields specified in the table below - when not using a field it is to be padded with blanks (ASCII 32.)
- Currency fields must be numbers only. The fields must be "right justified" and "zero filled." No "punctuation" is allowed and the decimal point is implied. For example, if the unit cost has been defined like this:
UNIT_COST STRING(10) DDDDDDDDdd
For example, a price of $1740.50 would be formatted like this: 0000174050
ASCII Format
An ASCII format file is the same as Fixed Field format but each record must end with ASCII Carriage Return (CR) and Line Feed (LF) characters.
Conversion Considerations
During the conversion process, the records may be changed to take advantage of several circumstances. The person(s) who will be using the system should think about how they want the records to "look" when they are loaded into the Escape system. What's wrong with the way your information is stored in the existing system? What would you like to change? The following are special considerations for specific fields.
Campus
This field is primarily used in the Community College setting.
Account Number
You need to enter the account number in the format of the current structure, including delimiters. You can only import/export one account number. If you have an asset that was purchased using two accounts, you must edit the record to add the second account. If an account already exists in an existing fixed asset record, the import will not update the account number.
Asset Type
Due to GASB 34, asset type is now a required field. Since this field determines which assets are depreciable, you will want to make sure that you check for existing codes first. You will also want to check the LOCK ASSET TYPE field for groups. If this field contains a Y, the asset type will automatically be set to the asset type of the group.
Cost
The state no longer requires that you keep track of assets with a value of less than $500. Your district
may want to track items with a lower value, but you could "throw out" records below some
minimum value ($2-300).
Commodity Group
Most mainframe systems use unfriendly numbering schemes for the commodity (which we call group) - furniture for example. Instead of the furniture assets having a code of FURN, they have a code of 0211 or some other meaningless number. Why not convert all of these to alphabetic codes that make sense? AV for audio visual, etc. Because the group code is a required field in Escape, the end result is much cleaner if your import file contains a group code for each asset record prior to import.
If you are unable to provide specific group information we suggest that you insert the text NONE in the group field for each asset missing a group code. Remember, if you are currently a Purchasing customer you may already have some commodity/groups established. These records are shared between the two programs, so check for existing codes first!
SubGroup
Each group can contain up to 30 different subgroups. However, only one subgroup per asset. Remember, if you are currently a Purchasing customer you may already have some subgroups established. These records are shared between the two programs, so check for existing codes first!
Description
Our system does not have a separate manufacturer field. It is intended that this information be part of the description field. If your system has separate fields, you may want to combine them - for example, put together your manufacturer and description fields. A possible result might be "IBM Typewriter." The description field is 120 characters so there is plenty of room.
Model Number
Escape does have a Model Number field, which can contain descriptive information about the item.
Serial Number
We do not provide a separate VIN number field, but our Serial Number field is 20 characters long, big enough for VIN numbers.
Site
If you are currently a Purchasing customer, you already have site codes established. These records are shared between the two programs, so check for existing codes first!
Tag Number
This field must be unique. Duplicates are not allowed. It should also not be zero filled unless your barcode tags use them.
Vendor
If you are currently a Purchasing customer, you already have vendor codes established. These records are shared between the two programs, so check for existing codes first!
Alternate Funding Source
If you are interested in tracking assets by Funding Source, you may want to enter a Funding Source code for each asset applicable. For example, you might look for all assets that were purchased with a lottery account and enter a Funding Source such as LOTTERY for each asset referencing that account. Remember, if you are currently a Purchasing customer you may already have some Funding Source codes established. These records are shared between the two programs, so check for existing codes first!
History Record Information
The import program will ignore information placed in the History Record Info Lines 1 - 10 unless at least one of the fields below contains data:
- Date of History Record
- History Record Cost
- Vendor Code for History Record
NOTE: Remember if the asset is depreciable and you are using Escape software to perform your calculations, the asset must have a receive date, unit cost, salvage value, expected life and asset type.
Import File Layout
The Escape Import record layout contains the following fields which are listed below in the order required.
For your convenience, we have a column designating the position number in the ASCII file and a column heading if you are creating or editing this file in Microsoft Excel.
| Field | Size | Pos Num |
Excel Col Heading |
Description |
|---|---|---|---|---|
| Tag_Num | 10 | 1 | A | Tag Number (must be unique, do not zero fill) |
| Desc | 120 | 11 | B | *Asset Description |
| Serial_Num | 20 | 131 | C | Serial Number |
| PO_Number | 12 | 151 | D | Purchase Order Number |
| Rcv_Dt | 8 | 163 | E | Date Asset Received (MM/DD/YY). See NOTE 1 below. |
| Unit_Cost | 10 | 171 | F | *Unit Cost (See notes above for differences in currency formats in FIXED LENGTH, ASCII, and COMMA DELIMITED formats.) |
| Salvage_Value | 10 | 181 | G | Salvage Value (rules same as Unit Cost.) |
| Site_Code | 4 | 191 | H | *Site Code (Must be same as Escape Finance or Purchasing site codes.) |
| Room_Num | 6 | 195 | I | Room Number |
| Quantity | 5 | 201 | J | *Quantity |
| Vendor_Code | 6 | 206 | K | Vendor Code |
| Alt_Source | 12 | 212 | L | Alternate Funding Source (Program) |
| Group_Code | 10 | 224 | M | *Group Code |
| SubGroup | 12 | 234 | N | Sub-category of Group (up to 15 possible.) |
| Cond_Code | 4 | 246 | O | Condition Code |
| Foreign_System | 1 | 250 | P | "F" (required, imported from foreign system) |
| Account_Num | 60 | 251 | Q | Appropriation Account Number, including delimiters |
| Dept | 4 | 311 | R | Department Code |
| Disposal_Dt | 8 | 315 | S | Date of Disposal (MM/DD/YY) |
| Disp_Method | 40 | 323 | T | Disposal Method Text |
| Longevity | 2 | 363 | U | Expected Life in Years |
| Scan_Dt | 8 | 365 | V | Date of Barcode Scan (MM/DD/YY) |
| Campus_Cd | 2 | 373 | W | Campus (used by community colleges) |
| Model_Num | 20 | 375 | X | Model Number |
| Disposal_Cd | 4 | 395 | Y | Disposal Method Code |
| Warranty_Days | 4 | 399 | Z | Length of Warranty in Days |
| Warranty_Dt | 8 | 403 | AA | Warranty Begin Date |
| Svendor | 6 | 411 | AB | Vendor Code for Service Vendor |
| Service_Amt | 8 | 417 | AC | Service Amount (rules same as Unit Cost) |
| Service_Exdt | 8 | 425 | AD | Service Expiration Date |
| Service_PO | 12 | 433 | AE | Service Contract PO Number |
| Accum_Depreciation | 10 | 445 | AF | Accumulated Depreciation ( rules same as Unit Cost ) |
| Hist_Dt | 8 | 455 | AG | Date of History Record |
| Hist_Cost | 9 | 463 | AH | History Record Cost (rules same as Unit Cost) |
| Hist_Vendor | 6 | 472 | AI | Vendor Code for History Record |
| Hist_Info1 | 50 | 478 | AJ | History Record Info, Line 1 |
| Hist_Info2 | 50 | 528 | AK | History Record Info, Line 2 |
| Hist_Info3 | 50 | 578 | AL | History Record Info, Line 3 |
| Hist_Info4 | 50 | 628 | AM | History Record Info, Line 4 |
| Hist_Info5 | 50 | 678 | AN | History Record Info, Line 5 |
| Hist_Info6 | 50 | 728 | AO | History Record Info, Line 6 |
| Hist_Info7 | 50 | 778 | AP | History Record Info, Line 7 |
| Hist_Info8 | 50 | 828 | AQ | History Record Info, Line 8 |
| Hist_Info9 | 50 | 878 | AR | History Record Info, Line 9 |
| Hist_Info10 | 50 | 928 | AS | History Record Info, Line 10 |
| Asset_Type | 1 | 978 | AT | * Asset Type |
| TOTAL | 978 | Total characters for FIXED or ASCII imports containing all fields. |
NOTE: Asterisk (*) in the Description Column indicates minimum required fields for import.
NOTE 1: Escape will automatically convert all of the two-digit years to four-digit years. The century is determined using a floating value that defaults to 30 years into the future which means that anything after that year will be treated as a past date.
- If the two-digit year is thirty years greater than the current calendar year (e.g., 2003 + 30), then Escape uses 19 for the first two digits (e.g., in the calendar year 2003, an asset received on 10/16/34 will be converted to 10/16/1934).
- If the two-digit year is equal to or less than the current calendary year plus 30 (e.g., 2003 + 30), then Escape uses 20 for the first two digits (e.g., in the calendar year 2003, an asset received on 10/16/33 will be converted to 10/16/2033).
Using this example, the only assets you need to be concerned about are those with a receive date of 1904 to 1933. These will need to be edited to correct the century. To do this, open the asset record in the FA module, tab to the RECEIVED field and change the date to the correct date. Save the record.
Sample Import Data File
To download a sample standard format import data file containing 1 record, right click on the link and choose Save as. IMPORTFA.PRN (Fixed Length - Notepad) or IMPORTFA.XLS (Excel)
The record contains a fixed asset record for a PowerMac, including most of the fields available for input. You may wish to edit the sample file, change the account number to an account number that matches your district's account structure, then import it into your database as a test.
How to Run the Import Process
Once you have your import file set up, you are ready to use the import utility. From the Fixed Assets main menu, choose the Import Fixed Assets option. The following screen will be displayed.
Below are descriptions of each field.
- Record Layout
Enter A for output in ASCII format. Enter F for output in fixed length format. Enter C for output in comma delimited format. - Update Level
This field determines how the import process will affect the current database. There are several options. You will want to carefully choose your option. Please contact Escape Customer Care if you have any questions or concerns. Remember to always backup your data before processing the import file!- Add
Enter an A to add all of the records in the import file. This option will not update records. If a record in the import file has the same tag number as an existing record, you will receive an error message. - Clear All
Enter a C to DELETE the ENTIRE fixed assets database. This will delete all fixed assets records and any associated records (e.g., history, audit trail), but not the barcode scan file. The records in the import file will then be added. - Update
Enter a U to update and add records. This will leave all history, audit, etc., records intact. It will update any record with a matching tag number and add any record that has a tag number that does not match an existing record. Be careful, because it will add ALL records that do not have a tag number. You cannot update a record that does not have a tag number.NOTE: The account number cannot be updated using this option.
- Replace
Enter an R to update, add and delete records. This will leave all history, audit, etc., records intact. First, it compares the existing database to the import file. It deletes any records that are not found in the import file and all records that do not have a tag number. Then, it reads the import file again and updates any record with a tag number that matches an existing record, adds any record with a tag number that does not match an existing record, and adds any record that does not have a tag number.
- Add
- Import File
Enter the name of the import file, or use SHIFT+ENTER to get a lookup window.
Press F3 when you are ready to begin the import. You will be prompted to verify that you are ready to begin. Depending on the UPDATE LEVEL, you will be prompted to confirm that you are ready to replace files, purge data, etc. Escape will display its progress on the screen, including the number of assets created, updated, etc. When the process is complete, press any key to continue.
If you entered R for the UPDATE LEVEL, Escape will create a set of files: NewAsset.*, NewAudit.*, NewAccts.*, NewHist.*, NewScan.*, and NewDepre.* in the \ESCAPE\PUR subdirectory. These files will contain only data for assets found in the file you are importing. After the import is complete, run CSRT on all of the .DAT files mentioned above. Then, copy the new file sets to their equivalent production file set:
NewAsset.* -> FixAsset.* NewAudit.* -> FixAudit.* (Note: this is optional) NewAccts.* -> FixAccts.* NewHist.* -> FixHist.* NewScan.* -> FixScan.* NewDepre.* -> FixDepre.*
How to Export Records
Escape makes exporting fixed asset data easy and manageable. The export data file contains the same fields, listed in the same order, as described for the import file.
You can choose the Export Fixed Assets option from the Fixed Assets main menu to export all fixed assets, or you can create a list and export only the list. The following screen will be displayed.
Below, are descriptions of each field.
- Record Layout
Enter A for output in ASCII format. Enter F for output in fixed length format. Enter C for output in comma delimited format. - Export File
Enter the name of the export file.
Press F3 when you are ready to begin the export. You will be prompted to verify that you are ready to begin. Escape will display its progress on the screen, including the number of assets exported. When the process is complete, press any key to continue.
NOTE: The export does not include any of the History Information fields. The last field exported is the Asset Type field.