Create Visio Database Diagram from Excel Table and Field List

Question

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!

score 1 · Answer 1 · answered Jun 05 '17 at 21:22

1

From what I read, you're trying to create an entity relationship diagram. There's a template for these diagrams but I think only the professional version. Here's a first link: https://support.office.com/en-us/article/Create-a-Database-Model-also-known-as-Entity-Relationship-diagram-7042e719-384a-4b41-b29c-d1b35719fc93

answered Jun 05 '17 at 21:22

y4cine

396
2
20

That is close, but I wanted to take an excel spreadsheet, with a list of table/field names, and use that to have Visio create the database model. Doesn't seem to be a way to do that. I do have professional. – missscripty Jun 06 '17 at 11:49
That's right. The generation from Excel is not implemented so far. Do you know this topic: https://stackoverflow.com/questions/2488/auto-generate-database-diagram-mysql?rq=1 ? – y4cine Jun 06 '17 at 17:01
Yeah, I saw that. Really wanted to use Visio, so I could share work with colleagues. Thanks! – missscripty Jun 06 '17 at 20:30
1

You could on the other hand write your own solution in Visio using VBA. The question is whether it is worth it. (Starting from the template, you'd open a connection to excel, for each table drop by code an entity shape and for each table column add a field to the entity). – y4cine Jun 07 '17 at 05:52
That is interesting. It would take some development time, but could be turned into a procedure to run anytime I wanted to do this. Thanks for the tip! – missscripty Jun 07 '17 at 13:26

score 0 · Answer 2 · answered Jun 05 '17 at 17:58

0

I ended up creating a series of excel formulas, to generate CREATE TABLE statements. I ran those in a scratch SQL database. Now I'm using a new SQL diagram to map the relationships between the tables, then I will see if I can bring this into Visio.

answered Jun 05 '17 at 17:58

missscripty

529
2
11
30

score 0 · Accepted Answer · edited Jun 29 '22 at 09:04

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.

Create Visio Database Diagram from Excel Table and Field List

3 Answers3