Importing Estimation Reference Data

You can import estimation reference data into AASHTOWare Project by importing a CSV or XML file. Imported data can include crews, cost sheets, laborers, and equipment, as well as rates for materials, laborers, and equipment, and reference prices for items. Importing the file can add reference prices to existing items, and it can add material rates to existing materials, but it cannot create new items or new materials.

For crews and cost sheets, you can choose to merge the imported data with existing data or overwrite existing data. For crews, if you merge the imported data, existing laborers and equipment records in the crew will be updated and new laborers and equipment records in the import file will be appended to the existing laborers and equipment records in the crew. For cost sheets, if you merge the imported data, all laborers and equipment records in the import file will be appended to the existing laborers and equipment records in the cost sheet.

Each record you import must include the key fields for the entity. In addition, key field values used in child records must either pre-exist in the system or appear in the import file prior to the record that uses them. For example, if you are importing a new reference crew that includes new laborers and equipment, the new laborer and equipment records must precede the reference crew in the import file. This could also be achieved by importing the laborer and equipment records first in one file, and then importing crews and cost sheets in a second file. If you are adding rates for materials, laborers, or equipment, the rate class codes must already exist in the appropriate code table. Values are case-sensitive.

• Note: During a file import, the Effective Date is part of the unique key for an item reference price. As a result, if you import an item reference price that has the same Item ID, Spec Book, and Reference Price ID as an existing reference price, the system will create a new reference price if the Effective Date in the import file is unique.

File Formatting Requirements

If you are importing the data as an XML file, the file must conform to the EstimationReferenceDataImportSchema.xsd schema file (located in the Support Files folder). The first line in the file must include "estimationreferencedata" to identify the contents of the file.

If you are importing the data as a CSV file, the file must use the correct column headings for each entity, as shown in the following example. Blank or missing values are allowed subject to business constraints and each field must be represented in the CSV file record for a given entity. For example, an item reference price must have a value for only one of the following fields: Amount, Percent of, Percent on top. If a value includes a space, the entire value must be enclosed in quotes.

 

AASHTOWareProjectEstimationReferenceData,csv
CSVHeader,for,specbook,name,effectivedate,amount,percentof,percentontop,expirationdate,description
itemprice,202-00013,99,refprice01,12/10/2015,12.5,,,,descr1
itemprice,202-00030,99,refprice02,12/1/2015,,10,,,descr2
CSVHeader,for,rate,class
materialrate,matcode001,55.75,MRATE1
materialrate,matcode002,123,MRATE2
CSVHeader,id,name,description
equipment,equip01,equip01,"equip01 descr"
CSVHeader,ref:equipment,class,rate
rate,equip01,EQRATE3,600
CSVHeader,Id,name,description
laborer,Laborer01,Laborer01,"Laborer01 Description"
CSVHeader,ref:laborer,class,rate,overtimerate,davisbaconrate,davisbaconovertimerate
rate,Laborer01,STATE01,10,15,,
rate,Laborer01,STATE02,12,17,22,44
CSVHeader,Id,name,description
crew,Crew01,Crew01,"Crew01 Descr"
CSVHeader,ref:crew,name,quantity
laborer,Crew01,Laborer01,1
CSVHeader,ref:crew,name,quantity
equipment,Crew01,equip01,12
CSVHeader,Id,name,description,unitsystem,unitofmeasure,laboroverheadpct,equipmentoverheadpct,materialoverheadpct,markupoverheadpct
costsheet,costsheet123a,costsheet123a,"123rd cost sheet, part a",English,ACRE,10.2,3.7,0.1,22.4
CSVHeader,ref:costsheet,code,quantity
material,costsheet123a,matcode001,123
CSVHeader,ref:costsheet,name,regular,overtime,quantity,crewname
laborer,costsheet123a,Laborer01,8,4,2,Crew01
CSVHeader,ref:costsheet,name,quantity,hours,crewname
equipment,costsheet123a,equip01,1,7,Crew01

Importing a Reference Data File

To import an XML or CSV file containing estimation reference data, perform these steps:

1. Select Import File from the Actions menu on the Menu Bar.
   
   The system takes you to the Import component.

2. Click the Select File button.
   
   The system opens a Choose File to Upload window.

3. In the Choose File to Upload window, navigate to the estimation reference data file you want to import, select it, and click the Open button.
   
   The system validates the selected file and displays the name and size of the file.

4. Choose whether you want the system to merge the new data or overwrite data already in the system. This applies only to laborer and equipment records for crews and cost sheets.
   
   To merge the data: select the Merge Crew and Cost Sheets with existing data option. This option is selected by default.
   To overwrite existing data: select the Replace existing Crew and Cost Sheets option.

5. When you are finished, click the Import button.
   
   The system displays a message to confirm that the import was successful, or provides any appropriate error messages for records that were not imported. If an error is encountered, the system continues to import remaining records in the file that are not affected by the error condition.

6. Note: Because the system converts CSV files to XML format before the import, any error messages displayed after importing a CSV file will reference "invalid XML".

Related topics:

Importing Cost Estimates