Load Excel Data Into SQL Server

By:   |   Comments   |   Related: > 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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



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.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article

















get free sql tips
agree to terms