Bentley Communities
Bentley Communities
  • Site
  • User
  • Site
  • Search
  • User
  • Welcome
  • Products
  • Support
  • About
  • More
  • Cancel
AutoPLANT | OpenPlant | PlantWise
  • Product Communities
  • More
AutoPLANT | OpenPlant | PlantWise
AutoPLANT | OpenPlant | PlantWise Wiki How to Add Data from External Database in OpenPlant Isometrics Manager Backing Sheet
    • Sign in
    • -AutoPLANT | Bentley Raceway and Cable Management | OpenPlant | PlantWise Wiki
      • -OpenPlant
        • +CONNECT Services - OpenPlant
        • +Example Configuration - CONNECT Edition
        • +Installations - OpenPlant CONNECT Edition Applications
        • +OpenPlant Download Links & Videos
        • OpenPlant Product Compatibility
        • +OpenPlant Modeler CONNECT
        • +OpenPlant PID CONNECT
        • +OpenPlant Isometrics Manager CONNECT
        • +OpenPlant Orthographics Manager CONNECT
        • +OpenPlant Support Engineering CONNECT
        • +OpenPlant Project Administrator CONNECT
        • +OpenPlant CONNECT Edition-PlantSight Integration
        • +OpenPlant Modeler v8i
        • -OpenPlant Isometrics Manager v8i
          • +OpenPlant Isometrics Manager for OpenPlant
          • -OpenPlant Isometrics Manager for AutoPLANT
            • Installation/Upgradation Steps of OpenPlant Isometrics Manager for AutoPLANT
            • +Errors, Logs, Messages, Crashes - OpenPlant Isometrics Manager for AutoPLANT
            • +Open/Save - OpenPlant Isometrics Manager for AutoPLANT
            • +Processing - OpenPlant Isometrics Manager for AutoPLANT
            • -Settings/Attributes - OpenPlant Isometrics Manager for AutoPLANT
              • +Modifying Text and Annotation for AutoPLANT - OpenPlant Isometrics
              • +Modifying Symbols for AutoPLANT - OpenPlant Isometrics
              • +Modifying Dimensions for AutoPLANT - OpenPlant Isometrics
              • -Modifying DB Query: Usage, Logging, Debugging
                • Adding Data from The Process Table to Isometric Backing Sheet, Drawing Frame, Seed File
                • Creation of External Database Path Variable for Textnode.txt File
                • DB query for Multimode Project Environment
                • How to Add Data from External Database in OpenPlant Isometrics Manager Backing Sheet
              • +Modifying BOM for AutoPLANT - OpenPlant Isometrics
              • +Modifying Isometric Content for AutoPLANT - OpenPlant Isometrics
              • How to Append Tag Name of Instrument in Long Description
              • How to Change the Cursor Crosshair Color in OpenPlant Isometrics Manager
              • How to Enable OpenPlant Isometrics Manager to Read AutoPLANT Spool Number
              • How to Get AutoPLANT Weld IDs in OpenPlant Isometrics Manager
              • How to Get Model Name(filename) from AutoPLANT In Isometrics Using OPIM
              • How to Report the Reinforcing Pad Using Schema Customization in OpenPlant Isometrics Manager
              • How to Show Reserved Weld IDs in AutoPLANT - OpenPlant Isometrics
              • How to Show Spool Identifier and Test Port Instrument Bubble Tag Number In Isometric
              • How to Show User Label only for Reducing Branch Size
              • How to Skip THREAD or SOCKET Weld IDs in OpenPlant Isometric Manager
              • How to Suppress ‘UBR_STUB_IN’ in OpenPlant Isometrics Manager Bill of Material
              • How to Import Isometric Cells from One Project Cell Library to Another Project Cell Library
              • How to Make OpenPlant Isometrics Manager Styles Stored Globally for All Projects Using AutoPLANT Modeler
              • How to Label Pipe Support Tag
              • How to Use Grid Lines in OpenPlant Isometrics Manager for AutoPLANT
              • How to Use Isometric Cleanup When the Output Format is DWG for AutoPLANT
              • How to Add Material Property in PCF file
              • How to enable the specific Component Tags to be shown in Isometrics Using User Label
              • OpenPlant Isometrics for AutoPLANT settings for Project Isometrics
          • +Release Notes - OpenPlant Isometrics Manager
          • OpenPlant Isometrics Manager [FAQ]
        • +OpenPlant Project Administrator v8i
        • +OpenPlant PID v8i
        • +OpenPlant Orthographics Manager v8i
        • +OpenPlant Support Engineering v8i
        • +OpenPlant ModelServer v8i
        • +OpenPlant Reporting v8i
        • How to Connect with Bentley Technical Support Team using Microsoft Teams
        • How to Connect with Bentley Technical Support Team using Microsoft Teams via Browser
      • +Bentley AutoPLANT
      • +Bentley Navigator
      • +Bentley PlantSpace
      • +Plant Project Tools
      • +Bentley Raceway and Cable Management
      • i-model Composition Service for S3D
      • Working from home with OpenPlant
      • Working from home with Bentley Raceway and Cable Management
      • Working from home with AutoPLANT Modeler
    • How to Associate the Specification Based on Service in OpenPlant Modeler Connect

     
     Questions about this article, topic, or product? Click here. 

    How to Add Data from External Database in OpenPlant Isometrics Manager Backing Sheet

    Applies To
    Product(s): OpenPlant Isometrics Manager
    Version(s): 08.11.09.XXX (SS5) | 08.11.11.XX (SS6)
    Environment: Windows 7/8.1/10 64 bit
    Area: Settings/Attributes
    Subarea: DBQuery
    Original Author: Abhijit Bute, Bentley Product Advantage Group

    Background

    In general, the majority of data for Isometric output is gathered from the Piping table. However, situations can arise where data from external database could be valuable for placement on Iso's. The following illustrated queries demonstrate how to pull a field from the external database into Isometric backing sheet (Project Title Block).

    Steps To Accomplish

    Create New (Database) Excel Sheet Add Data.  

    1. Create new excel file (say User.xlsx) and entry as per below screen shot.


    2. Save file in appropriate location.

    Edit Textnode.txt file

    1. Add new database entry in textnodes.txt file.
      exdsn=Driver={Microsoft Excel Driver (*.xls)};Dbq=C:\User.xlsx;
    2. Add following query to get DRAWNBY, CHEKEDBY,APPROVEDBY values from database.
      DRAWNBY=DBQUERY($(exdsn), select INITIALS from [Sheet1$] where NAME='$(USERNAME)')Z
      CHECKEDBY=DBQUERY($(exdsn), select CHECKED from [Sheet1$] where NAME='$(USERNAME)')
      APPROVEDBY = DBQUERY($(exdsn), select APPROVED from [Sheet1$] where NAME='$(USERNAME)')

      Refer below image


    3. Save textnodes.txt file.
    4. Launch OpenPlant Isometrics Manager and verify result. 

    NOTE: In case the column queried from the Excel sheet contains different data types like texts as well as numbers

    Convert all the numbers to text format.

    Steps To Accomplish:

    1. Select the column as shown leaving the merged cells


    2. Go to Data --> Text to Columns


    3. Select Delimited


    4. Select Tab

    5. Select Text


    6. Click Finish.
    7. Ensure all the fields with just numbers show up as text now.

    See Also

    Comments or Corrections?

    Bentley's Product Advantage Group requests that you please confine any comments you have on this Wiki entry to this Comments or Corrections?" section. THANK YOU!

    • Bill of Materials
    • 08.11.09.140
    • Settings/Attributes
    • 08.11.09.404
    • Plant SELECT Support
    • AutoPlant V8i
    • how to
    • 08.11.10.182
    • OpenPlant Isometrics Manager
    • AutoPLANT Piping
    • SELECTsupport
    • 08.11.09.417
    • AutoPLANT selections
    • Share
    • History
    • More
    • Cancel
    • Abhijit Bute Created by Bentley Colleague Abhijit Bute
    • When: Fri, Oct 10 2014 9:44 AM
    • Kestutis Mitkus Last revision by Bentley Colleague Kestutis Mitkus
    • When: Wed, Dec 19 2018 5:36 PM
    • Revisions: 10
    • Comments: 0
    Recommended
    Related
    Communities
    • Home
    • Getting Started
    • Community Central
    • Products
    • Support
    • Secure File Upload
    • Feedback
    Support and Services
    • Home
    • Product Support
    • Downloads
    • Subscription Services Portal
    Training and Learning
    • Home
    • About Bentley Institute
    • My Learning History
    • Reference Books
    Social Media
    •    LinkedIn
    •    Facebook
    •    Twitter
    •    YouTube
    •    RSS Feed
    •    Email

    © 2021 Bentley Systems, Incorporated  |  Contact Us  |  Privacy |  Terms of Use  |  Cookies