How to not have numbers convert to Scientific Notation when inserted into DB by Automator

Leonid
Leonid New Altair Community Member
edited December 2020 in Community Q&A

Hello, 

In DPS is there a way to make sure that numbers that are displayed normally don't get converted to scientific notation when inserted into a database by Automator. Currently, the only way I found is to convert them to Text, which is not desirable.


#Automator #DPS #scientificnotation

​​​

------------------------------
Leonid Shteyngardt
App Development Manager
Canaccord Genuity Inc.
New York NY
(212) 389-8176
------------------------------

Tagged:

Answers

  • Mahmoud
    Mahmoud
    Altair Employee
    edited December 2020
    Hi Leonid,

    You can create the table schema in the SQL database table and configure columns (fields) based on their types.

    For example, numeric integer fields to Int, or Numeric decimal fields to Float.

    In the Automator Visual Process in Data Prep Export you can manually map each workspace table field to a table field in SQL.
    Regards
    Mo

    ------------------------------
    Mahmoud Abdolrahim
    Senior Implementation & Integration Engineer
    Altair Engineering
    ------------------------------
    -------------------------------------------
    Original Message:
    Sent: 12-03-2020 11:27 AM
    From: Leonid Shteyngardt
    Subject: How to not have numbers convert to Scientific Notation when inserted into DB by Automator

    Hello,

    In DPS is there a way to make sure that numbers that are displayed normally don't get converted to scientific notation when inserted into a database by Automator. Currently, the only way I found is to convert them to Text, which is not desirable.


    #Automator #DPS #scientificnotation



    ------------------------------
    Leonid Shteyngardt
    App Development Manager
    Canaccord Genuity Inc.
    New York NY
    (212) 389-8176
    ------------------------------
  • Leonid
    Leonid New Altair Community Member
    edited December 2020
    Hi Mo,

    We'd like to keep all columns in the tables that we load from Monarch as varchar, in case the input data is not perfect - alpha characters in numeric or date fields, etc. This way we at least load the data and then deal with cleanup in stored procedures.
    So, although DPS displays the large numbers correctly as they are in the PDF file - all digits visible, when we run the process in Automator,  it converts large (or small) numbers into scientific notation and thus losing information - 123456789 gets converted into 1E+8. To avoid this, we have to convert each field into Character - either in the Report Design or later in the table.
    It would be helpful if Monarch would not implicitly convert numbers to scientific notation when inserting into varchar columns.

    Thank you,
    Leonid

    The information contained in this communication may be confidential and/or legally privileged. If you are not the intended recipient, please contact the sender and destroy all copies of this message and any attachments. Any disclosure, copying, distribution or taking any action in reliance on this information other than by the intended recipient is strictly prohibited and may be unlawful. Canaccord Genuity LLC is required by regulation to review and store both outgoing and incoming electronic correspondence. E-mail transmissions cannot be guaranteed to be secure, timely or error-free. This communication is not an offer or solicitation to buy or sell any security or other investment product. Additional information, including disclosures regarding securities under research coverage, is available at http://www.canaccordgenuity.com/en/Our-Company/Research/. Any information regarding specific investment products is subject to change without notice. Canaccord Genuity LLC - Member FINRA/SIPC.



    -------------------------------------------
    Original Message:
    Sent: 12/3/2020 11:28:00 AM
    From: Leonid Shteyngardt
    Subject: How to not have numbers convert to Scientific Notation when inserted into DB by Automator

    Hello, 

    In DPS is there a way to make sure that numbers that are displayed normally don't get converted to scientific notation when inserted into a database by Automator. Currently, the only way I found is to convert them to Text, which is not desirable.


    #Automator #DPS #scientificnotation



    ------------------------------
    Leonid Shteyngardt
    App Development Manager
    Canaccord Genuity Inc.
    New York NY
    (212) 389-8176
    ------------------------------
  • Phil_20586
    Phil_20586 Altair Community Member
    edited December 2020
    Mahmoud said:

    Hi Leonid,

    You can create the table schema in the SQL database table and configure columns (fields) based on their types.

    For example, numeric integer fields to Int, or Numeric decimal fields to Float.

    In the Automator Visual Process in Data Prep Export you can manually map each workspace table field to a table field in SQL.
    Regards
    Mo

    ------------------------------
    Mahmoud Abdolrahim
    Senior Implementation & Integration Engineer
    Altair Engineering
    ------------------------------
    -------------------------------------------
    Original Message:
    Sent: 12-03-2020 11:27 AM
    From: Leonid Shteyngardt
    Subject: How to not have numbers convert to Scientific Notation when inserted into DB by Automator

    Hello,

    In DPS is there a way to make sure that numbers that are displayed normally don't get converted to scientific notation when inserted into a database by Automator. Currently, the only way I found is to convert them to Text, which is not desirable.


    #Automator #DPS #scientificnotation



    ------------------------------
    Leonid Shteyngardt
    App Development Manager
    Canaccord Genuity Inc.
    New York NY
    (212) 389-8176
    ------------------------------

    Leonid,

                  For financial values (for example) as Mo said I use the decimal feature when writing to the database.  However, if you read that DB field back into DPS and then perform add/sum/difference functions I have experienced where fractional parts of a penny still result from calculation reading in DB fields that had more decimal precision.  The two methods I have used to overcome this are: a) convert to text and then back to a number in DPS or b) use the round function within mathematical arguments. 

     

    Phil Magrogan

    Immersion - SDVOSB

     



    -------------------------------------------
    Original Message:
    Sent: 12/3/2020 11:48:00 AM
    From: Mahmoud Abdolrahim
    Subject: RE: How to not have numbers convert to Scientific Notation when inserted into DB by Automator

    Hi Leonid,

    You can create the table schema in the SQL database table and configure columns (fields) based on their types.

    For example, numeric integer fields to Int, or Numeric decimal fields to Float.

    In the Automator Visual Process in Data Prep Export you can manually map each workspace table field to a table field in SQL.
    Regards
    Mo

    ------------------------------
    Mahmoud Abdolrahim
    Senior Implementation & Integration Engineer
    Altair Engineering
    ------------------------------
    -------------------------------------------
    Original Message:
    Sent: 12-03-2020 11:27 AM
    From: Leonid Shteyngardt
    Subject: How to not have numbers convert to Scientific Notation when inserted into DB by Automator

    Hello,

    In DPS is there a way to make sure that numbers that are displayed normally don't get converted to scientific notation when inserted into a database by Automator. Currently, the only way I found is to convert them to Text, which is not desirable.


    #Automator #DPS #scientificnotation



    ------------------------------
    Leonid Shteyngardt
    App Development Manager
    Canaccord Genuity Inc.
    New York NY
    (212) 389-8176
    ------------------------------

Categories