Formatting column entries for IN query

Extracting entries of a column in a spreadsheet and using them in an IN query is not a rare scenario [at least for me] at work. Now, copying and pasting the entries in a comma separated and sometimes quote-enclosed format is tedious and when there are 100 entries, doing that by hand is unacceptable for me. On top of that when each entry has to be modified a bit before passing in the IN query, then the whole process becomes drudgery. Today, when faced with such a situation, I used emacs and sed to save myself a lot of time and boredom.

Let us consider that the spreadsheet has a column that has the following entries:

12345-1
23456-2
34567-1
45678-1
56789-1
Now, lets consider that I have to strip the first suffix, enclose the values in quotes and pass them in the IN query. So, I copy the column and paste in a text file and run the following commands on the file.


sed -i 's/-1//' ./myfile.txt

sed -i 's/-2//' ./myfile.txt

Now the file had the following contents.

12345
23456
34567
45678
56789

Now, I opened the file in emacs and ran a keyboard macro to get the following string.

'12345', '23456', '34567', '45678', '56789',

Now, I could easily use it in my query. Now, the macro I used was simple. I started recording the macro using `C-x ('. The macro consisted of the following keys in sequence [try it yourself to be clear]:

' -> End -> ' -> , -> Delete -> Space

I ended recording the macro using `C-x )'. Now, I had one line edited so I let emacs run the macro 4 times using the following command:

C-u 4 C-x e

I got the inspiration for using macros in the following video.