ALT Highlighting in Word

Posted on March 20th, 2007 in MSOffice, Tools by Jay

Turn this..
“CHURCH”,0801 - Cathedral Catholic Parish
“CHURCH”,0811 - Casuarina Catholic Parish
“CHURCH”,0811 - Nightcliff Catholic Parish
“CHURCH”,0812 - Sanderson Catholic Parish
“CHURCH”,0822 - Daly River Catholic Parish
ALT Highlighting in Word
Into this…
Cathedral Catholic Parish
Casuarina Catholic Parish
Nightcliff Catholic Parish
Sanderson Catholic Parish
Daly River Catholic Parish

No big deal, i know, but sometimes you get stuck in the place. You know when you have a choice to make, do I do this manually for the next 5 minutes or do I write a script to do this for me. The script might take 5 minutes, but it might take 45 minutes also. This is a nice compromise when it suits.

If you have a list of data that you want truncate at the front or back (or middle) of each line, then you can do what I call ‘vertical highlighting’ in word. Paste the text into word, then while holding down the ALT key drag down through the text you want to delete. Hit delete and it’s gone.

What’s cool is that also works for formatting, so you could for example take a table of numbers (that’s separated with tabs as an example) and format a column with numbers.

Assembling SQL Statements Fast

Posted on March 20th, 2007 in Productivity by Jay

Today I had a task of taking a bunch of data supplied to me in tab delimited format and plugging this into a SQL database. There are of course a few ways to handle this sort of data, however one of things I do regularly is use string concatenation in Excel to build the insert statements required for a task like this.

So next time you are given a list of some sort and need to insert it into a database, try this:

  • Import the data into excel in whatever way you can. This might include copy and pasting, or just opening the file.
  • Parse the data. This is a fairly easy in excel, in fact there’s a little wizard to help you out. Under the data menu choose ‘text to columns’ and then follow the steps. The cool part is that at about step 3 you can choose to not import columns that contain useless data (as far your objective is concerned anyway).
  • Here’s the cool part, build your insert statement as a formula. Excel can do string concatenation by using the ‘&’ character in a formula, you then just need to join the text together to make up the rest of the statement.

The only tricky part is that excel expects the string literals (the textual part) of your formula to be enclosed in double quotes, and as you probably know SQL expects them to be in single quotes. So when it comes to joining this stuff together you get some odd looking statements. Once you get it right though, just copy and paste to do it over and over.

Of course, once you’ve done this bit you can then just auto fill down your spreadsheet for all your data then copy and paste across to query analyzer and execute.

SQL Insert Statement