sqlSave in R to create and save a dataframe to an sql table

Question

Hi I am using R to save a data frame into a DB2 SQL table. I seem to be able to create the table skeleton, but not append the data to the table -

>df <- read.csv("dat.csv")

where dat.csv is a csv with no headers, just raw data in two columns

then i create the table:

>sqlQuery(channel, "create table sqltable 
                   (
                   col1  int,
                   col2  float
                   )"
         (

where I confirm the table is created by being able to select the empty table "sqltable" on the database

so now I need to add the data from "dat.csv" into "sqltable" by doing:

>sqlSave(channel, df, "sqltable", verbose=T, fast=T, append=T)

no: 1 rownames 1/***/no: 2 col1 31105/***/no: 3 col2 0.001/***/
no: 2 rownames 1/***/no: 2 col1 31106/***/no: 3 col2 0.023/***/
no: 3 rownames 1/***/no: 2 col1 31107/***/no: 3 col2 1.456/***/
no: 4 rownames 1/***/no: 2 col1 31108/***/no: 3 col2 0.001/***/
no: 5 rownames 1/***/no: 2 col1 31109/***/no: 3 col2 2.102/***/

all seems good until I do:

>sqlQuery(channel,"select * from sqltable")

[1] COL1     COL2
<0 rows> or 0-length row.names

the sqlSave command clearly picks up the data from dat.csv, so why is it not added to the table? what am I doing wrong?

Solution 1

It would be a good idea to define varTypes within sqlSave. Here's how I do it. :-)

columnTypes <- list(ColumnName="varchar(255)", NumberColumn="float", datecolumn="date")

This defines the column types for you data so you can add the argument within sqlSave. So lets do the sqlSave now.

sqlSave(channel, YourTable, rownames = FALSE, varTypes = columnTypes)

and if the table has already been added then I would do an sqlDrop before that.

Why is this an answer?

Well, I had the same problem. I was getting an error saying it couldn't execute the sqlSave but it was putting in my columns in the database. So I had to fix the columnTypes. R tries to do it manually but it's not perfect. :-)

Solution 2

Use rownames = FALSE inside your sqlSave().

Since you do not provide a sample of the data from dat.csv, I am assuming it has 2 columns based on your sqlSave code block. From your create statement, sqltable has only 2 columns; sqlSave defaults to rownames = TRUE which will make the data you enter from the dataframe have 3 columns, instead of the 2 you desire. By attempting to place 3 columns into a Microsoft SQL Server table with 2 columns without rownames = FALSE my R session gets aborted with a fatal error.

Comments

[Nova]

I was getting an error, "Error in cbind_all(x) : Argument 1 must have names". setting rownames = F fixed the error or me

[Jared Marx]

@Nova, in case you are trying to figure out what ODBC connector to use inside R, I recommend using DBI instead of RODBC . DBI allows for a finer control of the schema used, while RODBC's sqlSave only uses the default schema. DBI also likely has more active developers than RODBC.

[Nova]

Thanks Jared, that's so thoughtful!