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.

JVM crash: an eye opener

I had done some programming but I guess I had not hit many walls. In C, my programs had segfaulted; but every time that happened I knew that my program broke; but there was never a notion of the programming environment breaking down. I, of course, know that when one is coding specifically to break the environment, it is not impossible; but I am talking about cases when the break is not intended. I could never imagine the shell, in which I am running a program for connecting to some website and getting some data, breaking down in between the running of the program.

However, recently I was using Rubymine to test some XML parsers I had written and suddenly it crashed. Initially, I thought may be Rubymine crashed, so I went back to shell where I had started it. To my utter surprise, it was the JVM that had crashed. That is when it hit me that programming environments can also crash. I have reported the bug; but there is not much done from Oracle's side about it.

Well, I do not expect much from them either; but the lesson I learnt is valuable: it is not only the program; but also the program's environment that can crash. Its not that I did not know it but it is not something I regularly expected. From now on, I will though. It did change the way I looked at errors. I guess this is what counts as experience.

Showing tmp folder in Rubymine

I have been using RubyMine for a few months now. I must say it has served me well. Recently, I was writing and reading files in the /tmp folder of root directory of my Rails project. However, in my project pane, it was not showing the /tmp folder. So, I had to go to the folder using my file manager and check the files. When I was through with my task, I wanted to figure out why that folder was not shown in the project pane. Getting the folder to show up was easy. In the project settings, in "Project Structure" section, there is a list of excluded folders. We just have to remove the entry for the /tmp folder.

Using sed to remove unwanted lines

I have written about sed's utility before as well and it has impressed me time and again by the amount of time it saves. Recently while testing E17, I generated backtraces using GNU debugger. However, the file to which I redirected the backtrace contained a large number of lines about reading and loading symbols. I wanted to quickly get rid of those lines so that the developers can directly view the backtrace instead of scrolling down pages before the actual backtrace. So I decided to use sed for the purpose. The approach I took to achieve my desired result was as follows:

1. I wanted to deletes lines that mentioned about loading or reading symbols. So, the sed operation I required was delete matching lines.
2. I wanted to edit the files. So, I turned the edit in-place option on.

With this approach in mind, I ran the following two lines and the file was formatted properly.

sed -i '/Reading symbols/d' my_file

sed -i '/Loaded symbols/d' my_file

N.B.: If you want to test the output, remove the -i option.

Audio settings for Skype on Archlinux

By default Skype on Arch linux does not take input from the microphone. It is usually because the channel to capture audio input is set to zero. It can be easily changed in alsamixer. Start by running the command alsamixer in console.

The default view above does not show all channels. Hit F5 to view all channels and increase the values of the channels 'LR Capture' and 'Digital' as shown below.

Webcam on Dell Latitude E6520 on Arch linux

On my Dell Latitude E6520, I have a webcam integrated into the front panel. I am not very used to video calls so I hardly use it. However, recently I wanted to talk to my brother using video call on Skype. So, I started digging in so that I can configure it. The first step was to see if it is already configured and works out of the box. I tried getting video to work during a Skype call; but it did not. So, I started looking for the actual model. I got the following output on lsusb.


Bus 001 Device 002: ID 8087:0024 Intel Corp. Integrated Rate Matching Hub
Bus 002 Device 002: ID 8087:0024 Intel Corp. Integrated Rate Matching Hub
Bus 001 Device 001: ID 1d6b:0002 Linux Foundation 2.0 root hub
Bus 002 Device 001: ID 1d6b:0002 Linux Foundation 2.0 root hub
Bus 001 Device 003: ID 413c:8187 Dell Computer Corp. DW375 Bluetooth Module
Bus 001 Device 004: ID 05ca:181c Ricoh Co., Ltd
Bus 002 Device 003: ID 0a5c:5800 Broadcom Corp. BCM5880 Secure Applications Processor

I could not figure out which one is the webcam. So, I looked into the output of lspci as well; but nothing there either. So, I was having a hard time figuring out which device is my webcam. The other question running in my head was the rare possibility of my kernel not detecting my webcam. I checked out /var/log/dmesg.log to see if it was detected. I ran the following grep.

grep webcam /var/log/dmesg.log

I was disappointed to find nothing. After some time, I realized my mistake and tried the grep query in case insensitive mode.

grep -i webcam /var/log/dmesg.log

This time I found the device.

uvcvideo: Found UVC 1.00 device Laptop_Integrated_Webcam_FHD (05ca:181c)
input: Laptop_Integrated_Webcam_FHD as /devices/pci0000:00/0000:00:1a.0/usb1/1-1/1-1.5/1-1.5:1.0/input/input8

Looking at the output I tried loading the uvcvideo kernel module and tested the webcam with the following vlc self-recording command.

vlc v4l:// :v4l-vdev="/dev/video0"

It worked fine. So, I looked into the Arch wiki for more details. It mentioned that linux-uvc was now in kernel. Looking into a more exhaustive list of devices supported by uvcvideo, I found the following matched my device closely.

05ca:181a
05ca:181b

So, I guessed 05ca:181c should also be supported by uvcvideo but being a new model was not listed there. So, I have reported this to the dev team and added the module uvcvideo the list of modules in my rc.conf.

MODULES=(acpi-cpufreq cpufreq_ondemand cpufreq_powersave uvcvideo)

Interestingly it is a Ricoh device but the driver is not the Ricoh driver [mentioned in the wiki].

GMail experience is too slow

Some time ago, I posted about the "Google" experience, where I mentioned about ease of use and speed being essential aspects of our regular usage of Google products and how Google [in my opinion] is doing it wrong by moving away from these key aspects. Here is one more incident. For the past two days, I have seen the following page a number of times.

It never happened to me before and I have been using GMail since the beginning of GMail itself. When I finally logged in successfully, I wanted to clear out my trash as it was huge. While it was deleting, it showed me the following lightbox.

It took more than 3 hours to complete the deletion of trash. I know that my trash is huge; but the time it took is way more. Overall GMail experience for me has slowed down considerably. When I create or update a filter, it takes at least 3 minutes to get back. People initially switched to Google because of its ease of use and speed; but now Google is gradually losing on those key aspects.

Reboot on Dell Latitude E6520 with Arch linux

On Dell Latitude E6520 and some other Latitude models, a number of distributions were having issues with reboot. Rebooting by any method, viz.

• restart command
• shutdown -r now command
• or restart from GUI

stopped all processes. The run level goes to 6 and everything works fine. Finally the system shows a message that it is rebooting now but the reboot does not happen. Ubuntu had this bug too but they fixed it and when my friend tried Ubuntu on the same hardware, reboot worked fine for him. I filed a bug in Arch and discussed it on #archlinux. The developers said it is an upstream issue and I was able to find an already reported bug upstream. Looking into the bugs and comments, I figured I should try setting the kernel parameter 'reboot' to 'pci'. So, I changed the kernel line in /boot/grub/menu.lst to include 'reboot' parameter as follows.

kernel /boot/vmlinuz26 root=/dev/sda2 resume=/dev/sda1 ro reboot=pci


After the above change, my system rebooted successfully.


N.B.: 1. Here, /dev/sda2 is my root partition and /dev/sda1 is my swap partition.
         2. Read more about rebooting a system.

Standards of "The Times of India"

From the name "The Times of India", one might expect high standards to be maintained in the newspaper in line of the British newspaper 'The Times'. However, gradually I have seen the standards of the newspaper going down. Some time ago, I saw their front page was a full page advertisement. The front page should be the place for the most important news for the day. Probably their editors thought the advertisement was more important than any event.

Recently, I saw a copy of The Times of india lying on the table and having nothing else to do, I thought to me "let me try the sports section". The article was called "Big Three in the making." It was about Indian cricket team, focussing on the players: Suresh Raina, Virat Kohli and Rohit Sharma. Reading through the section for Rohit Sharma, I found the following:

"Though he is an all-rounder and can fetch wickets too, his main strength is his batting and he has become one of the key members of Indian team. In the absense of biggies like Sachin, Laxman and Dravid he is one player on whom Indian team can depend fully. In the absense of biggies like Sachin, Laxman and Dravid he is one player on whom Indian team can depend fully. He can strengthen the middle order to fill the space after Laxman and Dravid's retirement."

The repetition of sentences above is not my mistake; but that is how it appeared in the newspaper. I was thinking to myself, "don't they even do any proofreading before sending an article for printing?"