Import Bills From Microsoft Excel

Prepare The Microsoft Excel Import File

  1. Open a blank Microsoft Excel Spreadsheet or download the sample file referenced below.
  2. 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.
  3. Insert data starting at Row 2.
  4. Make sure the sheet name that contains the data is labeled Sheet1.
  5. Save the file in Excel 97-2003 format (*.xls).
  6. Close the file otherwise Sage Construction Management can not import the data.

Import the File

  1. Click the Projects icon on the left menu.
  2. Select the Project in the Quick Select section. This will then refresh the page and show the Current Project Home page.
  3. On the Projects menu, select Procurement overview.
  4. Navigate to the Bills row in the Stats section.
  5. Click the Actions button on the far right.
  6. Click Import From Excel option from the drop-down menu.
  7. Click the Select Excel File button to select the Microsoft Excel import file.
  8. After selecting the file, the data will be imported automatically.

Microsoft Excel Import Sample

ImportBills.xls

Bill Transaction - Header Fields (1st Row):

Column Name Required (*) Field Type Comments
InvoiceNumber * Text
InvoiceDate * Date

Recommended date format: DD-MMM-YYYY (example: 15-Oct-2023)

US supported format: MM-DD-YYYY (example: 10/15/2023)

ISO supported format: YYYY-MM-DD (example: 2023-10-15)
ProjectNumber * Text Project # must exist in the system before importing.
PrimeContractNumber * Text Prime Contract # must exist in the system before importing.
ChangeOrderNumber Text CO # must exist in the system before importing.
PONumber Text

In order to link the bill to the PO, the PO# must exist in the system before importing. If the PO# does not exist, the field is ignored.

The system will use item description to match the Bill and PO line items.

SupplierCompany * Text The Company referenced must exist in the Contact Management module before importing with the Name matching exactly. Otherwise, the Bill will not be imported.
SupplierContact Text Like the Company reference, the Contact must exist in the Contact Management module and match the Display Name exactly. Otherwise, the Bill will not be imported.

This field can also be left blank.
BillType Text Reference must already exist in the system. See Settings | Features | Procurement for options.
Subject * Text
Terms * Text
Either Terms or PayDueDate must be specified in the spreadsheet. If both are specified, PayDueDate will be used.

Recommended date format: DD-MMM-YYYY (example: 15-Oct-2023)

US supported format: MM-DD-YYYY (example: 10/15/2023)

ISO supported format: YYYY-MM-DD (example: 2023-10-15)
PayDueDate Date

Bill Transaction - Item Detail Fields (1st Row):

Column Name Required (*) Field Type Comments
ItemNumber * Integer Examples: 1, 2, 3
ItemCode Text Optional: Sage Construction Management will do a lookup on the Work Items table based on the ItemCode field. If an item exists then the Work Items total cost rate can be used as the Bill Items unit price.
ItemDescription * Text
ItemManufacturer Text
ItemUPC Text
ItemCatalogNum Text
ItemUnit * Text Examples: Ea, LS. Must be 10 characters or less.
ItemQuantity * Number If not specified, default will be 1.
ItemUnitPrice * Number
ItemResource * Text Options: M, L, E, S, O

If not specified, default will be M.
ItemCostCode * Text Must exist in the system before importing.
ItemTaxCode Text Must exist in the system before importing. Otherwise, leave blank.
BillableStatus Text Options: Billable, Unbillable, or On Hold. This field is only applicable on Cost Plus Contracts.