Import work or material items from a Microsoft Excel file
Work items, including materials, are mainly used for estimating, but they can also be imported into various project management features.
Before you begin
-
Each import file must contain no more than 3,000 records.
- All unit fields must be 10 characters or less.
-
If you're categorizing work items using a classification structure like your master cost codes, make sure those codes already exist in Sage Construction Management before importing.
Prepare a Microsoft Excel file for import
- Open a blank Microsoft Excel spreadsheet or download the sample file.
- The first row specifies the column names and must be labeled as shown in the table below.
Make sure that all the required fields are included. Field names do not contain any spacing within the word, or before and after.
Make sure that the sheet is labeled as Sheet1.
- Start entering data from the second row.
- Save the file in Excel 97-2003 format (*.xls).
- Close the file to enable Sage Construction Management to import the data.
Import the file
- Go to Cost DB.
- In the Internal Cost Database Stats section, select the actions icon (
) next to the Work Items. - Select Import From Excel.
- Select Choose File and find the file to be imported on your local drive.
The data will be imported automatically.
Microsoft Excel import sample
Work item file fields
| Column name / First row | Required (*) | Field Type | Comments |
| Code | * | Text | The maximum number of characters allowed is 25. |
| Description | * | Text | The maximum number of characters allowed is 255. |
| Units | * | Text | Must be 10 characters or less. Commonly used for MatlUnits, LbrUnits, EqpUnits, SubUnits, and OtherUnits. |
| Manufacturers Information | |||
| Manufacturer | Text | ||
| UPC | Text | Universal part code. | |
| CatalogNum | Text |
Manufacturer's catalog number. |
|
| Size | Text | Overall size or dimensions. | |
| Color | Text | Item color. | |
| Finish | Text | Item finish. | |
| ShipWeight | Text | Shipping weight. | |
| ShipHeight | Text | Shipping height dimension. | |
| ShipWidth | Text | Shipping width dimension. | |
| ShipDepth | Text | Shipping depth dimension. | |
| Material Fields | |||
| MatlConv | Number | If the material cost rate (MatlCostRate) is specified in the spreadsheet, it is
recommended to also specify the MatlConv, MatlWaste, and MatlUnits columns.
If the material sell rate column (MatlSellRate) is not included in the spreadsheet as recommended, Sage Construction Management will automatically set the sell rate equal to the material cost rate. |
|
| MatlWaste | Number | ||
| MatlUnits | Text | ||
| MatlCostRate | Number | ||
| MatlSellRate | Number | ||
| Labor Fields | |||
| LbrConv | Number |
If labor cost rates (LbrBaseCostRate or LbrBurdenCostRate) are specified in the spreadsheet, it's recommended to also specify the LbrConv and LbrUnits fields. If the labor sell rate column (LbrSellRate) is not included in the spreadsheet as recommended, the rate will default to the sum of the labor base cost rate (LbrBaseCostRate) and the labor burden cost rate (LbrBurdenCostRate). If hours, days, weeks, or months is used in the LbrUnits column, Sage Construction Management will automatically calculate the correct man-hours for the line item. For example, if the Quantity is 24 and the LbrConv is 0.5, then LbrQty = Quantity * LbrConv = 24 * 0.5 = 12 The LbrUnit field must be 10 characters or less. The following units are recognized and supported:
|
|
| LbrUnits | Text | ||
| LbrBaseCostRate | Number | ||
| LbrBurdenCostRate | Number | ||
| LbrSellRate | Number | ||
| Equipment Fields | |||
| EqpConv | Number |
If you include equipment cost rates (EqpBaseCostRate or EqpBurdenCostRate) in your spreadsheet, it's recommended to also include the EqpConv and EqpUnits columns. If the equipment sell rate (EqpSellRate) column is not provided as recommended, Sage Construction Management will automatically calculate it as the sum of the equipment base cost rate and equipment burden cost rate. The EqpUnit field must be 10 characters or less. The following units are recognized and supported:
If EqpUnits uses time-based values such as hours, days, weeks, or months, Sage Construction Management will automatically calculate the appropriate equipment hours for each line item, similar to how labor hours are handled. |
|
| EqpUnits | Text | ||
| EqpBaseCostRate | Number | ||
| EqpBurdenCostRate | Number | ||
| EqpSellRate | Number | ||
| Subcontractor Fields | |||
| SubConv | Number |
If the subcontractor cost rate (SubCostRate) is specified in the spreadsheet, it's recommended
to also include the SubConv and SubUnits columns. If the subcontractor sell rate column (SubSellRate) is not included in the spreadsheet as recommended, then this rate will be equal to the sub cost rate. |
|
| SubUnits | Text | ||
| SubCostRate | Number | ||
| SubSellRate | Number | ||
| Other Fields | |||
| OtherConv | Number |
If the other cost rate (OtherCostRate) is specified in the spreadsheet, it's recommended
to also include the OtherConv and OtherUnits columns. If the other sell rate column (OtherSellRate) is not included in the spreadsheet as recommended, then this rate will be equal to the other cost rate. |
|
| OtherUnits | Text | ||
| OtherCostRate | Number | ||
| OtherSellRate | Number | ||
| Comment Fields | |||
| Comments | Text | ||
| ProposalComments | Text | ||
| RFPComments | Text | ||
| Labor and Equipment Resources (Used by Lbr/Eqp Calculator) | |||
| LbrCodes | Text |
These references do not impact the labor conversion factors or unit rates during import. They are only used later by the Labor and Equipment Calculator.
Separate multiple entries with commas. For example: Clab, ClabF |
|
| LbrQuantities | Text | Separate multiple entries with commas to match corresponding LbrCodes. For example: 3, 1 | |
| EqpCodes | Text | These references do not affect equipment conversion factors or unit rates during import. They are only used later by the Labor and Equipment Calculator. Separate multiple entries with commas. For example: Truck, Backhoe |
|
| EqpQuantities | Text | Separate multiple entries with commas to match corresponding EqpCodes. For example: 1, 1 | |
| Classifications Fields | |||
| TypeClassificationTitle and Division, Major, Minor, Subminor | Text |
For example:
Multiple classification structures can be referenced in the spreadsheet. |
|