| Applies To | |||
| Product(s): | InRoads | ||
| Version(s): | V08.11+ | ||
| Environment: | N/A | ||
| Area: | N/A | ||
| Subarea: | N/A | ||
| Original Author: | Bentley Technical Support Group | ||
How to Import Pay Items into a Database
All quantity formulas and pay items are stored in a pay item database, which is a Microsoft Access database.
A pay item database with a default set of English and metric formulas is delivered with InRoads. The following steps outline how to create a Pay Item database for InRoads V8i SELECTseries 2.
Acquire the pay items in a Microsoft Excel spreadsheet, text file or PDF document. Excel is preferred over text. If all else fails, a PDF file of pay items can be imported. If the file is PDF:
1. Open and use right-click to Select All and copy to the clipboard. Create a new text document, paste and save the file.
2. Format the text file for import into Excel.
This can be time-consuming, but you want Name, Description and Unit in the file. Use multiple spaces to add pipe symbols between the columns, assuming no multiple spaces are used in the description, which is not always the case. Each pay item must be on its own line.
3. Go to File > Open in Excel using the *.PRN, *.TXT, *.CSV filter so that the Text Import Wizard displays. Once loaded, save as an *.XLS file. Review the file for accuracy before moving on to the next step.
4. Create a column for Code and copy the NAME into the column. Copy the UNIT_NAME column to another for FORMULA. Arrange them in the following order using these headers:
Use Quantities > Pay Item Manager to create a Pay Item database in InRoads (File > New > Pay Item Database).
Open the Excel file with the pay items and use Data > AutoFilter to show all of the units that are in the pay items. With the Formula Manager in InRoads, create the formulas that are needed for the database. Close the database when finished.
Open the database with Microsoft Access. Go to the toolbar at the top and select Database Tools > Relationships. Between Category and Payitem there is a relationship. Right-click on the line between them and select Edit Relationship. Toggle off "Enforce Referential Integrity" and select OK. Close the Edit Relationship dialog.
In Access, go to the top and select External Data > Excel to open the Import wizard. For "Specify the source of the data field", Browse to your Excel file with the pay items. Under "Specify how and where you want to store the data in the current database", select Append a copy of the records to the table. In the drop down list, select PAYITEM and OK. The next dialog should have the columns separated correctly, as they were separated in Excel. Next. Finish. Either the data is imported successfully or it reports the number of records lost.
The number of records lost is caused by either duplicated pay item Names or empty fields in the spreadsheet. Cancel the wizard and return to the spreadsheet and look for the problems. When errors are corrected rerun.
Once you have imported the Excel spreadsheet, close the database. You can leave Access up since you will return to the program and then you can re-open the database using File > File History.
Open the database using Pay Item Manager. Now close it again.
Re-open the database in Access and go to the toolbar and reselect Database Tools > Relationships. Right-click on the line between Category and PAYITEM and select Edit Relationships. On the dialog toggle on "Enforce Referential Integrity" and the two toggles below it. Select OK and close the Relationships and then close the database in Access.
Return to InRoads and open the database using Pay Item Manager. You can now create categories for your pay items.
See Also
InRoads Product TechNotes FAQs And Support Video Clips
External Links
Bentley Technical Support KnowledgeBase
Comments or Corrections?
Bentley's Technical Support Group requests that you please confine any comments you have on this Wiki entry to this "Comments or Corrections?" section. THANK YOU!