Several ways exists to import CSV (excel) data into your SQL Server Database.
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.
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) <- "numb" colnames(mydata) <- "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) <- "numb" colnames(OutputDataSet) <- "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:
So, another way to store data from CSV into SQL Server.
Happy R-SQLing 🙂