OPIM 9.1 - How to populate textnodes from excel with padded spaces as characters?

History:  I have a working configuration that will pull data from an excel spreadsheet and populate textnodes in the isometric.

Problem:  It works great for regular text in the excel worksheet, but when the text requires padded spaces at the front of characters the result removes the padded spaces and adds the rest of the text.

I have tried Alt+0160 and that did not work either.

Any ideas?

  • Hi Dan,

    Thankyou for posting your Plant Technical Forum.

    Could you please share any image for this issue or the output you are getting.

    Regards,
    Ashutosh Patil

  • --> Drawing Attribute Evaluation Log

    --> LL_NOTE1 = DBQUERY ($(LL_DSN2), SELECT [NOTE1] FROM [LINEDATA] WHERE TAG_NO='$(LINENUMBER)' AND SHT_NO=$(SHT_NO))
    --> DSN = Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=C:\WORKING\CETEST\Config\OpenPlant\Isometrics\styles\CETEST_MM_CE\config\files\CETEST_MM_ISODATA.xls;HDR=YES;IMEX=1;
    --> SQL = SELECT [NOTE1] FROM [LINEDATA] WHERE TAG_NO='630P1001' AND SHT_NO=1
    --> result = 1000. NOTE1 TEXT
    --> LL_NOTE2 = DBQUERY ($(LL_DSN2), SELECT [NOTE2] FROM [LINEDATA] WHERE TAG_NO='$(LINENUMBER)' AND SHT_NO=$(SHT_NO))
    --> DSN = Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=C:\WORKING\CETEST\Config\OpenPlant\Isometrics\styles\CETEST_MM_CE\config\files\CETEST_MM_ISODATA.xls;HDR=YES;IMEX=1;
    --> SQL = SELECT [NOTE2] FROM [LINEDATA] WHERE TAG_NO='630P1001' AND SHT_NO=1
    --> result = NOTE2 TEXT2
    Want this result
    --> result =       NOTE2 TEXT2
    --> LL_NOTE3 = DBQUERY ($(LL_DSN2), SELECT [NOTE3] FROM [LINEDATA] WHERE TAG_NO='$(LINENUMBER)' AND SHT_NO=$(SHT_NO))
    --> DSN = Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=C:\WORKING\CETEST\Config\OpenPlant\Isometrics\styles\CETEST_MM_CE\config\files\CETEST_MM_ISODATA.xls;HDR=YES;IMEX=1;
    --> SQL = SELECT [NOTE3] FROM [LINEDATA] WHERE TAG_NO='630P1001' AND SHT_NO=1
    --> result = 1001. NOTE4 TEXT
    --> LL_NOTE4 = DBQUERY ($(LL_DSN2), SELECT [NOTE4] FROM [LINEDATA] WHERE TAG_NO='$(LINENUMBER)' AND SHT_NO=$(SHT_NO))
    --> DSN = Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=C:\WORKING\CETEST\Config\OpenPlant\Isometrics\styles\CETEST_MM_CE\config\files\CETEST_MM_ISODATA.xls;HDR=YES;IMEX=1;
    --> SQL = SELECT [NOTE4] FROM [LINEDATA] WHERE TAG_NO='630P1001' AND SHT_NO=1
    --> result = NOTE3 TEXT2
    Want this result
    --> result =       NOTE3 TEXT2
    --> LL_NOTE5 = DBQUERY ($(LL_DSN2), SELECT [NOTE5] FROM [LINEDATA] WHERE TAG_NO='$(LINENUMBER)' AND SHT_NO=$(SHT_NO))
    --> DSN = Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=C:\WORKING\CETEST\Config\OpenPlant\Isometrics\styles\CETEST_MM_CE\config\files\CETEST_MM_ISODATA.xls;HDR=YES;IMEX=1;
    --> SQL = SELECT [NOTE5] FROM [LINEDATA] WHERE TAG_NO='630P1001' AND SHT_NO=1
    --> result = 1002. NOTE5 TEXT
    --> LL_NOTE6 = DBQUERY ($(LL_DSN2), SELECT [NOTE6] FROM [LINEDATA] WHERE TAG_NO='$(LINENUMBER)' AND SHT_NO=$(SHT_NO))
    --> DSN = Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=C:\WORKING\CETEST\Config\OpenPlant\Isometrics\styles\CETEST_MM_CE\config\files\CETEST_MM_ISODATA.xls;HDR=YES;IMEX=1;
    --> SQL = SELECT [NOTE6] FROM [LINEDATA] WHERE TAG_NO='630P1001' AND SHT_NO=1
    --> result = NOTE6 TEXT2
    Want this result
    --> result =       NOTE6 TEXT2
    --> LL_NOTE7 = DBQUERY ($(LL_DSN2), SELECT [NOTE7] FROM [LINEDATA] WHERE TAG_NO='$(LINENUMBER)' AND SHT_NO=$(SHT_NO))
    --> DSN = Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=C:\WORKING\CETEST\Config\OpenPlant\Isometrics\styles\CETEST_MM_CE\config\files\CETEST_MM_ISODATA.xls;HDR=YES;IMEX=1;
    --> SQL = SELECT [NOTE7] FROM [LINEDATA] WHERE TAG_NO='630P1001' AND SHT_NO=1
    --> result = 1003. NOTE7 TEXT
    --> LL_NOTE8 = DBQUERY ($(LL_DSN2), SELECT [NOTE8] FROM [LINEDATA] WHERE TAG_NO='$(LINENUMBER)' AND SHT_NO=$(SHT_NO))
    --> DSN = Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=C:\WORKING\CETEST\Config\OpenPlant\Isometrics\styles\CETEST_MM_CE\config\files\CETEST_MM_ISODATA.xls;HDR=YES;IMEX=1;
    --> SQL = SELECT [NOTE8] FROM [LINEDATA] WHERE TAG_NO='630P1001' AND SHT_NO=1
    --> result = NOTE8 TEXT2
    Want this result
    --> result =       NOTE8 TEXT2

  • #-----------------------------------------------------------------------------
    # Drawing attribute definition file
    #
    # Each line hold a drawing attribute (DA) definition in the format
    #
    #   <DA name> = <expression>
    #
    #   <DA name>     the name of the drawing attribute
    #   <expression>  The expression is verbatim text that may contain
    #                 functions and macros.
    #
    #   When <DA name> starts with a '@' the variable will not be visible as
    #     drawing attribute in the isometric.
    #   Adding a statement DEBUG=1 will result in a verbose logging to the
    #     production log
    #
    #-----------------------------------------------------------------------------
    # $(<name>) resolves to the value of:
    #   - another drawing attribute
    #   - a configuration variable, example: $(IE_CONF)
    #   - an OS environment variable, example: $(NUMBER_OF_PROCESSORS)
    #
    #-----------------------------------------------------------------------------
    # *(<name>) resolves to the most common value of an attribute related to the
    #   components in the isometric
    #
    # Example:
    #   mostCommonRating = *(RATING)
    #
    #-----------------------------------------------------------------------------
    # Function: DBQUERY DBQUERY (<dsn>, <sql>)
    #
    #   <dsn> is an ODBC connection string
    #   <sql> Any SQL statement
    #
    #   Connection strings for almost every type of database can be found at
    #       http://www.connectionstrings.com/ 
    #   Connection string examples: (network or mapped drive)
    #       - Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=\\serverName\PSDS_Imperial\english.mdb;
    #       - Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=Z:\english.mdb;
    #       - Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=c:\tmp\excel\linelist.xls
    #
    # Example:
    #   IE_DSN = Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=Z:\temp\sample.mdb
    #   code   = 2605-2
    #   MyVar  = DBQUERY ($(IE_DSN), select * from code_desc where code='$(CODE)')
    #
    #-----------------------------------------------------------------------------
    # Function: DATE (expression)
    #
    # Return a user configurable date string
    #
    # In the expression certain character sequences are replaced:
    #   - yyyy  year as a four-digit number (2017)
    #   - yy    year as a two-digit number (17)
    #   - mmmm  month as text (January, February, April, etc)
    #   - mmm   month as text abbreviated (Jan, Feb, Apr, etc)
    #   - mm    month as a two-digit number (01-12)
    #   - m     month as a number (1-12)
    #   - dddd  weekday (Monday, Tuesday, etc)
    #   - ddd   weekday abbreviated (Mon, Tue, Wed, etc)
    #   - dd    day as two-digit number (01-31)
    #   - d     day as a number (1-31)
    #
    # Examples: (using date) August 4, 2017 (Friday)
    #   DATE (ddd mmm d yyyy)       Fri Nov 4 2017
    #   DATE (dddd mmm d yyyy)      Friday Nov 4 2017
    #   DATE (dddd mmmm d yyyy)     Friday November 4 2017
    #   DATE (ddd mmm dd yyyy)      Fri Nov 04 2017
    #   DATE (ddd/mmm\dd+yyyy)      Fri/Nov\04+2017
    #   DATE (dd/mm/yyyy)           04/08/2017
    #   DATE (dd-mm-yyyy)           04-08-2017
    #
    # Examples:
    #   Date = DATE (mm/dd/yyyy)        --> 03/16/2020
    #   Date = DATE (dddd, mmm d yyyy)   --> Monday, Mar 16 2020
    #
    #-----------------------------------------------------------------------------
    # Function: DATEODBC (odbcdate, expression)
    #
    # When a date field is returned using a query on an ODBC data source, the
    # return value is always in the format (2010-03-16 15:25:43) regardless of the
    # source formatting. This function provides an option to convert the ODBC date
    # part to a user specified format
    #
    # Example:
    #   odbc_date = DBQUERY ($(IE_DSN), Select [date1] from [SHEET1$])
    #   user_date = DateOdbc ($(odbc_date), mm/dd/yyyy)
    #
    #-----------------------------------------------------------------------------
    # Function: TIME (expression)
    #
    # Return a user configurable time string
    #
    # In the expression certain character sequences are replaced:
    #   - hh    hours (01-12)
    #   - h     hours (1-12)
    #   - HH    hours (00-23)
    #   - H     hours (0-23)
    #   - ss    seconds (00-59)
    #   - s     seconds (0-59)
    #   - tt    AM or PM
    #
    # Examples:
    #   time = Time (hh:mmtt) --> 01:23PM
    #   time = Time (HH:mm    --> 13:23
    #   time = Time (HHmmss)  --> 132355
    #
    #-----------------------------------------------------------------------------
    # Function: TIMEODBC (odbcdate, expression)
    #
    # When a time field is returned using a query on an ODBC data source, the
    # return value is always in the format (2010-03-16 15:25:43) regardless of the
    # source formatting. This function provides an option to convert the ODBC time
    # part to a user specified format
    #
    # Example:
    #   odbc_time = DBQUERY ($(IE_DSN), Select [time1] from [SHEET1$])
    #   user_time = TimeOdbc ($(odbc_time), HH:mm)
    #
    #-----------------------------------------------------------------------------
    
    
    
    # -------------------------  EXAMPLES ----------------------------------------
    #
    # Samples for data source definitions. Please note that he DSN itself may be
    # assembled from other drawing attribute
    #
    #  DSN_1 = Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=c:\temp\sample.mdb
    #  DSN_2 = Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=c:\temp\book1.xlsx
    #
    #  DSN_3 = Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=$(IE_CONF)/book1.xlsx
    #
    #  excel_driver = Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)}
    #  dbname = $(IE_CONF)style.xlsx
    #  DSN_4 = $(excel_driver);dbq=$(dbname)
    #
    # Use the @ to prevent exposing DSN_5 to the isometric
    #  @DSN_5 = $(excel_driver);dbq=$(dbname)
    #
    # DbQuery examples:
    #
    #  code = 2605-2
    #  desc = DBQUERY ($(DSN_3), Select description from code_desc where code='$(code)')
    #
    # Expression example:
    #
    #  msg = The description for code $(code) is $(desc)
    
    
    DATE = DATE (yyyymmdd)
    time = Time (HHmmss)
    
    debug=1
    
    LL_DSN2 = Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=C:\WORKING\CETEST\Config\OpenPlant\Isometrics\styles\CETEST_MM_CE\config\files\CETEST_MM_ISODATA.xls;HDR=YES;IMEX=1;
    
    LL_NOTE1 = DBQUERY ($(LL_DSN2), SELECT [NOTE1] FROM [LINEDATA] WHERE TAG_NO='$(LINENUMBER)' AND SHT_NO=$(SHT_NO))
    LL_NOTE2 = DBQUERY ($(LL_DSN2), SELECT [NOTE2] FROM [LINEDATA] WHERE TAG_NO='$(LINENUMBER)' AND SHT_NO=$(SHT_NO))
    LL_NOTE3 = DBQUERY ($(LL_DSN2), SELECT [NOTE3] FROM [LINEDATA] WHERE TAG_NO='$(LINENUMBER)' AND SHT_NO=$(SHT_NO))
    LL_NOTE4 = DBQUERY ($(LL_DSN2), SELECT [NOTE4] FROM [LINEDATA] WHERE TAG_NO='$(LINENUMBER)' AND SHT_NO=$(SHT_NO))
    LL_NOTE5 = DBQUERY ($(LL_DSN2), SELECT [NOTE5] FROM [LINEDATA] WHERE TAG_NO='$(LINENUMBER)' AND SHT_NO=$(SHT_NO))
    LL_NOTE6 = DBQUERY ($(LL_DSN2), SELECT [NOTE6] FROM [LINEDATA] WHERE TAG_NO='$(LINENUMBER)' AND SHT_NO=$(SHT_NO))
    LL_NOTE7 = DBQUERY ($(LL_DSN2), SELECT [NOTE7] FROM [LINEDATA] WHERE TAG_NO='$(LINENUMBER)' AND SHT_NO=$(SHT_NO))
    LL_NOTE8 = DBQUERY ($(LL_DSN2), SELECT [NOTE8] FROM [LINEDATA] WHERE TAG_NO='$(LINENUMBER)' AND SHT_NO=$(SHT_NO))
    LL_BIGNOTE = DBQUERY ($(LL_DSN2), SELECT [BIGNOTE] FROM [LINEDATA] WHERE TAG_NO='$(LINENUMBER)' AND SHT_NO=$(SHT_NO))
    
    SHT_NO = *(SHT_NO)
    
    CETEST_MM_ISODATA.xls

  • Hi Dan,

    In OPIM 9.1 and older versions there aren't any options. In the upcoming update 10 one can use a @ as a space placeholder. So if you want leading spaces you could use an Excel entry like: @    text. The same method can be used to get trailing spaces. For now you could try to change the textnode text justification to be right aligned (if that's an option)

    Regards, Frank

  • Thanks Frank to Confirm the scope!

    @Dan - I hope it is inline to your query. 

    Alternatively if you can define Multiple text node for each line with desired space then it prints accordingly. Much of work but then can achieve the results.

    HTH,

    Regards,

    Rahul Kumar

    Product Engineer – Global Technical Support | Community Moderator