/*******************************************************************************************************************
-- Title : [R3.3] RODBC를 통한 DB 연결 및 XLConnect를 통한 엑셀 연결
-- Reference : 웹검색
-- Key word : odbc rodbc odbcconnect sqlquery xlconnect loadworkbook readworksheet db excel
xls xlsx 엑셀 database connect
*******************************************************************************************************************/
# **************************************************
# -- Conenct DB using RODBC
# **************************************************
# -- Create ODBC SystemDSN
# "제어판 > 관리도구 > ODBC 데이터원본 > 시스템DSN"
# -- Install and Load RODBC Package
install.packages("RODBC")
library(RODBC)
# -- Declare connection string
conn_str <- odbcConnect("R_DSN", uid = "usr_dbrang", pwd = "******")
# -- Declare query string
sql_str = "SELECT TOP 10 ProductID, [Name], ProductNumber FROM [Production].[Product]"
# -- Call SQL result from DB
df_data = sqlQuery(conn_str, sql_str)
# -- See result
df_data
str(df_data)
class(df_data)
# **************************************************
# -- Conenct Excel using XLConnect package
# **************************************************
install.packages("XLConnect")
library(XLConnect)
# -- Declare connection string
xlsx_str <- loadWorkbook("e:\\ttt.xlsx", create = T)
# -- Call SQL result from Excel
df_data2 <- readWorksheet(xlsx_str, sheet = "중간고사")
df_data2
## File
ttt.xlsx