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
If you’re using a tool like Ditto, that’ll save the before/after versions too, which can be handy.  I’m sure similar scripts can be created for other SSMS shortcomings, too.  Oh, and there’s nothing about this that limits you to using it in SSMS.  It’ll work in any program on whatever is in your clipboard- SSMS is just my use case.

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

Join the Conversation

1 Comment

  1. I was looking for something like this.
    And it introduced me to AutoHotKey – thanks

Leave a comment

Your email address will not be published. Required fields are marked *