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で接続することになります。
ちょっと注意すべきはシート名は[シート名$]となるということです。
サンプルの社員名簿から抽出してみます。
社員番号 | 部署 | 氏名 |
1 | 営業部 | 山田太郎 |
2 | 営業部 | 鈴木花子 |
3 | 総務部 | 杉本一郎 |
4 | 財務部 | 支払益子 |
5 | 経理部 | 会計主水 |
6 | 秘書室 | 白鳥麗子 |
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つの条件を設定してみます。
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ステップが必要です。