Importing tables from Excel

Dataedo enables importing database schema (tables, views, and their columns) into its repository from external sources using an 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 the repository database with a database console to run the generated script

Excel import works only with the server repository.

How it works

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

Preparing metadata

1. Download the template (link below)
2. Prepare/extract a list of tables and columns of your data set (this is not covered in this tutorial)
3. Remove sample rows from the Excel template
4. Specify the documentation title in the B1 cell
5. Populate tables and columns in the Excel table. All tables and columns should be pasted into 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 the 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 the primary key. 1 - part of the 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 the custom_fields table in the Dataedo repository.

Importing

1. Enable macros if required.
2. Click the Generate SQL script button. The 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

• Excel import template for Dataedo 10.2