My account Book a demo

Importing tables from Excel

Applies to: Dataedo 24.x (current) versions, Article available also for: 10.x, 23.x

Dataedo enables importing database schema (tables, views and their columns) into its repository from external sources using Excel template.

What is it for

You can use it to document databases and data sources which are not supported by Dataedo:

  1. Not supported relational databases
  2. Mainframe computers
  3. Excel, csv or txt files

Requirements

  1. Excel 2007 or later,
  2. Access to repository database with database console to run generated script.

Excel import works only with server repository.

How it works

Excel template generates SQL script that creates new documentation in the repository and adds tables and views specified in the table. The script must be then executed in Dataedo repository database (SQL Server).

Preparing metadata

  1. Download template (link below)
  2. Prepare/extract list of tables and columns of your data set (this is not covered in this tutorial)
  3. Remove sample rows from Excel template
  4. Specify documentation title in B1 cell
  5. Populate tables and columns in Excel table. All tables and columns should be pasted in the table as shown in an example. Each column is one row in the spreadsheet. Columns will be ordered in Dataedo in the same order as they appear in the spreadsheet.

Fields

  1. Documentation (B2) - Title of the documentation (top level element in Dataedo repository) that will be created. Each run creates new documentation.
  2. Schema - Table schema name. Can be left blank.
  3. Table name - Table name.
  4. Type - Type of object, table or view: TABLE/VIEW.
  5. Column name - Column name.
  6. Column title - Column title (optional). This field is later editable in Dataedo Editor.
  7. Data type - Data type including all required details. It is advised to specify string length in brackets - i.e. char(100). This is free text so any value (up to 250 characters) would be accepted.
  8. PK - Flag indicating whether the column is part of primary key. 1 - part of primary key, blank - otherwise.
  9. Nullable - Flag indicating whether the column is nullable. 1 - is nullable, blank - otherwise.
  10. Identity - Flag indicating whether the column is identity. 1 - is identity, blank - otherwise.
  11. Default value - Default value for the column. NULL if blank.
  12. Computed formula - Formula for computed columns. Overrides default value if both filled.
  13. Description - Column description (optional). This field is later editable in Dataedo Editor.
  14. CustomField_1 - CustomField_40 - Additional custom fields to fill. They have to be already created through Dataedo. You can find which field number matches which custom column in custom_fields table in Dataedo repository.

Importing

  1. Enable macros if required.
  2. Click the Generate SQL script button. Template will generate a script file named dataedo_table_import_[datetime].sql in your documents folder.
  3. Connect to your SQL Server repository database as a user with dbo permissions (e.g. the user that created the repository).
  4. Then execute the script from the generated file.

Your documentation has been created. Run Dataedo and connect to your repository (or hit Refresh view) to enjoy your new documentation.

Download template

Right-click the link below and choose Save link as... If you run into any issues, make sure the file extension is .xlsm

Contact support Report issue