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:
- Make sure the script is running
- Select some text containing line feeds – or a column of SSMS Grid results
- Copy it
- Hit Win+,
- Answer the question about single quotes (Include them or No.. or Cancel?)
- 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