Thursday, May 10, 2007

Export Visio Database Table Names to Excel

If you use the Enterprise Architect edition of Microsoft Visio for data modeling regularly, then there is a good chance that at some point you’ve wanted to export just the table names into Excel. You might want to do this to map logical ERD entities to physical data model tables, track project status by entity, or track overlap between database versions.

Regardless, it turns out to be non-trivial to perform this export, particularly if you are unable to generate to a database to retrieve the table names. The trick is to use the reporting feature of Visio, but there are many reports and report options, and you will need one that is table-based to get the data into Excel easily.

Note: If you are unfamiliar with the capabilities of Microsoft Visio as a data modeling tool you may wish to take a look at my Data Modeling in Microsoft Visio Tutorial.

Export Procedure

1. This may seem a little unusual, but if you don't have any comments in any of your tables (which really shouldn’t be the case), you will need to add comments for at least one of your tables. Without this step Visio will not display tables in a grid format in the report.

2. Now select the somewhat obscure "Report" item off of the "Database" menu.

3. Only the "Table Report" provides the ability to layout database tables in a grid. Select it and click Finish.

4. Under “Predefined logical/physical reports" Click the button labeled "Default To: General Report" and change it to "Default To: Database Report." This will remove tables formatted per page from the end of the report.

5. Under the "Attributes" tab select “Deselect All” then select the “Table stats summary” option.

6. Click “Export to RTF,” save the file somewhere, and open it with Microsoft Word.

7. (optional) If you have any new lines in the notes field you may have to replace them with spaces. Just do a search and replace for "^l" and replace with " ".

8. Now you’re ready to copy and paste.

And you’re done! Hopefully this tutorial will make life easier for you next time you need to export table names from Visio to Microsoft Excel.

13 comments:

Anonymous said...

I do not think this feature is available anymore under Visio 2007.

Lee Richardson said...

Well, I think it depends on which version of Visio 2007. The forward engineering and other features only come with the Team System version of Visio. I would have to imagine they still have some kind of reporting, but I haven't evaluated the full version of Visio 2007.

Anonymous said...

Hi,
I have a question to the tutorial "Data Modeling in MS Visio". I did reverse engineer an existing huge database. Now I would like to modularize the entities, for example by manually adding a table to the page, selecting it and using menu item "Show related tables". Okay this works so far.. but.. the related tables are all added at one spot on the page one upon the other.
Is it possible to adjust them automatically the best way??
And one more question: You seem to have also some modularized entities at your page, summarized by the background color. How are you doing this??
Regards,
Nicole Stabe

Alexei said...

For Visio 2007: Select an Entity in your model, Edit-> Open Entity, double click on table name or columns names, use menu bar or contextual menu to copy/paste necessary data

Anonymous said...

Do you think it's possible to do the reverse.....create visio ERD from Excel table definition?

Jayesh said...

what version of visio is used for showing the whole process. i am currently using visio 2007 and under the database menu i cant find the report option.

Lee Richardson said...

Anonymous: I seriously doubt you could import Excel data into Visio.

Jayesh: I use Visio for Enterprise Architects version 11 that comes with Visual Studio 2005 Team Suite. I don't use Visio 2007 because sadly it doesn't support forward or reverse engineering. There is a lot of frustration over Microsoft's apparent abandoning of this functionality. You can read more here: http://social.msdn.microsoft.com/Forums/en-US/vstsarch/thread/5e32089a-fa36-47a5-a912-3ecb1b374403

Anonymous said...

Hi - I have microsoft visio 2007 - reverse engineered just fine but I can't find any functionality to export to excel for table/ column names, etc. I don't seem to have the report feature - any ideas ?

Lee Richardson said...

Anonymous: Make sure you have the Visio verison for Enterprise Architects. I'm willing to bet you can't forward engineer either.

Lee Richardson said...

Nicole Stabe: When you import there should have been a checkbox to automatically organize the tables. It doesn't work all that well though so ultimately you'll have to organize it manually anyway. I did the background colors manually like any object in Visio.

Anonymous said...

Thans Alexei ... your comment helped me a lot

mdlueck said...

Thansk Alexei, you saved me retyping LOTS of database column names with your suggestion for Visio 2007 Professional. Ugly but retyping would have been uglier.

Mike said...

I have Visio 2007 still and reverse engineered a PostgreSQL database. Now I want to export table names. However, under my Database menu I don't have all the same menu options. I have:
- Reverse Engineer
- Show Related Tables
----------
- Refresh Model
----------
- View ->
- Import ->
----------
- User defined types
- Options ->

I have Reports under Data, but it still isn't the same as shown above. And I've run out of other ideas to get my table/column data.