SQL Server Management Studio and Macros

There’s a plugin available to add macros to SSMS, but I don’t need more than about 1 functionality: Convert a results column I just copied to a Comma Separated Values (CSV) list.  Usually I select the text, run a Search & Replace, change the options to enable “Selection Only” and “Regular Expression”, type in \n\r, and , and then run the search.  That’s kind of a hassle.  If it’s just a small section of text, I’ll even do ye olde “end, comma, delete, repeat” carpal tunnel exercise.  But I hate that, and I wanted something better.

I knew about AutoHotKey previously but didn’t know it could modify clipboard contents. So today I made some magic: Now Win+, (as in Comma Separate) is defined in the script below to:

• Search my clipboard contents for Carriage Return + Line Feeds and replace them with Commas.
• It also handles the case where I want the values wrapped in single-quotes.
• It also sorts and removes duplicates from the list.

It’s bound to Win+, in the script, and you can change that – it’s commented.

To install this, download and install AutoHotKey, then save the script below somewhere on your computer and double-click it to load it in memory (so Win+, will work).  You might also put it in your Startup folder so it’s loaded every time you log in.

Usage:

1. Make sure the script is running
2. Select some text containing line feeds – or a column of SSMS Grid results
3. Copy it
4. Hit Win+,
5. Answer the question about single quotes (Include them or No.. or Cancel?)
6. Paste it

Lines-To-CSV.ahk

; Lines-To-CSV.ahk
; 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, ",")
quote := 0
Loop, parse, test, `,
{
if A_LoopField is not number
{
	quote := 1
	break
}
if a_index > 10
	break
}
	
If quote > 0
    replacement := "', '"
else
    replacement := ", "

; 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

; 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 . "'"

; 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
	
; Done!
    return