Excel CSV output using something other than commas

While dealing with the ICD 10 codes, we found that the SQL import process was adding spaces to text fields because of the tab delimiters we were using. A naive attempt to use the CSV format was quickly killed because the ICD 10 descriptions include commas, as I already knew from using J.

Notepad++ choked on the massive find-and-replace operations (I should probably see about submitting a bug). While it would complete the operation, somewhere along the way it would corrupt the file.

While something like dnGREP would probably be the right tool for find/replace in massive files, I instead used Excel 2010 to output the CSV using the pipe character ( | ) as the delimiter.

This is a kind of secret, as Excel doesn’t ask you what delimiter you want to use. Instead, you have to globally change the delimiter for Windows:

1. Open the Control Panel and select “Region and Language”.

2015-07-09 15_16_16-All Control Panel Items

2. Click on “Additional settings…”

2015-07-09 15_17_03-Region and Language

3. Change the “List separator” from a comma ( , ) to the character you want.

2015-07-09 15_16_44-Customize Format

And then remember to do this all again when you’re done, because you probably want your default to stay a comma.

Excel CSV output using something other than commas

Paste through remote desktop connections

I have to tunnel through three remote desktop connections in order to run SSMS on a computer that is allowed to connect to a production SQL server. Somewhere in the tunnel I lose the ability to paste text from my local machine into the remote machine. Generally I’m just hunting for specific data, so it isn’t a big deal to compose new SQL for that purpose.

At other times, however, it’s a real pain having to retype a long query that I’ve already composed and run on a test instance. In those situations, I hit WIN + b, thanks to AutoHotKey:

#b:: ; Simulate typing of the text on the clipboard
KeyWait, LWin ; Ensure that the Windows key has been lifted.
KeyWait, RWin
ToPaste = %ClipBoard% ; Convert to text
Sleep 100
StringReplace, ToPaste, ToPaste, `r`n, `n, All ; Prevent CR+LF from causing two newlines
Sleep 100
SendRaw %ToPaste% ; SendRaw simulates typing the characters exactly, including AHK's special !^#+ characters
Return

You get to watch the computer do the typing, so be patient. If you press any keys or click around, this script will just keep on typing and mix in your live actions with what it’s typing.

You do have to watch out for the helpful features of whatever program you’re “pasting” into. Auto-indent exaggerates already-indented text that you “paste” and sometimes auto-complete suggestions are accepted in place of what you wanted.

Regardless, this little script saves time and frustration.

Paste through remote desktop connections