How to Quickly Export Data from R to SQL Server

How to quickly export data from R to SQL Server

By writing the data to a CSV locally and then using a BULK INSERT (not readily available as a prebuilt function akin to sqlSave), the data can be written to the MS SQL Server very quickly.

toSQL = data.frame(...);
write.table(toSQL,"C:\\export\\filename.txt",quote=FALSE,sep=",",row.names=FALSE,col.names=FALSE,append=FALSE);
    sqlQuery(channel,"BULK
                INSERT Yada.dbo.yada
                FROM '\\\\<server-that-SQL-server-can-see>\\export\\filename.txt'
                WITH
                (
                FIELDTERMINATOR = ',',
                ROWTERMINATOR = '\\n'
                )");

SQL Server must have permission to access the network folder holding the CSV file, or else this process will not work. While it takes some setup with various permissions (the network folder and BULK ADMIN privileges, the reward in speed is infinitely more valuable).

how to export data from SQL in R

If I understand your question, you need to output the data in initdata into an excel file. I would use the openxlsx package in R.

 library(openxlsx)

 dbconnection <- odbcDriverConnect("Driver=ODBC Driver 11 for SQL Server;
                                    Server=OurServer; 
                                    Database=OurDatabase;
                                    Uid=;
                                    Pwd=")

 initdata <- sqlQuery(dbconnection,paste("exec ourTable"))`     

  #output the xlsx file
 write.xlsx(initdata,file = "file_name.xlsx")

I hope this helps.

Exporting data from R to MYSQL server

One of the fastest ways to load data into MySQL is to use its LOAD DATA command line tool. You may try first writing your R data frame to a CSV file, then using MySQL's LOAD DATA to load it:

write.csv(df, "output.csv", row.names=FALSE)

Then from your command line, use:

LOAD DATA INFILE 'output.csv' INTO TABLE table_name
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

Note that this assumes the CSV file is already on the same machine as MySQL. If not, and you have it still locally, then use LOAD DATA LOCAL INFILE instead.

You may read MYSQL import data from csv using LOAD DATA INFILE for more help using LOAD DATA.

Edit:

To deal with the issue of NA values, which should represent NULL in MySQL, you may take the approach of first casting the entire data frame to text, and then replacing the NA values with empty string. LOAD DATA will interpret a missing value in a CSV column as being NULL. Consider this:

df <- data.frame(lapply(df, as.character), stringsAsFactors=FALSE)
df[is.na(df)] <- ""

Then, use write.csv along with LOAD DATA as described above.

Push an R dataframe to SQL as a new table

How I solved it?

I was on the right track until I got to the part of pushing my dataframe to the DB. @r2evans pointed out that I was trying to create a table from the server with information that only existed locally with this code:

```{sql, connection = con}
SELECT * INTO [DB].[dbo].[New_Table]
FROM ?Data```

At this point, I changed the method and tried to pushed the dataframe using r-code:

```{r}
dbWriteTable(con, "[DB].[dbo].[New_Table]", Data)```

The result was the following error:

nanodbc/nanodbc.cpp:1587: 42000: [Microsoft][SQL Server Native Client 10.0][SQL Server]CREATE TABLE permission denied in database 'master'.

The problem here: I was trying to access to the database I have the permission using "[DB].[dbo].[New_Table]". The dbWriteTable function understand this parameter just as a pure name for the table, so I had to find a way to specify the right path to the database.

I did not find the way to do it using code directly. Solution: I went to the "ODBC Data Sources (32-bit)" (Windows APP); and changed the configuration of my connection. The connection I had, was taking the master as the default database. I changed it for the database I have the permission. So now I'm using the following code to push the table from R-notebooks:

```{r}
dbWriteTable(con, "[R_Test2]", Data, overwrite = T)```

It's working perfectly for what I need.

If someone know how to specify this path using code, will be good to know. Maybe in the first configuration chunk:

```{r setup, include=FALSE}
library(odbc)
library(DBI)
con <- dbConnect(odbc::odbc(), "Enterprise", timeout = 10)```

---

Related Topics