Making pretty database exports of HTML content

Not really .NET-related, but I thought I’d share it anyway:
Recently one of the account managers at my office asked for an Excel-sheet of all the e-mail templates for one of our application. He had already gotten one from a colleague, but it was just a straight dump from the query-result window of SQL Management Studio. It contained lots of HTML-tags and wasn’t readable for non-developers.

So, determined to spend more than 2 minutes and give him something useful, I took the following steps:
1. Fire up SQL Management Studio (in my case, the content was stored in an SQL Server database; use a management IDE of your chosing).
2. Run a SELECT-query of all the fields you want in your export. (note: by default the query-result window will only contain a limited amount of characters per cell; make sure you up that amount in the configuration-screen).
3. Copy/paste the query result to a new text file. All the cells should be TAB-separated.
4. Find/replace TAB with ‘</td><td>’.
5. Place a ‘<tr><td>’ at the beginning and a ‘</td></tr>’ at the end of each line*.
6. Surround it all with ‘<html><body><table>’ and ‘</table></body></html>’.
7. Open your file in Internet Explorer (other browsers don’t seem to copy the formatting the way IE does which would give problems in the following step).
8. Open Excel and paste the content from IE.
9. Voila, you now have an export of your database content which is manager- and customer-friendly.

*: In my case the cell-content didn’t contain newlines. If yours does, you might want to remove those or replace them with ‘<br />’ tags, depending on your needs.

This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *