読者です 読者をやめる 読者になる 読者になる

一所懸命に手抜きする

監査の仕事をしています。ITを利用し、良い意味で「手抜き」することは効率化と精度アップに役立つと思うんです。部下への引き継ぎのためにまずは諸々頭から引っ張り出そうとブログを始めました。

[ Main ]  [ 別館 ]
当ブログで取り上げられている事案やデータベース、人物等はすべてフィクションです。

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

R Excel Excel2013以前 SQL

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.

広告を非表示にする