R import MySQL data tables

R language is free. Its functions are very powerful. It can be used to do all kinds of data analysis and plot drawing. I always use MySQL database to manage my data. R has the capability to read data from MySQL databases. Below is a simple example to demonstrate the process.

1. Install database import packages

In R console to enter and execute the following command:

2. Try to connect to MySQL database and list all data tables in the database.

library(RMySQL) # import package
con<-dbConnect(dbDriver(“MySQL”),dbname=”ddddd”,username=”zzzz”,password=”ppppp”) # create connection
dbListTables(con) # list data tables

3. Deal with the UTF8 encoding

Visit https://github.com/rstats-db/RMySQL/issues/2 to get detailed information for dealing with UTF8 character set.

Another article in Chinese (http://www.cnblogs.com/xiongchang18/p/4955813.html) also provides solution to solve the encoding problem.

Posted in R/S-Plus, Tips | Tagged , | Leave a comment

MySQL transpose function of aggregation

An article published at the following providing a lot of examples.


At the stackoverflow website there is an example as below. It helps me a lot.

The following text from the site.


Here is what my current mysql table looks like:

PunchID EmpID PunchEvent PunchDateTime
1 0456 clockin 5/14/2013 8:36:26 AM
48 0456 breakout 5/14/2013 12:01:29 PM
53 0456 breakin 5/14/2013 12:28:31 PM
54 0456 clockout 5/14/2013 2:28:33 PM
57 0456 clockin 5/15/2013 7:38:34 AM
58 0456 breakout 5/15/2013 7:38:39 AM
59 0456 breakin 5/15/2013 7:38:41 AM
60 0456 clockout 5/15/2013 7:38:42 AM
Now I want to return a result set that is grouped based on the day of the week like so:

Day ClockIn BreakOut BreakIn ClockOut
Tuesday 8:36:26 AM 12:01:29 PM 12:28:31 PM 2:28:33 PM
Wednesday 7:38:34 AM etc, etc…
This is my current query. But it only returns the first punch for each day.


MAX(CASE WHEN PunchEvent = ‘ClockIn’ THEN DATE_FORMAT(PunchDateTime, ‘%r’) END) ClockIn,
MAX(CASE WHEN PunchEvent = ‘BreakOut’ THEN DATE_FORMAT(PunchDateTime, ‘%r’) END) BreakOut,
MAX(CASE WHEN PunchEvent = ‘BreakIn’ THEN DATE_FORMAT(PunchDateTime, ‘%r’) END) BreakIn,
MAX(CASE WHEN PunchEvent = ‘ClockOut’ THEN DATE_FORMAT(PunchDateTime, ‘%r’) END) ClockOut
FROM tableName
WHERE EmpID = 456
ORDER BY PunchDateTime
SQLFiddle Demo

║ Tuesday ║ 08:36:26 AM ║ 12:01:29 PM ║ 12:28:31 PM ║ 02:28:33 PM ║
║ Wednesday ║ 07:38:34 AM ║ 07:38:39 AM ║ 07:38:41 AM ║ 07:38:42 AM ║

Posted in MySQL | Tagged , , , , | Leave a comment

Removing the password of an Excel VBA project

' to remove the password of a excel with .xlam extension
' 1. use the Savexlam2xls subroutine to save a copy of xlam file to xls file
' 2. run the moveprotect subroutine to remove the password of the xls file
' 3. save as the xls file to xlam (vba addin)

' 把2007加载宏xlam文件修改成工作簿,再另存为2003格式工作簿可以用下面代码:
Sub Savexlam2xls()
    Dim strFile, wb As Workbook
    strFile = Application.GetOpenFilename(FileFilter:="Micrsofe Excel文件(*.xlam), *.xlam")
    If strFile = False Then Exit Sub
    With Workbooks.Open(strFile)
        .IsAddin = False
        .SaveAs FileName:=Replace(strFile, "xlam", "xls"), FileFormat:=xlExcel8
    End With
End Sub

Sub RemoveProtect()
    Dim FileName As String
    FileName = Application.GetOpenFilename("Excel文件(*.xls & *.xla),*.xls;*.xla", , "VBA破解")
    If FileName = CStr(False) Then
       Exit Sub
       VBAPassword FileName, False
    End If
End Sub

Sub SetProtect()
    Dim FileName As String
    FileName = Application.GetOpenFilename("Excel文件(*.xls & *.xla),*.xls;*.xla", , "VBA破解")
    If FileName = CStr(False) Then
       Exit Sub
       VBAPassword FileName, True
    End If
End Sub

Private Function VBAPassword(FileName As String, Optional Protect As Boolean = False)
      If Dir(FileName) = "" Then
         Exit Function
         FileCopy FileName, FileName & ".bak"
      End If

      Dim GetData As String * 5
      Open FileName For Binary As #1
      Dim CMGs As Long
      Dim DPBo As Long
      For i = 1 To LOF(1)
          Get #1, i, GetData
          If GetData = "CMG=""" Then CMGs = i
          If GetData = "[Host" Then DPBo = i - 2: Exit For
      If CMGs = 0 Then
         MsgBox "请先对VBA编码设置一个保护密码...", 32, "提示"
         Exit Function
      End If
      If Protect = False Then
         Dim St As String * 2
         Dim s20 As String * 1
         Get #1, CMGs - 2, St
         Get #1, DPBo + 16, s20
         For i = CMGs To DPBo Step 2
             Put #1, i, St
         If (DPBo - CMGs) Mod 2 <> 0 Then
            Put #1, DPBo + 1, s20
         End If
         MsgBox "文件解密成功......", 32, "提示"
         Dim MMs As String * 5
         MMs = "DPB="""
         Put #1, CMGs, MMs
         MsgBox "对文件特殊加密成功......", 32, "提示"
      End If
      Close #1
End Function
Posted in Excel, VBA/ASP | Tagged , , , | Leave a comment

fix dovecot namespace error

OS: Ubuntu 14.04 LTS

Dovecot: v2.2.9

Problem: after I ran a “apt-get upgrade” command, imap server refused to work. It was noticed by logging in to mail server using roundcubemail interface. It said that imap server cannot be connected.

Checking and testing:

view /var/log/mail.err and found the following message:

Mar 15 09:57:01 emerald dovecot: lmtp(3748, zhanshan.dong@sunfinedata.com): Error: user zhanshan.dong@sunfinedata.com: Initialization failed: namespace configuration error: inbox=yes namespace missing


Basically the latest version of Dovecot, its configuration is quite particular about namespaces, which it wasn’t in the past. Anyhow adding this to the dovecot.conf file help resolved the issue:

namespace inbox {
inbox = yes

Posted in Tips | Tagged , | Leave a comment

Clean the boot partition of ubuntu OS

Every time you installed a new Linux kernel, it left a footprint in the grub_config, making the boot menu longer each time. Furthermore, it clogs the boot partition of the Ubuntu system. When the boot partition is full, you will get trouble to boot the machine properly. Does Ubuntu provide any easier way to clean them up or keep them from showing in the boot list?

The following information is from the internet and works for me perfectly.

Open terminal and check your current kernel:

uname -r 


Next, type the command below to view/list all installed kernels on your system.

dpkg --list | grep linux-image 

Find all the kernels that lower than your current kernel. When you know which kernel to remove, continue below to remove it. Run the commands below to remove the kernel you selected.

sudo apt-get purge linux-image-x.x.x.x-generic 

Finally, run the commands below to update grub2

sudo update-grub2 

Reboot your system.

Posted in Software, Tips | Tagged , , , | Leave a comment