Excelからデータを読み込むR関数(7)sqlQuery()はやや面倒なのでユーザー関数化sqlExcel()

ExcelからRへのデータ読み込みは関数化しましょう

　ExcelからRにデータを読み込むことは頻繁にありますが、その都度　library(RODBC)、odbcConnectExcel()、sqlQuery()…とお決まりの手順を繰り返すのは馬鹿らしいので関数化してしまいます。ライブラリRODBCがインスールしてあることが前提です。
　自分としては、この関数による業務効率アップは多大なものでした。

[R]ユーザー関数 sqlExcel() 実行イメージ

   sqlExcel("C:/Wrk/Rtest.xls","Sheet1")
#[1] "select * from [Sheet1$]"
#   支社番号 支社名 支店番号 支店名
#1       10   札幌       11   札幌
#2       20   東京       21 大手町
#3       20   東京       22 秋葉原
#4       20   東京       23   横浜

　こんな感じでファイル名とシート名を指定するだけでデータが取り込めます。

ユーザー関数 sqlExcel()

[R]ユーザー関数 sqlExcel()

sqlExcel<- function(.FPath,.Sheet="Sheet1",.SQL="select * from Sheet1",
                    .Header=TRUE)
    #  常套句となっているものを省略して簡易にExcelから読み込めるようにした
    # .FPath  : Excelファイルパス名
    # .Sheet　: シート名(デフォルトではSheet1)
    # .SQL    : SQL文(デフォルトではselect * from Sheet1)
    # .Header : Excelにヘッダがあるかどうか(デフォルトではTRUE)
{
  #library(RODBC)  # if needed
    fid <- odbcConnectExcel(.FPath)
  if(.Header)
    {  .Sheet<-paste0("[",.Sheet,"$]")
       .SQL<-sub("Sheet1", .Sheet, .SQL)
          print(.SQL)
       .DGot<-sqlQuery(fid,.SQL,as.is=T)
    } 
  else
    {   print(paste("select * from ",.Sheet))
       .D<-sqlFetch(fid,.Sheet,as.is=T)
       .DGot<-rbind(names(.D),.D)
       for (i in 1:ncol(.DGot)) {names(.DGot)[i]<-paste0("F",i)}
    }
       close(fid)
  return(.DGot)
}

　ライブラリRODBCはR起動後一度メモリに読み込めば良いので関数の中で毎回library(RODBC)とする必要はありません。
　対象となるExcelファイルにヘッダがあるかないかで対処を区別しています。
1.対象となるExcelファイルにヘッダがある時はsqlQuery()関数を用いてデータを取得します。
　対象シート名は[????$]のように括る必要がありますので、第二引数(.Sheetシート名)を加工します。
　加工したシート名をもとにSQL文も書き換えます。
　sqlQuery()関数を用いてデータを取得します。
2.対象となるExcelファイルにヘッダがない時はsqlFetch()関数を用いてデータを取得します。
　sqlFetch()関数を用いて対象シートからデータを一括して取得します。
　ヘッダがないのに1行目がヘッダとして認識されますから、ヘッダを1行目のデータとして扱い2行目以降のデータの前にrbindします。
　ヘッダはないので、F1、F2、･･･と順に命名します。SPSSなどのようにV1,V2,…とするには"F"を"V"に書き換えてください。

ユーザー関数 sqlExcel() 使用例

　ユーザー関数 sqlExcel() は引数が多いので使用例を示します。引数はファイル名以外は省略可能となっています。
　以降の使用例では実行結果を　#でコメントとしてソースに埋め込んで表示してあります。

最低限の指定(ファイル名のみ)

   sqlExcel("C:/Wrk/Rtest.xls")
#[1] "select * from [Sheet1$]"
#   支社番号 支社名 支店番号 支店名
#1       10   札幌       11   札幌
#2       20   東京       21 大手町
#3       20   東京       22 秋葉原
#4       20   東京       23   横浜

　シート名が省略されるとSheet1からデータを取得することになります。Sheet1がなければエラーとなります。
　以下にエラー odbcTableExists(channel, sqtable) :
　'Sheet1’: table not found on channel

ファイル名とシート名を指定

　シート名はSheet1とは限りませんから、おそらくこの使用例が最もデフォルトなパターンかと思います。

   sqlExcel("C:/Wrk/Rtest.xls","社員名簿")
#[1] "select * from [社員名簿$]"
#  社員番号   部署     氏名
#1        1 営業部 山田太郎
#2        2 営業部 鈴木花子
#3        3 総務部 杉本一郎
#4        4 財務部 支払益子
#5        5 経理部 会計主水
#6        6 秘書室 白鳥麗子

　sqlQuery()を知っている人は注意してください。
　sqlExcel()ではシート名を関数内で[ $]付与しています
　Excelシート名を[ $]という形式で指定することを知っている人こそ注意して下さい。

　シート名を[ $]形式で引数として渡した場合のエラーの例。

sqlExcel("C:/Wrk/Rtest.xls","[社員名簿$])"
[1] "select * from [[社員名簿$]$]"
[1] "42000 -1002 [Microsoft][ODBC Excel Driver] '[社員名簿$' のかっこの使い方が正しくありません。"
[2] "[RODBC] ERROR: Could not SQLExecDirect 'select * from [[社員名簿$]$]'"

　実用のためにはExcelシート名を[ $]という形式で引数に引き渡された場合の対応も入れると良いかもしれません。

ヘッダのないExcelデータを取得する場合

　ヘッダのないExcelデータを取得する場合は.Header=FALSEを指定します。

   sqlExcel("C:/Wrk/Rtest.xls","Sheet3",.Header=FALSE)
#[1] "select * from  Sheet3"
#        F1      F2
#1     日本   Japan
#2 フランス  France
#3   ドイツ Germany

　ヘッダのないExcelデータを取得する際に.Header=TRUEとしたり、.Headerの引数指定を省略してはいけません。

   sqlExcel("C:/Wrk/Rtest.xls","Sheet3",.Header=TRUE)
#[1] "select * from [Sheet3$]"
#      日本   Japan
#1 フランス  France
#2   ドイツ Germany

　ヘッダがあることになっているので、1行目のデータがヘッダとして扱われてしまいました。

なお、ヘッダがない場合には全件取得としますのでSQLによる条件指定は無効です。

   sqlExcel("C:/Wrk/Rtest.xls","Sheet3",.Header=FALSE
　　　　　　,.SQL="select F2 from Sheet1 where F1=1")
#[1] "select * from  Sheet3"
#        F1      F2
#1     日本   Japan
#2 フランス  France
#3   ドイツ Germany

　この例では.Header=FALSEなのでSQLで指定したシート名・条件・項目すべて無視されています。

ヘッダがあるのに.Header=FALSEとしてもおかしなことになります。

   sqlExcel("C:/Wrk/Rtest.xls","Sheet3","社員名簿",.Header=FALSE)
#[1] "select * from  社員名簿"
#        F1     F2       F3
#1 社員番号   部署     氏名
#2        1 営業部 山田太郎
#3        2 営業部 鈴木花子
#4        3 総務部 杉本一郎
#5        4 財務部 支払益子
#6        5 経理部 会計主水
#7        6 秘書室 白鳥麗子

本来はヘッダなのにデータ1行目として扱われてしまい、ヘッダはないことになっています。

ヘッダがある場合にはSQLでExcelデータを条件抽出できます

   sqlExcel("C:/Wrk/Rtest.xls","社員名簿","select * from Sheet1 where 社員番号=1")
#[1] "select * from [社員名簿$] where 社員番号=1"
#  社員番号   部署     氏名
#1        1 営業部 山田太郎

注意

ヘッダに数値がある場合には問題となります。

    sqlExcel("C:/Wrk/Hist.xls",,"Sheet3",.Header=TRUE)
#[1] "select * from [Sheet3$]"
#  卑弥呼が魏に使い  F2
#1         奈良大仏 752
#2           平安京 794

　F2となっている場所には239という数値が入っていたので期待通りとなっていません。
そもそも1行目はヘッダでもないのですが。
　これはRは数値や記号で始まる変数を認めていないためらしいです。

引数名を明示するかどうか

　sqlExcel() 関数では第1引数(ファイルパス名)のみ必須ですが、第2引数から第4引数までは省略可能です。
　この場合、.Sheetなどの引数名は明示しなくても、例えば、2番目の引数は暗黙裡に.Sheetとして扱われます。ですから引数の順序を変えて指定する場合には必ず引数名(.Sheet,.SQL,.Header)を指定して下さい。

Excelからデータを読み込むR関数(1)RODBC::sqlFetch() - 一所懸命に手抜きする
Excelからデータを読み込むR関数(2)readxl::read_excel - 一所懸命に手抜きする
Excelからデータを読み込むR関数(3)書き込みも - 一所懸命に手抜きする
Excelからデータを読み込むR関数(4)とりあえずまとめ。書き込みもふくめて - 一所懸命に手抜きする
Excelからデータを読み込むR関数(5)readxl::read_excelのwrapper - 一所懸命に手抜きする
Excelからデータを読み込むR関数(6)ExcelからSQLで条件抽出するR関数sqlQuery() - 一所懸命に手抜きする

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.
So,I make wrapper sqlExcel().

sqlExcel(.FPath,.Sheet="Sheet1",.SQL="select * from Sheet1",.Header=TRUE)