Importing Work or Material Items From Microsoft Excel
Work Items (also materials) are primarily used for Estimating purposes but can also be imported into many project management features.
How To Prepare The Microsoft Excel Import File
- Open a blank Microsoft Excel Spreadsheet or download the sample file referenced below.
- The first row (column names) should be labeled as shown in the table. Be sure to include the required fields. Field names do not contain any spacing within the word or before and after.
- Insert data starting at Row 2. There should be a maximum of 3K records per import file.
- Unit of measures (SpreadsheetColumns: Units, MatlUnits, LbrUnits, EqpUnits, SubUnits, OtherUnits) must be 10 characters or less.
- If Work Items are going to be categorized by a classification structure such your master cost codes, be sure these codes exist in Sage Construction Management before importing.
- Make sure the sheet name that contains the data is labeled Sheet1.
- Save the file in Excel 97-2003 format (*.xls).
- Close the file otherwise Sage Construction Management can not import the data.
Steps To Import the File
- Click the Cost DB icon on the left menu.
- Navigate to the Work Items row in the Cost Database Stats section.
- Click the Actions button on the far right.
- Click Import From Excel option from the drop-down menu.
- Click the Select Excel File button to select the Microsoft Excel import file.
- After selecting the file, the data will be imported automatically.
Microsoft Excel Import Sample
Work Item Fields (1st Row):
| Column Name | Required (*) | Field Type | Comments |
| Code | * | Text | Maximum Characters: 25 |
| Description | * | Text | Maximum Characters: 255 |
| Units | * | Text | Must be 10 characters or less. Typical for MatlUnits, LbrUnits, EqpUnits, SubUnits, and OtherUnits. |
| Manufacturers Information | |||
| Manufacturer | Text | ||
| UPC | Text | Universal Part Code | |
| CatalogNum | Text | ||
| Size | Text | Overall size or dimensions | |
| ShipWeight | Text | ||
| ShipHeight | Text | ||
| ShipWidth | Text | ||
| ShipDepth | Text | ||
| Material Fields | |||
| MatlConv | Number | If the Material Cost Rate (MatlCostRate) is specified in the spreadsheet, it is recommended to include the columns: MatlConv, MatlWaste, and MatlUnits. If the Material Sell Rate column (MatlSellRate) is not included in the spreadsheet which is recommended, then this rate will be 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 is recommended to include the columns: LbrConv and LbrUnits.
If the Labor Sell Rate column (LbrSellRate) is not included in the spreadsheet which is recommended, then this rate will be equal to the sum of the Labor Base Cost Rate (LbrBaseCostRate) plus Labor Burden Cost Rate (LbrBurdenCostRate). If hours, days, weeks, or months is used for the LbrUnits column, Sage Construction Management will automatically calculate the correct man-hours for the line item. Estimate Example: Estimate Item Quantity = 24 LbrConv = 0.5 LbrQty = Est Item Quantity * LbrConv = 24 * 0.5 = 12 If LbrUnits = Hours or Hrs then Est. Items Man Hours = 12 If LbrUnits = Days or Day then Est. Items Man Hours = 12 * 8 = 96 If LbrUnits = Weeks or Week then Est. Items Man Hours = 12 * 40 = 480 |
|
| LbrUnits | Text | ||
| LbrBaseCostRate | Number | ||
| LbrBurdenCostRate | Number | ||
| LbrSellRate | Number | ||
| Equipment Fields | |||
| EqpConv | Number |
If Equipment Cost Rates (EqpBaseCostRate or EqpBurdenCostRate) are specified in
the spreadsheet, it is recommended to include the columns: EqpConv and EqpUnits.
If the Equipment Sell Rate column (EqpSellRate) is not included in the spreadsheet which is recommended, then this rate will be equal to the sum of the Equipment Base Cost Rate (EqpBaseCostRate) plus Equipment Burden Cost Rate (EqpBurdenCostRate). If hours, days, weeks, or months is used for the EqpUnits column, Sage Construction Management will automatically calculate the correct equipment hours for the line item. This is similar to the labor example shown above. |
|
| EqpUnits | Text | ||
| EqpBaseCostRate | Number | ||
| EqpBurdenCostRate | Number | ||
| EqpSellRate | Number | ||
| Subcontractor Fields | |||
| SubConv | Number | If the Sub Cost Rate (SubCostRate) is specified in the spreadsheet, it is recommended to include the columns: SubConv and SubUnits. If the Sub Sell Rate column (SubSellRate) is not included in the spreadsheet which is 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 is recommended to include the columns: OtherConv and OtherUnits. If the Other Sell Rate column (OtherSellRate) is not included in the spreadsheet which is 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 will not impact the labor conversion factor and unit rates during import. They can only be used in the future by the Labor and Equipment Calculator.
Comma should separate multiple entries. Example: Clab, ClabF |
|
| LbrQuantities | Text |
Comma should separate multiple entries which would correspond to LbrCodes. Example: 3, 1 |
|
| EqpCodes | Text |
These references will not impact the equipment conversion factor and unit rates during import. They can only be used in the future by the Labor and Equipment Calculator.
Comma should separate multiple entries. Example: Truck, Backhoe |
|
| EqpQuantities | Text |
Comma should separate multiple entries which would correspond to EqpCodes. Example: 1, 1 |
|
| Classifications Fields | |||
| TypeClassificationTitle and Division, Major, Minor, Subminor | Text | Examples: CSI 95: CSI 95Division, CSI 95Major, CSI 95Minor, CSI 95Subminor CSI 2004: CSI 2004Division, CSI 2004Major, CSI 2004Minor, CSI 2004Subminor Multiple Classification Structures could be referenced on the spreadsheet. | |