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.
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.
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??
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
- 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.