AutoHotKey SQL Formatting Help

The script I posted back in (holy crap) 2012 has gotten several updates, and when I made the latest today, I decided to repost it, in case I need the updated version again later.

It binds to Win+, and when invoked presents these options for manipulating whatever text is currently in the clipboard:

1: Sort/De-Dupe (A, B, C)
2: Sort/De-Dupe + Quote (‘A’, ‘B’, ‘C’)
3: No Sort/De-Dupe (A, B, C, B)
4: No Sort/De-Dupe + Quote (‘A’, ‘B’, ‘C’, ‘B’)
5: SUM (A+B+C)
6: Prepend commas in row 2+ (A\n, B\n, C)

Use cases:
* You highlight a column of values in a recordset grid and want to copy/paste them into another select.
* You ended up with a recordset that contains duplicates and you want just the uniques without rerunning sql.
* You need to know the sum of a column without bothering to add a COMPUTE SUM() and rerunning.
* You ran sp_columns to get the list of fields in a table and now need to paste that as part of an insert to modify further.


; Lines-To-CSV.ahk
; AutoHotkey Version: 1.x
; Language: English
; Platform: Win9x/NT
; Author: A.N.Other
;
; Script Function:
; Search & Replace clipboard contents: Convert line feeds to comma+space
;
; Example:
; 1
; 2
; 3
;
; Becomes: 1, 2, 3
;

#NoEnv ; Recommended for performance and compatibility with future AutoHotkey releases.
SendMode Input ; Recommended for new scripts due to its superior speed and reliability.
SetWorkingDir %A_ScriptDir% ; Ensures a consistent starting directory.

#,:: ; bind to [Win]+[,]

; Grab the current clipboard contents. Call it haystack
haystack := Clipboard

; Define what we're splitting on: CR+LF
needle := "\r\n"

; Check to see if there's any lines to convert here.
FoundPos := RegExMatch(haystack, "\r\n")
if FoundPos = 0
{
MsgBox, 64, Convert Lines to CSV, Clipboard contains no lines to convert to CSV., 2
return
}

; Prompt user for quote option
;MsgBox 3, Convert Clipboard Lines to CSV, Converting Clipboard lines to CSV`n`nWrap lines in single quotes?
;IfMsgBox Cancel
; return
;else IfMsgBox Yes
; quote := 1
;else
; quote := 0

; Can we auto-determine quote option? Get the first 10 items in the list, if they're all numeric, no quotes.
; Can always save & tweak this script as a separate hot key to force quotes.
test := RegExReplace(haystack, needle, ",")
RunParam := 1
sample :=
Loop, parse, test, `,
{
if a_index < 4 { sample := sample . A_LoopField . ", " } if A_LoopField is not number { RunParam := 2 break } if a_index > 10
break
}

; Ok, so we can auto-determine, but a week later, I've got 4 variants of this script:
; +Sorting +Auto-Quoting (Default)
; -Sorting +Auto-Quoting
; -Sorting !Quoting (anti-quoting... reverse normal quoting)
; +Sorting !Quoting
; OOOh, cool: -Sorting, !Quoting, just Add a ", " in the front and don't replace line endings.

; And I can't remember which stupid key is assigned to which stupid script. I was better off with a prompt.
; But let's make it so the default prompt option is the one we want based on the quote test above.

; 0 or blank = QUIT
; 1 = Sort Quote
; 2 = Sort Reverse Quote
; 3 = No Sort Quote
; 4 = No Sort Reverse Quote
; 5 = SUM Numbers
; 6 = No Sort No Quote, just ", "

options:="
(
1: Sort/De-Dupe (A, B, C)
2: Sort/De-Dupe + Quote ('A', 'B', 'C')
3: No Sort/De-Dupe (A, B, C, B)
4: No Sort/De-Dupe + Quote ('A', 'B', 'C', 'B')
5: SUM (A+B+C)
6: Prepend commas in row 2+ (A\n, B\n, C)
)"

; Prompt user for quote option
InputBox, RunParam, Convert line to CSV, % options , ,375,220, , , , , %RunParam%
if ErrorLevel
return
else {
if RunParam = 1
{
vSort := 1
quote := 0
vAdd := 0
vPre := 0
}
if RunParam = 2
{
vSort := 1
quote := 1
vAdd := 0
vPre := 0
}
if RunParam = 3
{
vSort := 0
quote := 0
vAdd := 0
vPre := 0
}
if RunParam = 4
{
vSort := 0
quote := 1
vAdd := 0
vPre := 0
}
if RunParam = 5
{
vSort := 0
quote := 0
vAdd := 1
vPre := 0
}
if RunParam = 6
{
vSort := 0
quote := 0
vAdd := 0
vPre := 1
}
}

If quote > 0
replacement := "', '"
else
replacement := ", "

if vSort = 1
{
; Optional Sort & remove Dupes If you don't want sorting, comment this out.
; If we're using quotes, sort as text. If not using quotes, sort as numbers...
; Sort haystack, use linefeed as delimiter, remove duplicates
If quote > 0
Sort, haystack, U
else
Sort, haystack, N U
}

if vPre = 1
{
needle := "m)(\r\n)^(\s*)\b"
replacement := "$1$2, "
}

; Perform the RegEx find and replace operation,
result := RegExReplace(haystack, needle, replacement)

; If using quotes, the result string needs a first & last single quote added
If quote > 0
result := "'" . result . "'"

if vAdd = 0
{
; Empty the Clipboard
Clipboard =
; Copy the result to the Clipboard.
Clipboard := result
; Wait for the Clipboard to fill.
ClipWait

; Optional Paste:
send, {CTRLDOWN}v{CTRLUP} ;send normal paste command

}

; If adding, loop over the list, uh, adding.
If vAdd > 0
{
sumtotal := 0
Loop, parse, result, `,
{
sumtotal += A_LoopField

}

; Empty the Clipboard
Clipboard =
; Copy the result to the Clipboard.
Clipboard := sumtotal
; Wait for the Clipboard to fill.
ClipWait

}

; Done!
return

One reply on “AutoHotKey SQL Formatting Help”