Archive for category VBScript (VBS)
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 https://anthonystechblog.wordpress.com '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) oText.Close 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 Else retval = "'" & theInput & "'" End If getInput = retval End Function
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 https://anthonystechblog.wordpress.com '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!
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)
Main Sub Main() Dim sNetworkString sNetworkString = "I:,\\somenetworklocation\somefolder; J:,\\someothernetworklocation\asubfolder;M:,\\onemorenetworklocation\something" 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 Next 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; J:,\\someothernetworklocation\asubfolder;M:,\\onemorenetworklocation\something" 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.
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
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: http://msdn.microsoft.com/en-us/library/cb7z8yf9
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.