Creating or Updating Models from Excel Files

Microsoft Excel is a powerful tool. I think I can say it excels in many areas, but… can it be used for database modeling? Yes and No.

As you probably know, Toad Data Modeler introduced support for import and export to Microsoft Excel in version 5.2 Kevin described the feature nicely in his blog article.

Let me give you more information about the functionality. The key requirement from our users was as simple as "Let me define comments in Excel". Nowadays, Microsoft Excel seems be used for other scenarios and use cases, for example for model updates and creation of new tables.

Toad Data Modeler allows you to add new objects via Excel, however, there are some limitations:

Target Database

Export and import can be used across models, but always for the same target database. That means, it is possible to export model for Oracle 10g and import the excel file to another Oracle 10g model. What should be avoided is Export from Oracle and Import to MySQL etc.

Example:

If you export physical model to Excel file:

The exported file will contain specifications for Oracle 10g database. See the sheets and headers:

If you export Logical model, the list of sheets and column headers will be completely different:

Data Types

See below a comparison of two excel files. One was created from physical model, the other from logical. On sheets Information – Data types you can find values that can be used in the same excel file on sheet Attributes.

If you try to use some non-supported data type, e.g. Clob is available in physical model and not logical, then import of the file to logical model will fail.

Recommendation: use only data types that you see on sheet Information – Data Types.

Locked Table Cells

In the excel file you can find various cell values in gray font color. Such items are locked and should not be modified.

The locked cells should not be modified, however you can easily unlock the sheet and for example deletethe ID values. That is OK in case you wish to create new objects (for such scenario ID must be empty).

New Items Creation

As you see, correct target database platform is required. In case you wish to create new objects via excel and start your modeling activity using Excel files, I recommend you to create new sample model and export the empty model to Excel. That will give you a list of supported/available data types and also
a list of object types and properties that can be defined in excel.

Good luck!