Excelからデータを読み込むR関数(6)ExcelからSQLで条件抽出するR関数sqlQuery()

RでExcelデータを読み込めましたが…

　RODBC,readxl,gdata,XLConnect,xlsReadWrite,xlsx,openxlsxなどでExcelデータを読み込めることがわかりました。
　Excel に全データを読み込んでから分析するのも良いですが、特定条件にあったレコードだけを抽出したいこともあります。

ExcelシートからSQLで条件にあったレコードのみ読み込む方法

　Excelシートのデータすべてが必要なのではなく、特定の条件にあったレコードのみ必要なことも多いはずです。

　つまり、ExcelからSQLで条件抽出したいわけです。
　RODBCパッケージのsqlQuery()関数を用います。

 library(RODBC)
    fid <- odbcConnectExcel("Excelファイル名", readOnly=TRUE) 
   #fid <- odbcConnectExcel2007("Excelファイル名", readOnly=TRUE) 
    sqlQuery(fid,"select 列名 from [シート名$] where 抽出条件")
 odbcClose(fid)

　Macの場合にはxlsx対応のドライバがありません。xlsのみ対応しています。Windowsの場合、xlsxはodbcConnectExcel2007で接続することになります。
　ちょっと注意すべきはシート名は[シート名$]となるということです。
　サンプルの社員名簿から抽出してみます。

Excelファイル「社員名簿」シート

社員番号	部署	氏名
1	営業部	山田太郎
2	営業部	鈴木花子
3	総務部	杉本一郎
4	財務部	支払益子
5	経理部	会計主水
6	秘書室	白鳥麗子

社員名簿サンプルExcelファイル作成コード

[R]RODBC関数 sqlQuery()実行例

 library(RODBC)
 fid <- odbcConnectExcel("Rtest.xls", readOnly=FALSE) 
 sqlQuery(fid,"select * from [社員名簿$] where 部署 like '%総務%'")
##　社員番号    部署 　　 氏名
##1 　  　　3 総務部 杉本一郎
 odbcClose(fid)

　SQLによって条件抽出できることがわかります。条件抽出できてこそデータベースですから、この機能は重要です。設定次第ですがデフォルトではMySQLを呼び出しているようです

応用:Excel2003以前のオートフィルタの代用

　我が社では下位互換性の維持のため Excel2007 以降を使用していないのですが、Excel2007 はAutofilterで一つの列に3つ以上の選択条件を設けることができます。
　一方　Excel2003 等ではAutofilterで一つの列に2つまでしか条件を設定できませんでした。
　SQLはかなり複雑な条件も設定できますので一つの項目に3つの条件を設定してみます。

[R]RODBC関数 sqlQuery() 実行例[2] Autofilterの代用

 library(RODBC)
 fid <- odbcConnectExcel("Rtest.xls", readOnly=FALSE) 
 sqlQuery(fid,"select * from [社員名簿$] where (氏名='杉本%' or 氏名='松田%' or 氏名='栗原%')")
#　社員番号    部署 　　 氏名
#1 　  　　3 総務部 杉本一郎
 odbcClose(fid)

　このように一つの項目に3つの抽出条件を設定しても問題なく動作します。
　ただし、
　1.library(RODBC)でロード。場合によってはinstallから。
　2. ExcelファイルにodbcConnect
　3.SqlQuery()でSQL実行
　4.ODBC切断
　の4ステップが必要です。

Several Package enables us to import Excel data to R dataframe.
Using RODBC::sqlQuery(), we can get Excel data via SQL.
But it costs 4 steps,
　1. needs to load library(RODBC).
　2. establish connection to Excel file.
　3. execute SQL by SqlQuery().
　4. close ODBC connection.