Drawing in Excel

I wrote a VBA module for Excel over five years ago for clients. It has a User Interface in Excel which imports CSV data from one of our software packages and then creates scatter plots on a separate worksheet using the draw capability native to most of the Office packages.
Trouble was that my 1700 line macro did not migrate to Excel 2007 without mishap. I was trying to find references on Google today to figure out what went wrong. These are few and far between as Draw is badly documented in books, online help and even on the Microsoft Website.
I stumbled across this 7-item set of blog posts by Doug Jenkins while looking which helped a great deal. These are an excellent introduction with intriguing and practical demos to help anyone use Draw in Excel 2007. The Animation post and download was really cool.
With Doug’s tutorials I have cleared up half of my problems so far.

Using Excel to create an html FAQ

Like Generating SQL from Excel, you can do the same for HTML files. One task I’ve automated is the building of Frequently Asked Question files. The kind with a short list of questions and these are linked below to the question repeated and answered.
Using a forumula you can enter whatever you want in the header, body and footer. I like to put all my plain text in columns on the left and then use one column to the right for the top of the faq and then another beside it for the answers.
Here’s an example formula for the title of the faq:
=".LT.title.GT."&B3&".LT./title.GT..LT./span.GT..LT./head.GT..LT.body.GT."
Note that .LT. means a ‘less than character’ and .GT.; a ‘greater than character’.
So then you only have to copy the formula results in the two right columns (first one –for the top– and then the next one) into a text file with an html or htm extension and you’re done.
One more tip is to remember to save the file with the right encoding (that’s the bottom dropdown in Notepad when saving) if you’re using Unicode characters.
I’ve put the faq spreadsheet in a google spreadsheet and made it available to everyone here if you’d like to try it out. It saves me a great deal of time!
Let me know if you can think of any improvements or if the google doc doesn’t work. The google doc does require a supported browser to be able to select individual cells and the formulae won’t show up because this is exported from Excel as text into a google doc but I think you get the general idea. If you’d like the original spreadsheet comment away and I’ll send it to you.

Improved SQL Server Index Scripting from Excel

I covered Index Scripts using Excel in an earlier post but I was obliged to make my spreadsheet do more over the past few workdays. Basically I needed to allow multiple indexed fields and multiple included fields. The following solution works for any situation between 0 and 3 of both.
My spreadsheet contains the following columns (so ‘Table’ is in cell A1):

• Table
• Field1
• Order1
• Field2
• Order2
• Field3
• Order3
• Include1
• Include2
• Include3
• Notes (this field isn’t necessary but I like to use to remind me how I decided to use this particular index)
• MultiField
• MultiInclude
• Index Name
• Insert
• Drops
• Reorgs
• Rebuilds

Your table name, of course, goes in the Table column (cell A2) and likewise we put in up to three Fields to combine for your index and their respective orders (ASC or DESC). Any included fields (only included in the leaf portion of the index and so contribute less size to the index) go in the the three Include columns. Those fields or includes you don’t use are left blank.
To break down the work a little I have the formulae broken up into the MultiField, MultiInclude, Index Name and Inserts columns.
Here’s the MultiField formula:

=IF(ISBLANK(B2),””,IF(ISBLANK(D2),”[“&B2&”] “&C2,IF(ISBLANK(F2),”[“&B2&”] “&C2&”,[“&D2&”] “&E2,”[“&B2&”] “&C2&”,[“&D2&”] “&E2&”,[“&F2&”] “&G2)))

The MultiInclude:

=IF(ISBLANK(H2),””,IF(ISBLANK(I2),”[“&H2&”]”, IF(ISBLANK(J2),”[“&H2&”],[“&I2&”]”, “[“&H2&”],[“&I2&”],[“&J2&”]”)))

The Index Name:

=”perfindex_”&A2&”_”&IF(ISBLANK(B2),””, IF(ISBLANK(D2),B2, IF(ISBLANK(F2),B2&”_”&D2,B2&”_”&D2&”_”&F2)))&IF(ISBLANK(H2),””, IF(ISBLANK(I2),”_incl_”&H2, IF(ISBLANK(J2),”_incl_”&H2&”_”&I2,”_incl_”&H2&”_”&I2&”_”&J2)))

And the Insert:

=”create nonclustered index [“&N2&”] on [dbo].[“&A2&”] (“&L2&”) “&IF(ISBLANK(H2),””,”include (“)&IF(ISBLANK(H2),””,M2)&IF(ISBLANK(H2),””,”) “)&”with (statistics_norecompute = off, sort_in_tempdb = off, ignore_dup_key = off, drop_existing = off, online = off, allow_row_locks = on, allow_page_locks = on) on [primary];”

The Drops, Reorganize and Rebuilds fields are for Index administration.
Formula for Drops:

=”drop index “&A2&”.”&N2&”;”

Formula for Reorgs:

=”alter index “&N2&” on “&A2&” reorganize;”

Formula for Rebuilds:

=”alter index “&N2&” on “&A2&” rebuild with (fillfactor = 90);”

Generating Index scripts for SQL Server 2005 in Excel

So, I go further in my series of blogs on automating SQL Server 2005 scripting. This time its the tool of the performance sensitive DBA: the nonclustered index. These are pretty straightforward and should be easy to implement.  The first formula is there to delete a previous version of the index should it exist.  In your Excel sheet I have the table name in column A and B has the field or column name. Here are the formulae for columns D and E (I leave column C for notes):

=”if exists (select * from sys.indexes where name=N’index_”&A4&”_”&B4&”‘) drop index “&A4&”.index_”&A4&”_”&B4&”; ”

=”create nonclustered index [index_”&A4&”_”&B4&”] on [dbo].[“&A4&”]([“&B4&”] asc) with (pad_index = off, statistics_norecompute = off, sort_in_tempdb = off, ignore_dup_key = off, online = off, allow_row_locks = on, allow_page_locks = on) on [primary];”

with “tbltest” and “foreignid_fk” in the A and B cells I get the following results:

if exists (select * from sys.indexes where name=N’index_tbltest_foreignid_fk’) drop index tbltest.index_tbltest_foreignid_fk;

create nonclustered index [index_tbltest_foreignid_fk] on [dbo].[tbltest]([foreignid_fk] asc) with (pad_index = off, statistics_norecompute = off, sort_in_tempdb = off, ignore_dup_key = off, online = off, allow_row_locks = on, allow_page_locks = on) on [primary];

Note that I intentionally leave out the “drop_existing” clause in the index create. It can lead to errors and doesn’t seem to be very useful.

Using Excel to generate Inserts for SQL Server

Much of my day job involves database design, maintenance and manipulation. I use SQL scripts to quickly build my databases from scratch. In order to test them I need piles of data and find it is often too much work to generate all those inserts manually or figure out sqlcmd or osql code to import bulk data.
The best programmers, as many say, are lazy and the quickest way I’ve found to generate the inserts is to use Excel. If any of the following isn’t clear please attach a comment and I’ll be glad to explain in further detail.
Doing everything with scripts is certainly doable but the changes and administration can be a real pain. So I use Excel formulae to generate the data insert scripts for me. The first step is to lay my data out in columns in a spreadsheet. A quick way to do this using SQL Server Management Studio is to choose the option that allows the headers to be exported along with the data and then just copy the current data from a database table into a spreadsheet using the clipboard. Skip a column (or use it for notes) and then type something like the following formula in it:
="insert into tblyourtablename (yourkeyID_pk, intmine, strval) values ("&A4&", "&B4&", N'"&C4&"')"
Now you’ve got your insert statement for a table with your primary key (PK), an integer and a unicode string. Now all you need to do is to use Excel’s fill tools to copy data and the formulae down the rows and then just copy the whole column and insert into a SQL script. Including PK’s requires statements like
set identity_insert tblyourtablename on
and
set identity_insert tblyourtablename off
to bracket your insert statements. But if you want to avoid that hassle, you can skip the PK’s as I do in this statement:
="insert into tblyourtablename (intmine, strval) values ("&B4&", N'"&C4&"')"
Of course, if you skip the PK’s then you need to keep any foreign keys you may be using straight. How do you do that you might ask? It’s all doable. You simply arrange the order of things if you like to keep the PK’s. So your data insert script would have lookup table inserts at the top, and then those for tables with foreign keys later.
Or you get fancy and make your scripts relative (you can use your inserts regardless of whether there’s data already present or no) by using variables. In this way if you have a set of tables that rely on each other you could chain the insert statements together with semi colons and local variables for the foreign keys. Here’s an example:
declare @intpane int;
="insert into tblyourtablename (intmine, strval) values ("&B4&", N'"&C4&"'); set @intpane = scope_identity(); INSERT INTO tblpane (nameid_fk,strtext,bitmine,vbarmine) VALUES (@intpane ,N'"&D8&"' ,0 ,convert(varbinary,''));"
Bear in mind that you must copy the above into your SQL script by selecting the entire block if you have formulae in multiple columns.
There are a few tricks to keep in mind.

• The text designator in SQL is the ‘ whereas it is the ” (double quote) in Excel.
• Sometimes you need to insert text that contains a ‘ and that can really mess your code up so you need to put two ‘ together in that data column. For example the name O’Brien needs to be entered in the data column in Excel as O”Brien (this is easily done with a simple search and replace).
• Remember to enter “””” if you need to print a ” with Excel.
• Date/time fields are inserted like text in SQL (they use the ‘ too, like in ‘1956-09-01 13:24:16.000’, I find it easier to use 000 for the milliseconds because in SQL they go up by 3.33 and so always round to end in 0, 3 or 7).