Load Excel Data Into SQL Server

By: Matteo Lorini | Updated: 2020-02-28 | 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.

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.

Step 3 – Connect to SQL with R

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

Step 4 – Open Excel file with R

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

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.

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.

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.

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:

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.

Once again, we are using RStudio to view our 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)

Next Steps

The reader will need to install RStudio in order to test this tip.
- Click here to get RStudio
Check out these tips

About the author

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

Article Last Updated: 2020-02-28