Copy Data from Excel to SQL Server (or any DBMS) Script to generate Insert Statements in Excel

Unfortunately sometimes copy / paste from Excel into SQL server doesn’t work. Since moving data from Excel into SQL server (or another DBMS) can sometimes prove trickier than it should be I’ve created this handy script to help with just that. To get it to work:
1) Paste it into your excel project as a macro.
2) Select the sheet you want to copy
3) The first row of your sheet must contain the column names of the table to be inserted into.
4) Update the macro to use the correct table (look for TABLE_NAME_HERE)

That’s it! Put your cursor in the method and press F5. Upon completion you’ll get a message box with “complete” in it. Once the file is generated copy the contents of c:\temp\insert.txt into Management Studio / Toad / etc and run it.

Sub GenerateInsertRecords()
'Code thanks to
'Feel free to use, change and re-distribute the code you find on my site,
'all I ask is you leave these these comments intact!
    Dim i As Integer, ii As Double, iColumn as integer
    Dim str As String, strInsert As String, strComplete As String
    str = ""
    str = "insert into TABLE_NAME_HERE ("
    For i = 1 To 999
        If Len(Cells(1, i)) < 1 Then Exit For
        str = str & Cells(1, i) & ","
    Next i
    str = Left(str, Len(str) - 1)
    str = str & ") values ("
    Dim oConvert As Variant
    strInsert = str
    strComplete = ""
    For ii = 2 To 65000
        If Len(Cells(ii, 1)) < 1 Then Exit For
        strComplete = strComplete & strInsert
        str = ""
        For iColumn = 1 To i - 1
            oConvert = getInput(Cells(ii, i))
            str = str & oConvert & ","
        Next iColumn
        str = Left(str, Len(str) - 1)
        str = str & ")"
        strComplete = strComplete & str & vbNewLine
    Next ii
    Dim oFS As Scripting.FileSystemObject
    Set oFS = CreateObject("Scripting.FileSystemObject")
    Dim oText As Scripting.TextStream
    Set oText = oFS.OpenTextFile("c:\temp\insert.txt", ForWriting, True)
    oText.Write (strComplete)
    MsgBox ("complete")
End Sub

Function getInput(theInput As Variant) As Variant
    Dim retval As Variant
    If IsNull(theInput) Then
        retval = "Null"
    ElseIf UCase(theInput) = "NULL" Then
        retval = "Null"
    ElseIf IsNumeric(theInput) Then
        retval = theInput
        retval = "'" & theInput & "'"
    End If
    getInput = retval
End Function

Calculating PI, ASin, ACos in VB (VBScript, Excel, Word, Access)

I was recently surprised when I found out a number of numerical functions don’t exist in Access. Granted I believe they do exist in Excel, and probably word, but if you’re doing any coding you may find this useful anyway. Here is the code to accomplish all three:

'Code thanks to
'Feel free to use, change and re-distribute the code you find on my site,
'all I ask is you leave these these comments intact!
Public Function PI() As Double
    PI = 4 * Atn(1)
End Function

Public Function ASin(val As Double) As Double
    ASin = 2 * Atn(val / (1 + Sqr(1 - (val * val))))
End Function

Public Function ACos(val As Double) As Double
    ACos = PI / 2 - ASin(val)
End Function

If you’re wondering, Atn() IS built into Access, so these functions should work as is, they do for me!

iSeries (AS400) error messages

Every so often (mostly during development) iSeries will return an error number.  Generally I find they do nothing but confuse since the person using iSeries is a PC developer and knows little about the AS400 and the AS400 developer knows nothing about iSeries.

Next time you’re dealing with an obscure error from iSeries try looking for it in this document:

I found my error in there.

ICO Files – add a image to your application

I developed a great n-tiered application for my customer recently.  I put the webservices on their IIS server and ran my client showing how they could get data using click once installation and not have to manage the resources at all.

It was pretty awesome, I was proud of it and expected my client to be wowed.  Guess what their first comment was?
Them: “That icon is no good”
Me: “Uh, yeah, it’s just a demo app”
Them: “Can you fix that?  Oh, and the project name is no good either”
Me: “Yeah, again, demo app.  I’ll change those things for sure.  So, how do you like click once?”

So, I thought this was pretty amusing.  After that meeting I went out to get him an icon suitable to a business app and I found this site:
It’s a great resource with thousands of icons!  I’m sure you’ll find it a great resource on your next project.

Oh, and if you were wondering which icon I picked, I recommend the Tie Fighter.  Nothing says scheduling like a Tie Fighter 🙂

How to automate mapping network drives via script – VBS Logon Script – MapNetworkDrive

How to automate mapping network drives via script

My coworkers were constantly managing their clients mapped drives so I wrote a script to automate setting them up.  I wrote it both in VB6 and VBScript for those of you interested in seeing it written both late and early bound.  Here it is:

==== VBScript ====
(to use this put it in a file with a .vbs extension and then double click on it)


Sub Main()
    Dim sNetworkString
    sNetworkString = "I:,\\somenetworklocation\somefolder;
    Dim sNetworkArray
    sNetworkArray = Split(sNetworkString, ";")
    Dim sNetworkLine

    Dim objNetwork
    Set objNetwork = CreateObject("WScript.Network") 

    'To remove a network drive: call objNetwork.RemoveNetworkDrive("I:",true,true)
    Dim item
    For Each item In sNetworkArray
        sNetworkLine = Split(item, ",")
        On Error Resume Next 'This is here to ignore the drive errors if they 
        'already exist
        objNetwork.MapNetworkDrive sNetworkLine(0), sNetworkLine(1)
        On Error GoTo 0
End Sub

==== VB6 ====
(to use this put it in a VB6 forms or console app, add a reference to
‘”Windows Script Host Object Model” and compile)

Private Sub Form_Load()
    Dim sNetworkString As String
    sNetworkString = "I:,\\somenetworklocation\somefolder;
    Dim sNetworkArray() As String
    sNetworkArray = Split(sNetworkString, ";")
    Dim sNetworkLine() As String

    Dim objNetwork As WshNetwork
    Set objNetwork = CreateObject("WScript.Network") ' WshNetwork

    'To remove a network drive: call objNetwork.RemoveNetworkDrive("I:",true,true)
    Dim item As Variant
    For Each item In sNetworkArray
        sNetworkLine = Split(item, ",")
        On Error Resume Next 'This is here to ignore the drive errors if they already exist
        objNetwork.MapNetworkDrive sNetworkLine(0), sNetworkLine(1)
        On Error GoTo 0
    Next item
End Sub

If you want to make this smarter you could play with objNetwork.EnumNetworkDrives().  By using this collection you could bypass my messy method of ignoring already mapped drives by ignoring the error they throw.

How it works:
You may of noticed above there is a variable declared like so:
sNetworkString = “I:,\\somenetworklocation\somefolder;
This variable is key to this function working. If you want to map more / less / different paths all you need to do is add your own to this string. It is delimited by comma and semi colon, so this maps the X drive:
sNetworkString = “X:,\\mycomputer\afolder”
and this maps X and Y:
sNetworkString = “X:,\\mycomputer\afolder;Y:,\\anothercomputer\folder1\folder2”
Note that there is no semi-colon (;) at the end of the string. This is intentionally omitted because of the way Split() works.

If you use this script in your company I would love to hear about it! Feel free to post your comments here, I always enjoy hearing about other people’s experiences.

How to automate installing the .Net Framework, iSeries and Oracle Client

I created a batch file that checks to see if the .net framework 3.5, 4.0, iSeries and Oracle Client is installed and if not install them.  I found it pretty useful, here’s the code:

@echo off
if not exist %windir%\\framework\v3.5 (
echo Copying installer for .Net 3.5
xcopy “\\somenetworklocation\dotNetInstallers\dotnetfx35.exe” c:\dotNetInstallers\ /c /r /y /q
echo Installing .Net 3.5
c:\dotNetInstallers\dotnetfx35.exe /qb /nopatch /norestart /lang:enu
) Else (
echo .Net 3.5 already installed, skipping

if not exist %windir%\\framework\v4.0.30319 (
echo Copying installer for .Net 4.0
xcopy “\\somenetworklocation\dotNetInstallers\dotNetFx40_Full_x86_x64.exe” c:\dotNetInstallers\ /c /r /y /q
echo Installing .Net 4.0
c:\dotNetInstallers\dotNetFx40_Full_x86_x64.exe /norestart /passive
) Else (
echo .Net 4.0 already installed, skipping

if exist c:\dotNetInstallers (
rmdir c:\dotNetInstallers /S /Q

if not exist C:\ORACLE\Base\product\11.1.0\client (
echo Copying Oracle 11g .Net Data Adapter
xcopy “\\somenetworklocation\Oracle 11g ODAC and Oracle Developer Tools for Visual Studio\*.*” C:\OraInst\ /E /C /H /R /Y /Q
echo Installing Oracle 11g .Net Data Adapter
setup.exe -responseFile C:\OraInst\OracleInstallPrefrencesNew.rsp
rem setup.exe -silent -responseFile C:\OraInst\OracleInstallPrefrencesNew.rsp
) Else (
echo Oracle 11.1.0 client already installed, skipping
if exist c:\OraInst (
rmdir c:\OraInst /S /Q

if not exist “C:\Program Files\IBM\Client Access\READMESP.TXT” (
echo Installing IBM iSeries Driver
“\\somenetworklocation\iSeries Access Install Image w all Options\setup.exe” -s
) Else (
echo IBM iSeries Driver already installed, skipping

echo Installation Complete
echo NOTE: you may need to restart for the changes to take effect.

Thanks to symantec’s forum for the inspiration for this one:

===== ORACLE NOTES =====

Automating Oracle’s install requires an ini file of sorts.  The easiest way to generate this file is to run the install on a machine with the settings you’re interested in, like so:
setup.exe -record -destinationFile C:\OraInst\rec.rsp
Once you have that file you can use it to install like so:

setup.exe -silent -responseFile C:\OraInst\custom.rsp

There are some gotcha’s with this, one of note is the -responseFile option would not work for me when the file path had a space in it, even if I used the standard double quote escaping.  So
setup.exe -silent -responseFile "C:\Oracle Install\custom.rsp

wouldn’t work.
Also you can’t move the installer after generating it, if you do you will get a “Invalid staging area” error.  What I did to get around this was create a folder for installing, like:
and generated the file there.  Then in the batch file copy the install from a network share to this local dir and then run setup.
Some more info on this is available on informit’s website here:

===== iSeries NOTES =====

Automating iSeries works much the same as automating Oracle.  It creates an ini file of sorts when you run this command:
setup -r -f1d:\dir\file.iss
note that the flag "-f1" actually runs into the file and folder path, in this case: "d:\dir\file.iss". More here:
To then install the app on subsequent machines run this:
setup -s -f1d:\dir\file.iss -f2d:\dir\file.log
-s indicates a silent install, -f1 is the ini file and -f2 is where the install logs messages to.  If you name the iss file this:
setup.iss, and put it in the same dir as the setup app you don’t have to provide the -f1 parm.  This is because the setup app will look for setup.iss automatically. More here:

Date Time stuff

I write code in VB6, VB.Net, C#, VB Script, TSQL, Java, JavaScript, and I find I’m regularly scrambling for the proper date time code for each. I find myself asking “What on earth did they name THAT date function in THIS language again? I just did this X months ago, why on earth can’t I remember…”. So I finally decided to write some of it down. Hopefully you find it useful. My intention is to update this page as I use different features.

In SQL for Microsoft SQL Server:
How to get the Month / Day in the format “Aug20”:
select Left(DateName(Month,getdate()),3) + Convert(varchar(50),Day(getdate()))

In SQL for Oracle
How to get all the records created in the last 15 minutes:
select * from tableName where theDate > (sysdate – (15/(24*60))
How to view the full date when selecting columns:
Option 1) Run this statement:
alter session set nls_date_format=’dd-mm-yyyy hh24:mi:ss’
Then run any sql containing a date column after.
Option 2) Us the to_char() function:
select to_char(theDateColumn, ‘DD-MON-YYYY HH:MI:SS’) as aDate from theTableName

In VB6
Get the current Date / Time: Now()
Incrementing / Decrementing a date reliably, use the DateAdd() function, for decrementing just pass a negative parm.  This is the best method because it will automatically handle things like leap years, months with different days (30, 31, 28, 29), etc:
dim dDateVariable as Date
dDateVariable = DateAdd(“n”, -3, Now())
More on the Interval format here:

Oracle stores dates as a number, where hours, minutes and seconds are fractions of that number.  So a value of 1 = 1 day, and a value of 1.125 = 1 day and .125 of a day or .125 * 24 = 3 hours.  To get minutes you multiply the date by hours in a day (24) and then by minutes in a hour (60).  Since 24 * 60 = 1440 many examples out there just say to multiply 1440 to get minutes.
Thanks goes out to Mike for the math correction in my original post.