Search code examples
exceldatabase-designexcel-2010diagramvisio

Create Visio Database Diagram from Excel Table and Field List

I have an Excel worksheet, which has the following columns: Table Name Field Name Data Type

My goal is to import this into Visio, and have it generate the table shapes, for each table, filling them with each field/data type.

I can then draw the relationship lines and arrange everything.

I've seen posts here and on other sites, about importing Excel, but it seems to be the data, not the data plan.

Anyone know how to do this? Please let me know if you need more info.

I'm using Office 2010.

Thank you!

Solution

  • I create an excel spreadsheet and fill in the info for these columns:

    tOrder - Table order (in case you need to sort back and forth, you can get to the order that makes sense for the db structure you are outlining)

    sOrder - Field order within the table.

    sTable - Table name

    sField - field name

    DataType - SQL valid data type (including length if needed)

    IsIdentity - Y if identity column

    Max Length - A Note to identify length while outlining tables and fields - length should go in data type as e.g. VARCHAR(300)

    Script - Once everything is filled in the 2nd row (providing you have the field labels above, in the same order on row 1, and table is sorted by A then B) you can put this formula:

    ="CREATE TABLE "&C2& " (" &D2 &" "&E2&" primary key IDENTITY(1,1) NOT NULL,"

    For every table field, but the last field, you can put this script formula:

    =D3&" "&E3&","

    For the last field of each table, put this (adjusting the row number to the right row):

    =D16&" "&E16&")"

    You can drag down the 2nd formula for a table, and just change the comma in the last field's formula to a close parens.

    Now copy that whole column, except the header row, and paste it in a new query, in a new empty SQL database, in SSMS. Run it and your database is created.

    Now you can create a new SQL schema, add in all the tables, and simply drag relationships between tables. You can also use Visio tools to connect to the DB, but I found the SSMS diagram to be sufficient. HTH.