This tool provides a solution to the problem of sending results from SQL Server Management Studio to my co-worker, without copying the results with headers, launching MS Excel, pasting into Excel, formatting in Excel, copying from Excel, pasting into the email to be sent. The Excel to SQL transformation takes the Excel input and makes it simple to paste that Excel data into a SQL #Temp table, which can then be used to import or analyze the issue.
Note: You will need to "Update Packages" to get NuGet dependencies before compiling.
Introduction
This Windows desktop application, while running, monitors your clipboard, and allows you to alter the contents before you paste. This can be quite powerful, for example, copying a range of data in Excel (unformatted) and then transforming it into a pretty table to be pasted into an email. Or, splitting a string of numbers and getting a distinct, sorted subset for analysis on paste. One of my favorites is copying the top row of a SQL result set from MS SQL Server Management Studio, and transforming it into a column list to make selecting and re-arranging the columns easier. The most powerful one is taking an Excel sheet and transforming it into a SQL temp table script. All with the push of a button.
Background
Repetitive tasks drive me nuts. After having to alter text to make it fit some need over and over and over again, I finally realized what I need to do is be able to programmatically alter the text in my clipboard without having to go to some intermediate tool.
For example, without this tool, to send results from SQL Server Management Studio to my co-worker, I would copy the results with headers, launch MS Excel, paste into Excel, format in Excel, copy from Excel, paste into the email to be sent. That's a lot of busy work just to display a table of data. Given I was doing that many times a day, I needed a solution, and this is that solution.
Conversely, I would regularly get Excel workbooks from users asking me to import/export/explain some piece of data. To get that into our production database on the other side of the firewall was a pain. The Excel to SQL transformation takes the Excel input and makes it simple to paste that Excel data into a SQL #Temp
table, which I can then use to import or analyze the issue.
Where the real power is in your ability to write your own transformations in XML or JSON. In fact, most of the transformations that come in the download are in the XML or JSON format!
After using this tool for several years, and sharing with my co-workers, it seemed a proper time to share with the rest of the world. Enjoy!
Using the Code
This posting is more about making the tool available for others to enjoy. To use, launch the executable, copy something into your clipboard, select the transformation(s), apply and paste. That's it! For common transformations that you use every day, you can create a shortcut macro on your desktop (or quick launch bar) to apply the transformation with a single click. See the Help section of the application for more details. For more details on creating your own transformations, see Sample.json and/or Sample.xml in Transmogrifiers directory.
Key Features
- Transform Text in Your Clipboard! Out of the box, several transformations are provided, like: trim, to UPPER CASE, to lower case, to Title Case, from/to list options, list sort/distinct options, character replacements, remove HTML tags, escape XML, and many more.
- Easily Create Custom Transformations to transform your text data however you like, in most cases without needing to write new application code. Create your transformation using simple text configuration files in XML or JSON formats. Files must use the .xml or .json extension and live in or below the /Transformers/ directory. See Sample.xml and/or Sample.json for an example file-based transformer (a.k.a. transmogrifier). You can use
TransmogrifierConverter
(in the UnitTest
project) to convert between XML and JSON as desired. - Batch Transform: As of version 9.0, you can now drag/drop text files into the preview window (top part of the application) to do a batch transformation of each file dropped. All selected transformers will be applied to each dropped file as if you had opened each file and copied its contents into your clipboard.
- Import/Export Transformers: You can drag text-based transformers (.json/.xml files) to the list of transformers to import. Right click on a transformer to export.
- Create Quick Transformation Macros on your desktop to re-apply common transformations with a simple click.
- Favorites & User Specific Settings
Your most often used transformations bubble up to the top to make the app easier to use. The number of favorites to save is adjustable in the app.config. The favorites file is user specific in the user’s %APPDATA% folder, which opens up opportunities to save other user-specific details. See Favorites
(class) for details.
- Shell Hooks as of v8.0, you can install windows shell integration hooks that allow you to access favorites more easily.
- In-Line Unit Testing: Since you can add a new transformer just by dropping a text file in a folder, it made sense that we should be able to just drop the unit tests in as well. Both coded transformers and transmogrifiers can have their unit written tests in line. See any of the Transmogrifier XML files for examples, or Sample.xml for more details. To run the tests, run the application with either the /DEBUG or /TEST startup flag, or run the unit test
InLineUnitTestRunAll
.
- Configurable Groups: Group names and sort order are defined in the configuration file Groups.xml. You can add/remove/re-arrange groups as you see fit. There are still constants (
GroupSortOrder.CannedGroups.*
) that map to values in the config file for use in coded transformers in case you need them, but they are not required. A group's sort order is determined by the order in which it appears in the Groups.xml file. Items with an unknown group are added to the top or bottom of the list based on the app.config setting UNKNOWN_GROUPS_TO_TOP
. - 1-to-1 Transformation Matrix: Many transformations were simple character replacements (comma with tab, tab with space, etc.). There is a list of common replace characters (CharReplacers.json) that will resolve to a grid of all possible replacements. This was a long journey where I experimented with all sorts of options, notably a grid (example below), but I found it more complicated to use than the solution presented of just including all permutations in line. See
CharReplacerLib
for most of the relevant code.
- Filter/Search: Start typing to filter transformers. Backspace/Delete to clear. Current filter and the count of matches shown in the upper right corner of the transformer list. Filter will match on any word (e.g., "
aaa BBB
", will match on "Aaa
or Bbb
").
- Color Coded Group Names: Groups can define a color for the header (made for color coding the favorites and 1:1 matrix transformations easier).
Compound Transmogrifiers
: One transmogrifier can call another before/after the current execution. You will need to be mindful of recursive calls (A calls B calls A) to prevent unexpected transformations. See CommasToVerticalQuotedTrimmedList.xml for an example of this feature. Hopefully, this allows for easier construction of more complicated transformations?! Note the change of adding TransformClipboard.PostLoadedInit
so transformers can be aware of each other, and TransformReferenceTransformations
to execute the transformation(s). - Debugging Window: Access the debugging window by launching with the
/DEBUG
command line argument. This can be useful for diagnosing issues with a failed transformation. Double click to see results in a text editor.
Exploring the Code
This application is a standard WPF desktop application that is, for the most part, unremarkable. However, if you are interested in extending the code to meet your own needs, it would be helpful to understand some of the moving parts.
Overview
The general design is a ViewModel
bound WPF application. On startup, TextTransformerBase.AllTransformers
uses several methods to scour the code and file system for transformers to add to the list of transformers available in the application. If in DEBUG
or TEST
mode, we will attempt to run unit tests on any loaded transformers. On transform (Apply
), we capture the user's clipboard, and apply the transformations (calling Transform(ref text)
on each selected transformer in the order it was selected), then write the result back to the clipboard, finally closing the application. The preview feature basically does the "Apply
" without writing the results back to the clipboard, and will be disabled if the text data in the clipboard is too big as it slows the app way down.
As with most WPF applications, we start in App.xaml.cs, in Application_Startup
, we parse the command line to see if we are running in UI mode (display a list of possible transformations) or command line mode (a canned list of transformers was passed in, so just transform and exit -- no UI). In either case, after we have loaded AllTransformers
, we wrap them in a ListBoxTransformerItem
, which keeps track of selection sort order, visibility, and other UI features, finally being added to an ObservableCollection
that is bound and rendered into the UI list.
On selection of any item, we DoPreview()
to display what the output of the transformation will look like. Ultimately, if the user clicks "Apply
", we basically just do VM.ClipboardText = VM.Transform();
and close the application.
Challenges
Problem 1: Integrating the Win32 components. This code was entirely grafted from the internet (clipboard-event-c-sharp) thanks to dbkk!
Problem 2: How to display the bubble for the right amount of time after the transformation, but before the application closes and removes the bubble and systray icon. Most of the logic around this is in MainWindowVM.DisplayTransformComplete
. If we pass in delegate function (onDoneHandler
), we wire it in to a 3 second timer to be called on done. In most cases, this basically calls VM.Quit()
to shut the app down. In any case, no matter what apply mode we are in, as soon as the "apply" starts, we hide the main window to make it look like the application has shut down while we display the popup bubble.
Problem 3: Desktop shortcut creation. This code was almost entirely taken from (here) -- Thanks! The two CreateShortcut
functions have a lot of app-specific logic to try and make the shortcuts meaningful (come up with a name that is not too insane, but still descriptive). This is likely code I will use again.
Problem 4: Serialization. Since the JSON/XML transmogrifiers could get somewhat complicated, deserialization of them got messy. I wanted to be able to support multiple serialization formats gracefully, balance hard coding with dynamic code, and balance what the user is wanting to do with enough of a safety net to prevent them from blowing things up. SerializationToolsFactory
seems to accomplish most of those goals. Also note the code in Tools.UnescapePseudoEscapeSequences
that really helped make writing transmogrifiers easier, notably in treating new line characters uniformly (\n == \r == \r\n
).
Problem 5: InLineUnitTests. Since users can add new code by just dropping in a text file (.json, .xml), there is not an easy way to test that their transformation works. With the InLineUnitTest
feature, they can add their own tests to the dropped file and have them run on startup to make sure the transformation is doing what they want. This was immensely helpful in debugging a lot of the transformers.
Problem 6: Supporting user-defined transformers. Take a look at Transmogrifier.Transform()
to get an entry point into the complexity. Where it gets hard is in needing a new feature (e.g., return results as upper case, or trimming the input, etc.). How do we determine where in the process that should happen? To try and make this more manageable, I tried to break the steps into clearly named functions (Step_*
). I did debate breaking each step into its own class, but that just made it harder to correlate changes – even if it is a better design.
Problem 7: Admin Install. Adding in the installer for the Shell Hooks feature (v8.0) proved to be uniquely challenging because of User Access Controls (UAC). When running as an Admin in Visual Studio, everything worked fine, but when running the install as my actual user account, it was not able to copy files to the program files folder. I was unable to find an elegant solution to executing a single function with elevated permissions, so the solution was to launch a silent instance of the app with Admin permissions and an /INSTALL (or /UNINSTALL) switch. It works, but is not very pretty. Better ways to solve the problem are most welcome.
Problem 8: No Frills. In 8.0, there was enough new code that was not necessary for basic functionality that I was annoyed. As such, there is a preprocessor directive of #FRILLS that will enable a lot of that new code. Conversely, compiling without it will yield a smaller executable with reduced features.
Problem 9: Help Images. The embedded help uses the WebBrowser
control to render a string of HTML to display the help. However, I was never able to (easily) put images in the help since no matter what reference I would use to the image, I would get security failures on web page load. The solution: embed the image as Base64-encoded data in the HTML. Check out HelpHelper.EmbeddedImage
class for details.
The remaining code that is noteworthy is really the view model (MainWindowVM
) and the transformer code.
Points of Interest
BlankInVisibiltyConverter
: Handy converter for hiding something when the text value is blank.
ClipboardFormat
: There are some transformers that return HTML. HTML in the clipboard gets tricky. You have to provide these index positions of where the pasted HTML lives, which is somewhat cumbersome to compute. If you need to return an HTML result, I would recommend you use HTMLClipboardHelper.HTMLToClipboardFormattedText
to provide the result.
ClipboardAssist
& MainWindowVM.ClipboardText:
Clipboard assist is largely borrowed from (here), but the dispose was still challenging. I have tried to correct it with the invoke call, but it is a pretty ghetto solution. Likewise, when addressing very large bodies if clipboard text, there was a odd race condition, so there is a retry loop in ClipboardText
’s getter that seems to work around it, but again is pretty ghetto code.
Desktop Shortcuts: I have used this app for years, and I always just create macro shortcuts to it by hand, but that's pretty lame. So with the help of the internet (create-shortcut-on-desktop-c-sharp) and thanks to Simon Mourier, I was able to wire in a simple button to do so. See ShortcutHelper.CreateShortcut
for the fun code.
Disable Preview: While working on this post, I had cause to transform a very large SQL result set (> 100 MB). It was SLOOOOW! After a little debugging, I realized that the issue was solely with the rendering to the UI. As such, I have added a "Disable Preview" option that will trigger if it thinks the amount of data in your clipboard is too big. The setting can be tweaked in the app.config using the PREVIEW_CUTOFF_KB
setting. Set to 0
to disable the feature. The preview is disabled and the text in the clipboard is too big, a warning is displayed.
Event Log: Meaningful, but non-crashing, errors are logged to the Windows Event Log. This can be helpful when debugging transformer issues when you don't have a debugger attached.
Favorites
: A generic ranked cookie class for bubbling favorites to the top. I have needed this sort of functionality a lot, and I will likely use this again.
GroupSortOrder
: This seemed a practical solution for a user configurable arbitrarily re-sortable list of items. This is likely code I will use again in the future in some manner or another.
Help: I worked hard to have the help be included in-line in a seamless way, which is why TextTransformerBase
has a HelpText
property. The styling and other details are defined in Help.html, which is what is ultimately rendered out to the help window. There are tags that swap out data in the HTML help file, including embedded file references to make showing concepts easier. I will definitely use this code again.
InLineUnitTest
: Given we are really just testing text transformations, the unit testing of that is pretty simple string before/after pair testing. I think this solution worked out well for this use case and lends quite a bit of durability to custom transformers.
PropertyStateChangeEventArgs
: When passing an event argument for a property changed, I was surprised that I couldn’t find a simple class to show the old/new values (it probably exists, I just couldn’t find it). This is a nice little class that helps with that.
SerializationTools*
: There is some fun code to auto detect what kind of serializer to use and balance that with coded defaults. The whole stack of code in the SerializationTools
folder is stuff I will likely use again and refine over time.
SysTrayBalloonTip
: Wrapper class for display balloon tip system tray messages. Handy. The tricky part is the shutdown and when to close, and this solution seems to be working reasonably well.
HelpHelper
: This uses pseudo tags to swap out complex objects (files and images) in the help web page. It seems to work pretty well and I will likely use it again.
OcrTextTranform
: A user reached out to me to request this custom transformer, which was fun to put together, and seems to work well for him. It finds/replaces regular expression values based on some additional criteria. The part that was interesting is that if it finds duplicate matches, it would try to replace twice, which was killing performance. The fix: group by the unique value to get the distinct list of matches! It was a clean and handy solution to the problem that I will certainly use again.
Implementing Custom Transformers
For the most "bang for your buck", it will be easiest for you to write your own file-based transformers. I did try to write the code so you can just include another assembly (.dll) below the application root and if it contained a transformer, you could use it. This proved somewhat problematic when it came to security, and that code is currently commented out (TextTransformerBase
- #if false
section).
There are a few options to create your own transformers.
Option 1 - Transmogrifier (XML or JSON file-based Transformers)
On startup, Transmogrifier.GetAllTransmogrifiers
parses the application’s “Transmogrifiers” directory (and sub directories) for *.xml and *.json files that contain file-based serialized transformations (transmogrifiers). See Sample.xml or Sample.json for full details. The biggest advantage is that there is no need to re-compile code, just create the file, include it in the directory and restart the app. Note that you can also include unit tests in the file as well, again avoiding the need to actually compile code. For details on how the transformations happen and all the options available, see Transmogrifier.Transform()
. Some of the currently coded features include:
- Specify output case (lower, UPPER, Title, None)
- Split by
char
s, string
s or regular expressions - Find and Replace by
string
or regular expression, including found regular expression patterns (see TransmogifierReplaceTest
for an example) - Regular expressions now support additional options (like toggling case sensitivity)
- Input is always assumed to be a "grid" (rows x columns), where a single
string
is treated as a single cell in the grid. This added WAY more flexibility in transforming complex data. You can split rows and columns in different ways, act on the split cells, and re-assemble them in different ways. See Sample.xml for details. - Canned options to make several operations easier and giving you more flexibility in the transformation process. Including:
- When and how to trim text (Per row? Per cell? Etc.). See options:
TRIM_ON_SPLIT_ROW,TRIM_ON_SPLIT_COL, REMOVE_EMPTY_ROWS, REMOVE_EMPTY_CELLS
and SKIP_MASK_LAST_CELL
for more details. - How to deal with different types of line breaks. (Can treat
\r
, \n
and \r\n
all the same!) See option: NORMALIZE_NEW_LINES
for more details. - How to deal with different types of spaces. (Can treat
Ascii(32)
the same as
!) See option: NORMALIZE_SPACES
for more details. - Row based operation like sorting, top and distinct. See options:
DISTINCT_ROWS, SORT_ROWS, GRIDIFY
and the property TopRows
for more details. - I found working with XML escape sequences challenging, especially for new line and tabs. I have created an option of
USE_COMMON_ESCAPE_CHARS
that will pre-process the XML file swapping out \n
, \r
, \t
and \l
(custom tag that maps to Environment.NewLine
) with their XML escape sequences. It just made working with the transformation files easier.
- Support for raw XLST transformations. Assuming the input is valid XML, you can just apply an XSLT transformation. An included sample is used to convert the XML help text generated by Microsoft Visual Studio into HTML. Thanks to Emma Burrows for Simple XSLT stylesheet for Visual Studio .NET XML Documentation.
- Group is inferred based on directory structure, unless explicitly set. It just felt more natural that way.
Transmogifier
process is broken into several steps to make working with the code easier. See Transmogifier.Transform
for details. - Two stage sorting. Primary sorting of list items is by group, ordered in the order in which they appear in the Groups.xml file. If a
Rank
is specified, it will be used to position items within the group, the finally sorting by name.
Option 2 – Character Transformations
For simple 1-to-1 character transformations (e.g., “spaces to quotes” or "comma to tab”), you can add the character to the list of known characters in CharReplacers.json.
Option 3 - Custom .NET Transformers
To create your own custom transformer, create a class that implements the abstract
base class TextTransformerBase
. The semantics are pretty simple (I hope), where the constructor defines the basics (name, group, description, ...), and then overrides Transform (ref string str)
with your custom code.
Example
public class ToUpper : TextTransformerBase
{
public ToUpper() : base
(
GROUP_SORT_ORDER.STRING_CASE,
"To Upper",
"Make all characters UPPER case."
) { }
override public void Transform(ref string str)
{
str = str.ToUpper();
}
}
Note: There are checks in place to ensure str
is never null
, so you should just be able to write whatever transformations you desire.
Version History
- Version 9.1 (22nd February, 2021): Minor changes
- Improvements to the OCR text tranformer
- Simplified adding Unit Tests to coded tranformers
- When normalizing spaces, include many more spaces per Wikipedia (link)
- When normalizing new line, include a few more new line characters per Wikipedia (link)
- Recompiled using .NET 4.7.2
- Version 9.0 (2nd January, 2021): Drag and Drop New Transformers, Batch Convert, Better Messaging
- Drag and Drop a text transmogrifier file (.xml or .json) on to the list of transformers to install it
- Drag and Drop text files to the preview window to do batch transform
- Right click a text file backed transformer in the list to export to a file
- Image embedder for HTML Help file
- More clear alerts and notifications
- Additional transformers
- Help with pictures
- Version 8.2 (9th December, 2019): More System Generated Char Transformations
- CharReplacers.json now is aware of Dashes (including hyphens, minus, long dash, etc...) and Underscores
- Added new
CharReplacer
default action of Remove
, thanks to my wife!
- Version 8.1 (19th October, 2019): Trim Chars
- Since we have the common
char
s in the CharReplace.json, we might as well support trimming them as well as replacing. Done. Thanks Andy! - App setting to toggle on/off system generated transformers. This was just easier than having to clear out the CharReplacers.json and DateFormats.json files.
- More granular system group names
- Minor bug fixes and code shuffle
- Version 8.0 (9th October, 2019): Shell Integration, Date Formats, #FRILLS
- Shell Integration! New menu bar offering to install/uninstall "Shell Hooks". When installed, right click on the app shortcut, the app itself or desktop background, and a context menu allows you to select from your favorite transformations. Special thanks to Mauricio Díaz Orlich for the ShellIcon.cs code, and Dave Kerr for the SharpShell library, which I have now used on a few projects -- Thanks! When installed, the app is copied to a fixed location in the Program Files directory, which makes it easier to wire into the shell and still work with the code.
- Given the shell integration comes with a lot of new code, I have introduced a preprocessor tag of #FRILLS. When frills are enabled, you get the shell install options, debugging, test support, and similar "bells and whistles". If you build without the #FRILL directive, that code is not compiled into the assembly and features are hidden. There are new canned builds to support this.
- Member 12949186 suggested a good idea of date formatters. This gets complicated, but I took a first stab at it following the
CharReplaceLib
pattern. The potential output formats are supplied in a config file (DateFormats.json). The tricky part will be parsing input in DateTimeReplaceTransformer.TryParseInputDate()
. Right now, the function is just a wrapper around the default DateTime.TryParse()
, but the hope would be we can expand it to support regex or other more vigorous parsing processes. PreferredAssembly
. With the shell integration, detecting what was executing the code became more difficult. Most places in the app now rely on Tool.PreferredAssembly
to "guess" on what codebase we actually want to look at (TransformClipboard.exe) as opposed to what is actually running.
- Version 7.2 (26th September, 2019): Minor Tweaks, New Transformers
- Unescape XML Transformer
- Assorted DB Transformers, breaking code into more reusable classes
AdditionalIndicators
, currently only supports "Beta"
- Version 7.1 (20th September, 2019): Speed and Bug Fix
- Minor speed fixes (notably caching the
TextTransformerBase GetHash
) - Fixed errors in the help file that were giving the wrong directions
- Version 7.0 (20th September, 2019): Speed Boost
- On checkbox click was listening to a generic property change event handler. This was firing too often for our needs. To fix, there is now a dedicated selection changed event handler to capture the change event, which seems to make things go quite a bit quicker.
- Shortcut created notification
- Shortcuts call transformations by code, not by name
- Added back in the preview disable feature (not sure when it went away!). This highlighted a nasty race condition, which I have tried to code a fix for, fingers crossed it works.
- Attempted to fix cross thread issue on dispose of clipboard assistant
- Embedded sample transmogrifiers in help file
- Double click debug window to open in text editor (I am surprised how much I am liking this feature!)
- Hardening Code. Visual Studio suggested some good edits to clean up the code. I took 'em.
- Version 6.2 (15th September, 2019): Typo Fixes, Requested Transformer
- Typo fixes throughout
- "englebart" requested a SQL parameter substitution transformer
- Version 6.1 (2nd September, 2019): New Features
- Favorites! Your most commonly used bubble to the top. Click favorites header to clear.
- User Specific Settings. Favorites stored in user-specific folder
- Search Filter Improved. Matches on any words (e.g. "
A B
", will match on "(A
or B
)).") - Compound Transmogrifiers! Can call other Transmogrifiers before/after the current transmogrifier.
- 1:1 Transformation Matrix. I realized a lot of my transformers were simple replaces (comma with tab, tab with space, etc.). There is a list of common replace characters that will resolve to a grid of all possible replacements.
- Color codes in list of transformers
- JSON Transformers. Why? Why not!
SKIP_MASK_LAST_CELL
option. When processing cells, optionally skip the last one, which is helpful for assembling comma lists leaving off the last comma. - Breaking Change - Re-organized many of the transformers to work better with the new features
- Bug Fixes
- Version 5 (6th March, 2019): Bug Fixes
- Fixed bug in Serializable Dictionary that was preventing some transmogrifiers from working correctly
- Added debugging aids to both the serialization processes and transmogrify unit tests
- Fixed bug in Strip HTML transformer
- Fixed bug in Replace Smart Quote transformer
- Breaking Change - Removed "
Root
" element from serializable dictionary by default. To fix your custom transmogrifiers, remove the and tags in your XML transmogrifies. - Added some fun on the help page
- Version 4 (5th February, 2019): Major Updates
- New Transformers
- Grouping in Config File
- In Line Unit Testing
- Filter/Find Transformers (just start typing)
- Declarative Item Type (made the code way easier to work with)
SerializationTools
broken out to own class (reusable!) - Debugging window. Appears by default in DEBUG mode, or show with
/DEBUG
command line switch. - Major Overhaul to XML-based Transformers (renamed to Transmogrifier)
- Version 3 (26th August, 2016): First public offering
- Version 1-2 (12th December, 2013): Untracked -- I just made it for me to use! :-)