Formatting Data Type in Modeler Export file.

Toad Data Modeler
1

In the Toad Modeler’s export.xlsx file under the Attributes tab the Data Type column is represented with 2 parameters like “Varchar2(%p1 %p2)” where the values for p1 and p2 are broken out into separate columns. Is there a way to bring back the parameters so the Data Type can be displayed as one column value such that it would appear like "Varchar2(32) or Number(10,0)?. Having the separate parameter columns is awkward.

2

Hello Rebecca,

Unfortunately, it is not possible for users to customize exported files like this. However, you can modify the file afterwards in Excel to make it look like you want. If you think more users would want this customization to be implemented, you can put your idea in the Idea Pond.

Note that Export to Excel function should be primarily used to add/edit Comments and Notes and to import the modified file back into TDM. It is possible to add/edit other objects (Attributes, Entities…), but it is highly recommended to do such changes in Toad Data Modeler itself, as you may encounter inconsistency errors when importing the file back to the application.

If you’d like to customize the exported file using Excel, I can create a Visual Basic macro for you which does exactly what you need. You would only need to execute it every time you open an exported Excel file.

Regards,

Lukas

3

Lukas,
Yes, in this case I do only need the Excel output for reading/presentation purposes, so a macro for formatting the data type column would be appreciated. Thank you.
Regards,
Rebecca Hryniewich
-----Original Message-----
From: Lukas Knapek [mailto:bounce-LukasKnapek@toadworld.com] Sent: Friday, June 26, 2015 4:38 AM
To: tdm@toadworld.com
Subject: [EEMSG: Marketing]RE: [Toad Data Modeler - Discussion Forum] Formatting Data Type in Modeler Export file.
RE: Formatting Data Type in Modeler Export file. Reply by Lukas Knapek Hello Rebecca,
This would require us to somewhat refactor our internal Excel exporting code, it cannot be done by users themselves. However, it is possible to customize the result file in Excel itself.
What are you planning to use the Excel file for? Note that the Export to Excel functionality is not meant to be used as a tool to edit values in Excel file and then import it back into TDM. Comments and Notes are the only exception to this rule. If you only need to customize the Excel file for reading/presentation, I can create a Visual Basic macro which does exactly what you need. You would only need to execute it in the exported file.
Regards,
Lukas
To reply, please reply-all to this email.
Stop receiving emails on this subject.
Or Unsubscribe from Toad Data Modeler - General notifications altogether. Toad Data Modeler - Discussion Forum Flag this post as spam/abuse.

4

Hello again Rebecca,

Download the attached file and follow these steps:

  1. Open your exported excel file and display Developer tab.

  2. Switch to the Review tab and make sure to **Unprotect Sheet ** (and Workbook, if it is protected). Otherwise, the macro won't work.

    4745.Macro1.png
    4745.Macro1.png1297×942 128 KB

  3. Now switch to the Developer tab and click the Visual Basic button.

    0131.Macro2.png
    0131.Macro2.png1297×942 127 KB

  4. A new windows opens, right-click Microsoft Excel Objects and choose Import File.

    Macro3.png
    Macro3.png1366×768 51.1 KB

  5. Navigate to the file you downloaded from this post and import it. You can now close the Visual Basic window.

  6. In Developer tab, click Macros and you should see the imported macro as seen on screenshot. Click Run to execute it.

    Macro4.png
    Macro4.png1298×943 162 KB

  7. A new column should appear - Data Types with Params, which contains the Data types and both of their parameters. Data Type and Data Type Params 1/2 should be deleted.

    Macro5.png
    Macro5.png1297×942 180 KB

I hope this is what you needed.

Regards,

Lukas
DataTypeParameters.bas (1.4 KB)

5

Lukas,
Thank you for creating a macro and providing the steps for using it to solve this problem. Unfortunately, for security reasons, the .bas file was removed by NMCI and replaced with the following message " Removed attachment: DataTypeParameters.bas"
Would it be possible for you to give me the code lines in a txt file or just include them in the email message. Then I will create the .bas file myself?
Regards,
Rebecca Hryniewich
-----Original Message-----
From: Lukas Knapek [mailto:bounce-LukasKnapek@toadworld.com] Sent: Wednesday, July 01, 2015 7:31 AM
To: tdm@toadworld.com
Subject: [EEMSG: Marketing]RE: [Toad Data Modeler - Discussion Forum] Formatting Data Type in Modeler Export file.
RE: Formatting Data Type in Modeler Export file. Reply by Lukas Knapek Hello again Rebecca,
Download the attached file and follow these steps:

  1. Open your exported excel file and display Developer tab .
  2. Switch to the Review tab and make sure to Unprotect Sheet (and Workbook, if it is protected). Otherwise, the macro won’t work.
  3. Now switch to the Developer tab and click the Visual Basic button. 4. A new windows opens, right-click Microsoft Excel Objects and choose Import File.
  4. Navigate to the file you downloaded from this post and import it. You can now close the Visual Basic window.
  5. In Developer tab, click Macros and you should see the imported macro as seen on screenshot. Click Run to execute it.
  6. A new column should appear - Data Types with Params, which contains the Data types and both of their parameters. Data Type and Data Type Params 1/2 should be deleted.
    I hope this is what you needed.
    Regards,
    Lukas
    Attachments:
    DataTypeParameters.bas To reply, please reply-all to this email.
    Stop receiving emails on this subject.
    Or Unsubscribe from Toad Data Modeler - General notifications altogether. Toad Data Modeler - Discussion Forum Flag this post as spam/abuse. EEMSG Message:
    The following attached file was dropped from the original message because it violated the executable file attachment policy:
    DataTypeParameters.bas
6

Hello again,

I have attached the macro code in .txt format to this post.

Also, please reply to posts on the forum itself. I assume you reply to the notification email, which unfortunately creates a new thread every time.

Regards,

Lukas
DataTypeParams.txt (1.43 KB)

7

Lukas,
Thank you, the macro worked.
Regards,
Rebecca
-----Original Message-----
From: Lukas Knapek [mailto:bounce-LukasKnapek@toadworld.com] Sent: Thursday, July 02, 2015 1:54 AM
To: tdm@toadworld.com
Subject: [EEMSG: Marketing]RE: [Toad Data Modeler - Discussion Forum] Formatting Data Type in Modeler Export file.
RE: Formatting Data Type in Modeler Export file. Reply by Lukas Knapek Hello again,
I have attached the macro code in .txt format to this post.
Also, please reply to posts on the forum itself. I assume you reply to the notification email, which unfortunately creates a new thread every time.
Regards,
Lukas
Attachments:
DataTypeParams.txt To reply, please reply-all to this email.
Stop receiving emails on this subject.
Or Unsubscribe from Toad Data Modeler - General notifications altogether. Toad Data Modeler - Discussion Forum Flag this post as spam/abuse.

8

Hello,

I ran the macro posted in this thread to format the data type in modeler export. I am getting the attached error message. Please advise. @Lukas.Knapek

Thanks1214 Macro Error.pdf (87.5 KB)

9

@Lukas.Knapek It worked now… I did unprotect sheet and then ran the macro.