Importing CSV data using T-SQL and R

June 18, 2016
By

(This article was first published on R – TomazTsql, and kindly contributed to R-bloggers)

Several ways exists to import CSV (excel) data into your SQL Server Database.

362-exporting-csv-and-excel

One is of course, using SSIS. The other one – similar to SSIS – is using import/export function in SSMS. With wizard, you will be able to import data.

Futher on, BULK INSERT (BCP) statement is to all DBA and developers very close way of importing data. Another T-SQL statement is selecting from OPENDATASOURCE; simple and fast way of doing this (also you might want to check Linked Servers). In c# there is straightforward class SqlBulkCopy in System.Data.SqlClient namespace. Going into script language, PowerShell is also a fast and neat way to import CSV into your SQL Server. Script Guy server four ways how to do it with PS. I usually use c# namespace and same class.

Another way, going back to T-SQL, is using OLE Automation stored procedure, using standard set of sp_OAMethod. And also natively compiled stored procedures will also bring you same functionality.

With R integration in SQL Server with Microsoft R Server, importing CSV data in SQL Server can be done with sp_execute_external_script as well. With following snippet you can import data from csv into T-SQL

EXECUTE sp_execute_external_script    
        @language = N'R'    
    , @script = N'
                mydata <- data.frame(read.csv("C:\\MyFolder\\Book1.csv", 
sep=";", HEADER=FALSE))
                colnames(mydata)[1] <- "numb"
                colnames(mydata)[2] <- "charc"
                OutputDataset <- mydata;'

WITH RESULT SETS (( 
                     numb INT
                    ,charc VARCHAR(10)
                    ));

Or even shorter input file directly assigned to OutputDataSet:

EXECUTE sp_execute_external_script    
        @language = N'R'    
       ,@script=N'OutputDataSet<- read.csv("C:\\MyFolder\\Book1.csv")
            colnames(OutputDataSet)[1] <- "numb"
            colnames(OutputDataSet)[2] <- "charc;'
WITH result sets ((numb int),(charc VARCHAR(10)));

 

Creating stored procedure (with above execute statement we create procedure call ImportCSV ) with this script you can store data directly into table:

DECLARE @result TABLE (cifra varchar(10))
INSERT INTO @result
EXECUTE ImportCSV
SELECT * FROM @result

And this is my Book1.csv sample file:

2016-06-19 07_48_09-Book1.csv - Excel

So, another way to store data from CSV into SQL Server.

Happy R-SQLing:-)

To leave a comment for the author, please follow the link and comment on their blog: R – TomazTsql.

R-bloggers.com offers daily e-mail updates about R news and tutorials on topics such as: Data science, Big Data, R jobs, visualization (ggplot2, Boxplots, maps, animation), programming (RStudio, Sweave, LaTeX, SQL, Eclipse, git, hadoop, Web Scraping) statistics (regression, PCA, time series, trading) and more...



If you got this far, why not subscribe for updates from the site? Choose your flavor: e-mail, twitter, RSS, or facebook...

Comments are closed.

Search R-bloggers


Sponsors

Never miss an update!
Subscribe to R-bloggers to receive
e-mails with the latest R posts.
(You will not see this message again.)

Click here to close (This popup will not appear again)