When people who know SQL use Excel

So I had some urgent data import to do, and DTS wasn't playing fair...
I have the following csv format:

contactid, telephone1, telephone2, mobilephone
GUID, number, number, number
GUID, number, number, number
GUID, number, number, number

and I needed it in a database... so sure, I could have got DTS working, or I could have used BCP to put the data in (though the biggest set is only 3000 lines)... or I could have written a dot net program to load the csv and import it to a table using a sqlbulkcopy (which i've done before for significantly bigger files) but..

Well, i'm ashamed to say, log growth and performance issues aside, that I did this:

=CONCATENATE("create table [",MID(CELL("filename"),59,LEN(CELL("filename"))-59+1),"] (id int identity(1,1) primary key, contactid uniqueidentifier, ",$B$1," varchar(1000), ",$C$1," varchar(1000), ",$D$1," varchar(1000))")

=SUBSTITUTE(CONCATENATE("insert into [",MID(CELL("filename"),59,LEN(CELL("filename"))-59+1),"] (contactid,",$B$1,",", $C$1, ",", $D$1,") values ('",A2,"','",SUBSTITUTE(B2,"'","''"),"','",SUBSTITUTE(C2,"'","''"),"','",SUBSTITUTE(D2,"'","''"),"')"),"'NULL'","NULL")

It's like I said... when people who know SQL use Excel.

prototype.js & Date.dateAdd / Date.dateDiff / Date.iso8601string

If you ever need dateAdd and dateDiff in javascript...
also included is a method to get the 2007-11-22T11:42:00.000 string for a date.

This is what I use.
This requires prototype.js

I'm pretty sure I didn't write the dateDiff/dateAdd methods, i'm just going through some of my source code and I was slack and didn't add references.
If you own this code, let me know and i'll add the appropriate references.

iso8601string

Date.prototype.iso8601string = function()
{
var out;
out = this.getYear() + '-' + this.getMonth().toPaddedString(2) + '-' + this.getDate().toPaddedString(2) + 'T' + this.getHours().toPaddedString(2) + ':' + this.getMinutes().toPaddedString(2) + ':00.000'
return out;
}

dateDiff
if you've ever used vbscript, or vb you should be familiar with this method. It gets the difference in units between two dates.

Date.prototype.dateDiff = function (ret,edDate) {
var start = this;
var end = edDate;
ret = ret.toLowerCase();
if(ret=='d') {
return Math.ceil((end.getTime() - start.getTime()) / (24*60*60*1000));
}
else if(ret=='h') {
return Math.ceil((end.getTime() - start.getTime()) / (60*60*1000));
}
else if(ret=='n') {
return Math.ceil((end.getTime() - start.getTime()) / (60*1000));
} else if(ret=='s') {
return Math.ceil((end.getTime() - start.getTime()) / 1000);
} else {
return Math.ceil(end.getTime() - start.getTime());
}
}

dateDiff
if you've ever used vbscript, or vb you should be familiar with this method. It adds a unit to a date, eg: the current date, plus a month.

Date.prototype.dateAdd = function(p_Interval, p_Number){
var thing = new String();
p_Interval = p_Interval.toLowerCase();
if(isNaN(p_Number)){
throw "The second parameter must be a number. \n You passed: " + p_Number;
return false;
}
p_Number = new Number(p_Number);
switch(p_Interval.toLowerCase()){
case "yyyy": {
this.setFullYear(this.getFullYear() + p_Number);
break;
}
case "q": {
this.setMonth(this.getMonth() + (p_Number*3));
break;
}
case "m": {
this.setMonth(this.getMonth() + p_Number);
break;
}
case "y":
case "d":
case "w": {
this.setDate(this.getDate() + p_Number);
break;
}
case "ww": {
this.setDate(this.getDate() + (p_Number*7));
break;
}
case "h": {
this.setHours(this.getHours() + p_Number);
break;
}
case "n": {
this.setMinutes(this.getMinutes() + p_Number);
break;
}
case "s": {
this.setSeconds(this.getSeconds() + p_Number);
break;
}
case "ms": {
this.setMilliseconds(this.getMilliseconds() + p_Number);
break;
}
default: {
throw "The first parameter must be a string from this list: \n" +
"yyyy, q, m, y, d, w, ww, h, n, s, or ms. You passed: " + p_Interval;
return false;
}
}
return this;
}

prototype.js & text caret/selection methods

I have an addiction, it's to the syntactic sugar of prototype.js, the excellent ruby-syntax style (i've never even looked at ruby) javascript library that's now up to version 1.6.

In a recent project (where I wrote some keypress input filter code, to filter to numeric inputs etc) I found that I needed to get the selected text in an input, so that I knew I didn't have to Event.stop the keypress as it would overwrite the selection anyway.
In another area in the same control library, I needed to get/set the text caret position, to allow me to detect when the caret was getting close to a maximum input length and 'jump' to the next box.
I found it took quite a bit of searching to locate the correct cross-browser methods of doing these tasks.

I've implemented these methods as standard javascript functions instead of element methods via prototype, though it's on my todo list to do this. They'd be better suited to be on the element themselves.

Without further ado, the code.
Get the selection in a text field
You must pass the element to it, in some browsers, it is not input-dependant, ie: will get the document selection as well.

function getSel(oField)
{
var txt = '';
var foundIn = '';
if (oField.selectionStart)
{
txt = oField.value.substring(oField.selectionStart,oField.selectionEnd);
}
else if (window.getSelection)
{
txt = window.getSelection();
foundIn = 'window.getSelection()';
}
else if (document.getSelection)
{
txt = document.getSelection();
foundIn = 'document.getSelection()';
}
else if (document.selection)
{
txt = document.selection.createRange().text;
foundIn = 'document.selection.createRange()';
}
else
{
return;
}
return txt;
}

Get Caret Pos (for a field)
Gets the caret position within a text input.

function getCaretPos(oField)
{
var iCaretPos = 0;
if (Prototype.Browser.IE)
{
var oSel = document.selection.createRange ();
oSel.moveStart ('character', -oField.value.length);
iCaretPos = oSel.text.length;
}else{
iCaretPos = oField.selectionEnd;
}
return iCaretPos;
}

Set Caret Pos (for a field)
Sets the caret position within a text input.

function setCaretPos(oField, iCaretPos)
{
if (Prototype.Browser.IE)
{
var oSel = document.selection.createRange ();
oSel.moveStart ('character', -oField.value.length);
oSel.moveStart ('character', iCaretPos);
oSel.moveEnd ('character', 0);
oSel.select ();
}else{
oField.selectionStart = iCaretPos;
oField.selectionEnd = iCaretPos;
oField.focus();
}
}