About Data Importer

Data Importer is a powerful tool that automates the process of updating your database prices accurately and efficiently.

You can update prices in your database based on phase and item codes, price code, or WBS code and WBS value. Data Importer reads your price update file and replaces the prices in your current database and any other databases that are being updated with the new item prices.

Your price update file must be in an acceptable file format, such as CSV (Microsoft Excel comma separated value file), TXT (text document), or XLS (Microsoft Excel worksheet) format.

The update process generates a log file that shows each price change, the total number of prices changed, and the number of errors, if any. Errors are shown at the end of the report.

Valid formats for price update files

Your price update file must use one of the following formats:

• Excel worksheet (XLS)
• Comma Separated Value file (CSV)
• Text file (TXT)

The following examples of price update files show how to achieve the same price changes in the database with different file formats. All the examples:

• Update the material price to $500 for any items that have a WBS code labeled “Level4” and a value of “01-09-31.50”.
• Update the labor price to $0 and the subcontract price to $566.14 for any items that have a price code of “U10000”.
• Update the labor, material, subcontractor, equipment, and "other" price categories for phase 01-45-23.50, item 1800 to 1800, 0.65, 289.81, 578.97, 868.13, and 1157.29 respectively.

Note: For more detailed information about how to structure each type of record (for example, how to enter WBS code and Value fields), use the links in the Learn More box in this topic.

Updating database prices using a price update file

You can easily update prices in a standard database with prices from a third party service by importing price information.

The price import file must use a format and data structures that the Data Importer can read.

Estimates you create after updating prices use the new database prices. Updating the database prices does not affect existing estimates.

Notes:

• Data Importer does not update prices for labor or equipment where crews are used.
• If multiple source rows in the price update file use the same criteria, the final sequential row is used to update the target table.

Before you begin

• Create a price update file using an acceptable format and structure.

To update prices:

1. In the Database Editor, open the standard database for which you are going to update prices.
2. On the Data tab, click Import.
3. In the Import Data window, in the Source file name box, type the name of the price update file that you want to use to update the current database, or browse to select the file.

Note: If you do not see the file you are looking for, select the correct file type beside the File name box.

4. To start the price update, click [OK].

To cancel the update, click [Cancel]. The original prices will be restored in the current database.

5. When the import price update is finished, click the [View Log] button to display a report that lists the records that were updated as well as records that could not be processed and the reason.
6. Check the log to ensure that it includes all the required changes and that there are no errors or unwanted exceptions.
7. To accept the changes, updating your database permanently, on the Home tab, click the Save icon .

To discard the changes, reverting to the prices before the price update, close the database without saving the price changes.

8. To update the database for use in Estimating, on the Home tab, from the Database group, click Check In.

To cancel the checkout and remove any changes made, click Undo Check Out.

About the price update log

When you import a price update file, the Data Importer creates an Import Data log that lists all the records that were updated as well as records that could not be updated for some reason.

Viewing the Import Data log

To view the Import Data log, you can click the Log button in the Data Importer window immediately after the import, or you can click the Log icon on the ribbon when you return to the Data view.

Printing the Price Update Log

To print a copy of the log, click the Print button located at the top right of the Import Data log.

Tip: You can also copy the log, and paste it into an Excel file.

Interpreting the price update log

The Import Data log has three parts:

• Summary information
• Errors found
• Records matched

The following sections explain each part in more detail.

Summary information

The summary section shows:

• The number of rows in the price update file that match records in the database.
• The total number of rows in the price update file.
• The number of database records that match criteria in the price update file.
• The total number of database records.
• The number of item records that were updated.
  

Note:

• Each updated record is counted only once, even if it was updated multiple times or if it was updated to match the initial value(s).
• The count is increased for each record that is linked to an updated item record.

• The number of fields updated in the database. This count is increased:

• Each time a field, or a combination of fields, was updated for a given record. (Each update is counted, even if a field was updated multiple times.)
• Each time a field was updated to match the initial value(s).
• Each combination of field and record that is linked to an updated item record.

• A list of warnings about the number of database records that could not be updated because:
• The item record uses crews.
• The item categories specified in the price update file do not exist in the item record.
• The item record is linked to an item that uses a different price code.
• The price in the update file is not a-number.

Errors found

This section lists errors found in the update file during processing.

An error is reported for a row in the price update file if the row includes:

• A blank phase code.
• A blank item code.
• A blank price update code.
• A blank WBS code or WBS value.

An error is also reported there was no match in the database for a row in the price update file.

Records matched

This section lists the database records that matched the criteria in the import file. For each record that was matched, the list shows:

• A code to identify the record.
• The number of the row that was matched in the update file.
• The phase and item updated.
• The original and the updated category unit price.
• An indicator for linked prices.
• A note if the price update was skipped for one of the reasons in the warnings section.

[BCB:164:Chat Estimating US:ECB]

Getting Started with Database Editor (DBE SQL)