• Intro to AutoHotkey HotStrings with AutoHotkey Intermediate AutoHotkey GUIs are Easy with AutoHotkey Intro to DOS & AutoHotkey

Remove duplicates from CSV files in a flash with an AutoHotKey script

Remove-DuplicatesIn email marketing I am frequently having to remove duplicates.   I typically used Excel for this but I did it so frequently I built a small script to automate the process.

The program requires a CSV file and utilizes Microsoft’s Component Object Model to open Excel and examine the headers for a field named “email”.  Once it finds it it removes duplicate email addresses and saves them as a new file in the same folder with the naming showing how many rows are present.   This greatly reduces the amount of work needed to do the job.
This should ease the removal of duplicates from CSV files.

Remove duplicates demo video

Examining SPSS String variables – Flagging and re-coding data

SPSS string

SPSS string search

While not obvious, SPSS has some simple syntax that makes it easy to search through your string variables looking for key words /phrases.  The below syntax is my default go-to when reviewing open-end comments and trying to categorize their response.  The code controls for the case format of the variable as well as for the text you’re looking for.

If INDEX(Upcase(Var),Upcase("spss"))>0 Flag= 1 . 
exe.

The video below shows just how easy it is to review open-end – string variables and categorize them.

SPSS macro to flip ordinal variable labels so they graph well in Excel

SPSS macroEven though I do nearly all my analysis in SPSS, I still prefer to use Excel to graph my data.  Unfortunately Excel inverts the legend when graphing. Creating a new variable, or re-coding the old one, is time consuming and errors can creep in. This SPSS macro to invert an ordinal variable with very little work.  Saves an amazing amount of time!

It also has a built-in way to filter out too small a sample size.

 

 */////////////////////.   
DEFINE !ISubgp  (Var !TOKENS (1) /Cut !TOKENS (1) )
AUTORECODE   VARIABLES=!Var  /INTO SubGroup   /DESCENDING   /BLANK=MISSING   /PRINT.
AGGREGATE /OUTFILE=* MODE=ADDVARIABLES OVERWRITEVARS=YES /BREAK=subgroup  /Sub_CT=N .
Select if Sub_CT >=!Cut.
if missing(Subgroup) Sub_CT=$Sysmis.
Freq SubGroup.
exe.
!ENDDEFINE.
*/////////////////////. 
!ISubgp Var=Var_toInvert   Cut=20 . 

SPSS macro

The below video demonstrates the usage of the SPSS macro.

 

Transposing clipboard content in any program- No need to run to Excel!

Transposing clipboard content

While it is easy to use Excel to transpose rows into columns, I’ve created an AutoHotkey script which negates the need to.  Basically it examines what is on the clipboard and replaces tabs with line breaks, then it sends paste to the program thus it is transposing clipboard content.  I have it triggered of hitting a hotkey and comes in pretty handy and can save some time if it is something you do frequently.

Below is a video which demonstrates it usage.

 
^+t:: ;Control, Shift and T trigger script
    StringReplace, clipboard, clipboard,%A_Tab%, `r , All ;replace all tabs with line return
  SendPlay,^v   ;send "paste"
  Return

Transposing clipboard content

  • Intro to AutoHotkey HotStrings with AutoHotkey Intermediate AutoHotkey GUIs are Easy with AutoHotkey Intro to DOS & AutoHotkey