2015-03-02

Conectando R con Excel mediante RODBC

Title El paquete RODBC conecta R con Excel. Con él podremos acceder hojas de libro: leer, guardar, copiar y manipular datos de tablas y consultas. El paquete emplea la conectividad ODBC (Open Database Connectivity) para establecer la conexión con Excel y mediante SQL (Structured Query Language) se interactúa con la base de datos.

Conexión de R con Excel

Instalamos y cargamos el paquete RODBC 

install.packages("RODBC")
library(RODBC)

# Listado de DSNs disponibles
odbcDataSources()

                                             Excel Files 
"Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)" 
                                      MS Access Database 
              "Microsoft Access Driver (*.mdb, *.accdb)"
Establecemos la conexión, el canal. Emplearemos como ejemplo un fichero de Excel cuyas hojas son las tablas de la base de datos Neptuno.mdb

# Interactivamente
canal <- odbcConnectExcel(file.choose()) 
canal <- odbcConnectExcel2007(file.choose()) # Para Excel 2007/2010

# Escribiendo la ruta
neptuno <- "C:/Users/User1/Documents/R/TablasNeptuno.xlsx"
canal <- odbcConnectExcel2007(neptuno) 

# Base de datos en el directorio de trabajo
canal <- odbcConnectExcel2007("TablasNeptuno.xlsx") 

# Descargando el fichero
url <- "https://sites.google.com/site/nubededatosblogspotcom/TablasNeptuno.xlsx"
download.file(url, "TablasNeptuno.xlsx", mode = "wb")  
canal <- odbcConnectExcel2007("TablasNeptuno.xlsx") 

# Descargando el fichero zip
# en el directorio de trabajo
url <- "https://sites.google.com/site/nubededatosblogspotcom/TablasNeptuno.zip"
download.file(url, "TablasNeptuno.zip")
unzip("TablasNeptuno")
canal <- odbcConnectExcel2007("TablasNeptuno.xlsx") 

# Descargando el fichero zip
# en un archivo temporal
temp <- tempfile()
download.file("https://sites.google.com/site/nubededatosblogspotcom/TablasNeptuno.zip", temp)
unzip(temp)
unlink(temp)
canal <- odbcConnectExcel2007("TablasNeptuno.xlsx")
Detalles de la conexión

canal

RODBC Connection 1
Details:
  case=nochange
  DBQ=C:\Users\Ben\Documents\R\TablasNeptuno.xlsx
  DefaultDir=C:\Users\Ben\Documents\R
  Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)}
  DriverId=1046
  MaxBufferSize=2048
  PageTimeout=5

Listado de las hojas

Devuelve un data frame de las hojas accesibles desde la conexión ODBC establecida. Está compuesto de cinco columnas: catalog, schema, name, type and remarks. Incluye las tablas del sistema por defecto. 

sqlTables(canal) 

        TABLE_CAT TABLE_SCHEM   TABLE_NAME   TABLE_TYPE  REMARKS
1 C:\\R\\Neptuno         <NA>  Categorías$ SYSTEM TABLE     <NA>
2 C:\\R\\Neptuno         <NA>    Clientes$ SYSTEM TABLE     <NA>
3 C:\\R\\Neptuno         <NA>   Empleados$ SYSTEM TABLE     <NA>
4 C:\\R\\Neptuno         <NA>     Pedidos$ SYSTEM TABLE     <NA>
5 C:\\R\\Neptuno         <NA>   Productos$ SYSTEM TABLE     <NA>
6 C:\\R\\Neptuno         <NA> Proveedores$ SYSTEM TABLE     <NA>
Para obtener una columna específica, empleamos el símbolo $ seguido del nombre de la columna. 

sqlTables(canal)$TABLE_NAME # All objects' names

[1] "Categorías$"           "Clientes$"             "Empleados$"           
[4] "Pedidos$"              "Productos$"            "Proveedores$"         
[7] "'Compañias de envío$'" "'Detalle de Pedidos$'"

Estructura de las columnas

Devuelve un data frame con la información de las columnas de las tablas de la conexión establecida (canal). 

names(sqlColumns(canal, "Clientes"))

[1] "TABLE_CAT"         "TABLE_SCHEM"       "TABLE_NAME"       
 [4] "COLUMN_NAME"       "DATA_TYPE"         "TYPE_NAME"        
 [7] "COLUMN_SIZE"       "BUFFER_LENGTH"     "DECIMAL_DIGITS"   
[10] "NUM_PREC_RADIX"    "NULLABLE"          "REMARKS"          
[13] "COLUMN_DEF"        "SQL_DATA_TYPE"     "SQL_DATETIME_SUB" 
[16] "CHAR_OCTET_LENGTH" "ORDINAL_POSITION"  "IS_NULLABLE"      
[19] "ORDINAL"
Para obtener una columna específica, empleamos el símbolo $ seguido del nombre de la columna. 

sqlColumns(canal, "Clientes")$COLUMN_NAME

 [1] "IdCliente"          
 [2] "NombreCompañía"     
 [3] "Nombre del contacto"
 [4] "Cargo del contacto" 
 [5] "Dirección"          
 [6] "Ciudad"             
 [7] "Región"             
 [8] "Código postal"      
 [9] "País"               
[10] "Teléfono"           
[11] "Fax"

Manipulación de datos

Antes de manipular datos es recomendable hacer una copia de seguridad. Las funciones sqlUpdate y sqlDrop ocasionan cambios o pérdidas de datos irreversibles.

    Leer e importar datos

Tenemos dos opciones:

  1. sqlFetch tanto para tablas como consultas ya existentes.

    2. ConsultaDePedidos <- sqlFetch(canal, sqtable = "Detalles de pedidos")
# Para limitar el nº de filas importadas: max
ConsultaDePedidos <- sqlFetch(canal, sqtable = "Detalles de pedidos", max = 10)

    # Obviando el argumento max
sqlFetch(canal, "Pedidos")[1:3, ] # Filas
sqlFetch(canal, "Pedidos")[1:3] # Columnas
sqlFetch(canal, "Pedidos")[1:6, 1:3] # Filas y columnas

      Id# de pedido              Cliente          Empleado
1         10248          Wilman Kala  Buchanan, Steven
2         10249   Toms Spezialitäten   Suyama, Michael
3         10250        Hanari Carnes Peacock, Margaret
4         10251 Victuailles en stock  Leverling, Janet
5         10252     Suprêmes délices Peacock, Margaret
6         10253        Hanari Carnes  Leverling, Janet
    Filas y columnas específicas

    sqlFetch(canal, "Pedidos")[c(1,3,5), c(1,3)] 

      Id# de pedido          Empleado
1         10248  Buchanan, Steven
3         10250 Peacock, Margaret
5         10252 Peacock, Margaret
  2. sqlQuery para enviar consultas SQL a la base de datos de Ms Access. Se puede incluir cualquier sentencia válida de SQL, incluyendo la creación, modificación, actualización o selección.

    3. Clientes <- sqlQuery(canal, "SELECT * FROM [Clientes$]")
# Para limitar el nº de filas leídas: max
Clientes <- sqlQuery(canal, "SELECT * FROM [Clientes$]", max = 10)
Es necesario especificar el nombre de la hoja entre corchetes y con el símbolo $ al final. Tanto si incluye espacios en blanco como si no.

CompEnvios <- sqlQuery(canal, "SELECT * FROM [Compañias de envío$]")
También es posible en lugar de corchetes usar acentos graves `Nombre de la hoja$`.

Clientes <- sqlQuery(canal, "SELECT * FROM `Clientes$`")
Los nombres de los campos entre corchetes cuando incluyen espacios.

sqlQuery(canal, "SELECT [Id# de pedido], Cliente, Empleado, [Fecha de pedido] FROM [Pedidos$]")[1:10, ]

   Id# de pedido                Cliente          Empleado Fecha de pedido
1          10248            Wilman Kala  Buchanan, Steven      2010-07-04
2          10249     Toms Spezialitäten   Suyama, Michael      2010-07-05
3          10250          Hanari Carnes Peacock, Margaret      2010-07-08
4          10251   Victuailles en stock  Leverling, Janet      2010-07-08
5          10252       Suprêmes délices Peacock, Margaret      2010-07-09
6          10253          Hanari Carnes  Leverling, Janet      2010-07-10
7          10254      Chop-suey Chinese  Buchanan, Steven      2010-07-11
8          10255     Richter Supermarkt   Dodsworth, Anne      2010-07-12
9          10256 Wellington Importadora  Leverling, Janet      2010-07-15
10         10257       HILARIÓN-Abastos Peacock, Margaret      2010-07-16

    Creación de tablas

Para crear una hoja en Excel a partir de un data frame utilizamos la función sqlSave. Los principales argumentos son:

channel - conexión creada con odbcConnect.
dat - data frame.
tablename - nombre de la tabla creada, por defecto el del data frame.
rownames - valor lógico (TRUE o FALSE) o el nombre de la columna de los nombres de de fila.
addPK - valor lógico, para establecer los rownames (nombres de fila) como clave principal.

Es preciso especificar al crear el canal que no sea de solo lectura:

# Base de datos en el directorio de trabajo
canal <- odbcConnectExcel2007("TablasNeptuno.xlsx", readOnly = FALSE) 

# Nombre de la nueva tabla el del data frame 
sqlSave(canal, women, rownames = FALSE) 
# Especificando el nombre de la nueva tabla
sqlSave(canal, women, "mujeres", rownames = FALSE)
Si el nombre de la hoja existe en el fichero generará un error:

Error in sqlSave(canal, women, "mujeres") : 
  table ‘mujeres’ already exists

# Especificando el nombre de las filas (rownames) 
sqlSave(canal, women, "mujeres", rownames = "filas")

    Actualización de tablas

sqlUpdate es la función que empleamos para actualizar tablas con Ms Access. Sin embargo, a día de hoy, en Excel genera errores y no funcion. Para solventarlo, empleamos la función sqlQuery para generar directamente la consulta de actualización en SQL.

# Tabla con los datos de origen
height <- c(70, 75, 80)
weight <- c(120, 125, 130)
act <- data.frame(height, weight)
sqlSave(canal, act, "act", rownames = "filas")
sqlFetch(canal, "act")

  filas height weight
1     1     70    120
2     2     75    125
3     3     80    130
Datos a destino (a actualizar):

sqlFetch(canal, "mujeres", max = 3)[1:3]

  filas height weight
1     1     58    115
2     2     59    117
3     3     60    120

sqlQuery(canal, "UPDATE [mujeres$] INNER JOIN [act$] ON [mujeres$].filas = [act$].filas SET [mujeres$].filas = [act$].[filas], [mujeres$].height = [act$].[height], [mujeres$].weight = [act$].[weight]")
Datos actualizados:

sqlFetch(canal, "mujeres", max = 3)

  filas height weight
1     1     70    120
2     2     75    125
3     3     80    130

Eliminación de tablas

La función sqlDrop elimina no elimina la hoja sino los datos de la misma. Cuidado pues la acción es irreversible.

sqlFetch(canal, "mujeres")sqlDrop(canal, "mujeres")

sqlFetch(canal, "mujeres")[1:6, ]

  F1 F2 F3
1 NA NA NA
2 NA NA NA
3 NA NA NA
4 NA NA NA
5 NA NA NA
6 NA NA NA

Referencias

Posted by at
Labels: , , , ,

No hay comentarios:

Publicar un comentario

Suscribirse a: Enviar comentarios (Atom)
Nube de datos