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

一所懸命に手抜きする

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

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

RでODBCを使うためのライブラリRODBC

Excel R SQL

 Rは便利とは言っても、Rだけで業務は完結することはまずありません。Rで処理するデータはどこからか取得する必要があります。
 CSVならば標準のread.table()で取得できますが、会社ではxlsやMDBなど多様なデータベースからデータを取得することがよくあります。これらはODBC経由でアクセスすることになる事が多いと思います。
 ODBC経由のデータ取得では RODBCライブラリが大活躍しています。特に Excel から SQL でデータが取得できるのはとても便利です。
 RODBC に含まれている主な関数をメモすると…

主要な関数(主要引数) 説明
odbcConnect(dsn, uid=xxx, pwd=xxx) DBにODBC接続する
odbcConnectExcel(xlfile) ExcelファイルにODBC接続する(32bitR)
odbcConnectExcel2007(xlfile) 同上(Excel2007-)
sqlTables(channel) DBからテーブル一覧を取得する。Excelならシート一覧
sqlColumns(channel, table) テーブルから列フィールド情報を取得する
sqlFetch(channel, table) DBからテーブルを読み出す
sqlQuery(channel, query) DBにSQLを投げ結果を返す
sqlDrop(channel, table) DBからテーブルを削除(Excelシートをクリア)する
sqlSave(channel, df, table, append = FALSE) RデータフレームをDBテーブルに書き出す
odbcClose(channel) ODBC接続を閉じる

▼コーディングの例

require(RODBC)


  # connect to the Excel file
  # Excel ファイルに接続します
conR = odbcConnectExcel("ReadTest.xls",readonly=TRUE)
conW = odbcConnectExcel("WriteTest.xls",readonly=FALSE)

  # delete target file 
  # ファイルを削除します
file.remove("WriteTest.xls")

  # or delete target sheet
  # シートを削除します
#SqlDrop(conW,"Result")

  # show tables(Excel sheets) 
  # テーブル一覧(Excelの場合にはシート一覧)を表示
sqlTables(conR)

  # read full data from the sheet
  # シートからデータを取得
df = sqlFetch(conR, "Sheet1")

  # read a sheet via SQL 
  # シートからSQLでデータを取得
df = sqlQuery(conR, "select * from [Sheet1$]")

  # Write a dataframe to Excel
  # データフレームをExcelファイルに書き出す。
sqlSave(conW, df,"Result", fast=FALSE,rownames=F,colnames=F)

  # Append a dataframe to the existing Excel sheet
  # データフレームを既存のExcelファイルに追記。
sqlSave(conW, df,"Result", fast=FALSE,rownames=F,colnames=T,append=T)

  # close all the connection
  # odbcコネクションをとじる
odbcCloseAll()

 sqlSaveはデフォルト(safer=TRUE)では指定されたテーブルをcreateします。既存テーブルがある場合にはエラーになるので上の例ではfile.remove()かsqlDrop()を使用することにしています。Safer=FALSEにしてみると
HY000 -5410 [Microsoft][ODBC Excel Driver] この ISAM では、リンク テーブル内のデータを削除することはできません。 [RODBC] ERROR: Could not SQLExecDirect 'DELETE FROM [Result]'
というエラーが出ました。DELETEできないと言うなら、file.remove()かsqlDrop()で削除してやれば良いと判断したわけです。

広告を非表示にする