Load Excel Data Into SQL Server


By:   |   Updated: 2020-02-28   |   Comments   |   Related: More > Microsoft Excel Integration

Problem

The simple task of helping the line of business to load, read and analyze their data can sometimes be a challenge because of the division of roles and responsibilities that may prevent business users from having direct access to SQL Server. In addition, business users might use different tools to analyze the data. In this tip I am going to show how to use R to load and read data from SQL Server.

Solution

The solution that I am proposing is geared mostly towards analytics users that are familiar with R, but not familiar with SQL Server Management Studio or any other related SQL import/export utilities.

Solution – Import Microsoft Excel data using R

This tip is not aimed to explain what is R or how to use it. Most data analytics people use R or Python as the tools of choice for their work. This tips goal is to show how quickly we can write a generic ad-hoc code that will load an Excel worksheet into a SQL Server table.

The main advantage is that business people can load data into SQL without opening a format request ticket to have a busy DBAs create an import routine.

Step 1 – Excel file to import

Let's start with an example. Below is the Excel file we need to import into our SQL Server table. The file contains Customers, State, and Sale values.

sample excel data

Step 2 – RStudio

Let's open RStudio and import the two libraries that will allow us to connect to SQL and to open an Excel spreadsheet.

rstudio libraries

Step 3 – Connect to SQL with R

Let's use R to connect to SQL Server using integrated security.

rstudio connect to sql server

Step 4 – Open Excel file with R

Let's use R to open the Excel file and view its contents quickly.

sample data query in rstudio

Step 5 – Load SQL Table with R

Now we have a connection to SQL created, and the Excel file ready in R, the last step is to move our data into a SQL Server table. We will use the R function "sqlsave" to copy data from an R data frame to our SQL Server table.

load data into sql using rstudio

Please note that the R function sqlSave creates the target table if it does not exist.  Below is the Create Table code that was issue by the R function sqlSave.

CREATE TABLE [dbo].[myFileTest](
   [Customer] [varchar](255) NULL,
   [myState] [varchar](255) NULL,
   [mySale] [float] NULL
)

Step 6 – View Data with R

To verify that the data was loaded into our test table successfully, let us use RStudio to query the target table.

query data with r

RStudio Code.

> myQuery <- "select * from [dbo].[myFileTest]"
> VmyQuery <- sqlQuery(channel = sqlcnt, query = myQuery, 
+                      stringsAsFactors = F, 
+                      as.is = T)
> View(VmyQuery)

Step 7 – Filter Data with R

The beauty of R is that it lets us filter our data easily. For example, the below screenshot shows how the R function "filter" was used to filter data having State equal to GA or Sale greater than 500.

filter data using r

Step 8 – Save Filter Data to SQL with R

Now that we learned how to use our "filter" function; let's truncate our SQL table and load only data where State is equal to GA. To truncate our target table we are using T-SQL statement truncate table via RStudio:

save data to a sql server table with r

RStudio truncate table code.

> myQuery <- "truncate table [dbo].[myFileTest]"
> VmyQuery <- sqlQuery(channel = sqlcnt, query = myQuery, 
+                      stringsAsFactors = F, 
+                      as.is = T)
> View(VmyQuery)

Our table is empty, let's run the RStudio code to load only data related to the state of GA.

load data into sql server with r

Once again, we are using RStudio to view our data.

query data

RStudio code.

> sqlSave(sqlcnt, filter(myFile, myState == "GA"), "dbo.myFiletest",rownames=FALSE, append = TRUE,verbose = FALSE)
> myQuery <- "select * from [dbo].[myFileTest]"
> VmyQuery <- sqlQuery(channel = sqlcnt, query = myQuery, 
+                      stringsAsFactors = F, 
+                      as.is = T)
> View(VmyQuery)

Step 9 – Final R Code

Here is the final set of code.

Library(RODBC)
Library(readxl)
 
myFile <- read_excel("test.xlsx")
View(myFile)
sqlcnt <- odbcDriverConnect('driver={SQL Server Native Client 11.0};
                                server=youSQLServer;
                                database=YourDatabase;
                                rows_at_time=1024;
                                trusted_connection=yes')
# Write all xlsx file
sqlsave(sqlcnt, myFile, "dbo.myFiletest",rownames=FALSE, append = TRUE,verbose = FALSE)
 
# Write filter data
sqlsave(sqlcnt, filter(myFile, state == "GA"), "dbo.myFiletest",rownames=FALSE, append = TRUE,verbose = FALSE)
 

GO

Conclusion

RStudio allows users to create/truncate/read and update SQL Server tables. The R function sqlSave can be used for such purposes. Let's see a create table example:

Create a table:

> myQuery <- "CREATE TABLE  [dbo].[TestCreate] ( CustID int, custName varchar(20))"
> 
> VmyQuery <- sqlQuery(channel = sqlcnt, query = myQuery, 
+                      stringsAsFactors = F, 
+                      as.is = T)
> myQuery <- "select * from [dbo].[TestCreate] "
> VmyQuery <- sqlQuery(channel = sqlcnt, query = myQuery, 
+                      stringsAsFactors = F, 
+                      as.is = T)
> View(VmyQuery)
create table with r
Next Steps


Last Updated: 2020-02-28


get scripts

next tip button



About the author
MSSQLTips author Matteo Lorini Matteo Lorini is a DBA and has been working in IT since 1993. He specializes in SQL Server and also has knowledge of MySQL.

View all my tips




More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.






download


get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools