In this post, let us see how to import csv or text file into SQL Server table. If you want to import some random files on adhoc basis then we can make use of Import Flat File Wizard from SQL Server Management Studio.
If the requirement is to do on regular basis or import multiple files simultaneously then we can do this using t-sql script that executes R script to read an csv or text file & load into SQL Server table.
As a Pre-requisites, Install R services and then from SSMS enable the external scripting feature. Restart the database engine and then verify the installation as mentioned in MSDN.
In my previous post on SQL Server export adhoc SQL or table to CSV file, I have exported few csv files. I am going to make use of those three csv files for this example.
I have also created a database called "Staging" to load the csv files into different SQL tables created dynamically with csv filename as table name.
Below screenshot shows executing the below script imports the csv files as SQL Server tables under staging database.
Please note: In the below script, we need to configure the values for
sqlconnString variable with database connection details
list_filespath & list_filenames variable with folder path details
DECLARE @RScript NVARCHAR(MAX) =N'
library(foreach);
sqlConnString <- "Driver=SQL Server;Server=''LAPTOP-LCUB6HBB''; Database=Staging;Uid=sa;Pwd=***"
list_filespath <- list.files(path = "H:/Temp/Blog/ImportExportExcel", pattern = "*.csv", all.files = FALSE,
full.names = TRUE, recursive = TRUE,
ignore.case = FALSE, include.dirs = FALSE, no.. = TRUE)
list_filenames <- list.files(path = "H:/Temp/Blog/ImportExportExcel", pattern = "*.csv", all.files = FALSE,
full.names = FALSE, recursive = TRUE,
ignore.case = FALSE, include.dirs = FALSE, no.. = TRUE)
#Check file exists in path
if (length(list_filespath) != 0) {
foreach(m = 1:length(list_filespath)) %do%
{
sqlTable <- strsplit(list_filenames[m],split = ".csv")
sqlTable <- gsub("\\s", "", sqlTable)
sqlTable <- gsub("[^[:alnum:]]", "", sqlTable)
Output <- read.csv(file=list_filespath[m], header=TRUE, sep=",")
sqlDS <- RxSqlServerData(connectionString = sqlConnString,table = sqlTable)
rxDataStep(inData = Output, outFile = sqlDS,overwrite = TRUE)
}
}'
EXEC sp_execute_external_script
@language = N'R'
,@script = @RScript
If the requirement is to do on regular basis or import multiple files simultaneously then we can do this using t-sql script that executes R script to read an csv or text file & load into SQL Server table.
As a Pre-requisites, Install R services and then from SSMS enable the external scripting feature. Restart the database engine and then verify the installation as mentioned in MSDN.
You might face “Access is denied” error while executing below scripts, to overcome that right-click on the folder where CSV files will be imported / exported and provide folder access (read, write) to "ALL_APPLICATION_PACKAGES".
In my previous post on SQL Server export adhoc SQL or table to CSV file, I have exported few csv files. I am going to make use of those three csv files for this example.
I have also created a database called "Staging" to load the csv files into different SQL tables created dynamically with csv filename as table name.
Below screenshot shows executing the below script imports the csv files as SQL Server tables under staging database.
Please note: In the below script, we need to configure the values for
sqlconnString variable with database connection details
list_filespath & list_filenames variable with folder path details
library(foreach);
sqlConnString <- "Driver=SQL Server;Server=''LAPTOP-LCUB6HBB''; Database=Staging;Uid=sa;Pwd=***"
list_filespath <- list.files(path = "H:/Temp/Blog/ImportExportExcel", pattern = "*.csv", all.files = FALSE,
full.names = TRUE, recursive = TRUE,
ignore.case = FALSE, include.dirs = FALSE, no.. = TRUE)
list_filenames <- list.files(path = "H:/Temp/Blog/ImportExportExcel", pattern = "*.csv", all.files = FALSE,
full.names = FALSE, recursive = TRUE,
ignore.case = FALSE, include.dirs = FALSE, no.. = TRUE)
#Check file exists in path
if (length(list_filespath) != 0) {
foreach(m = 1:length(list_filespath)) %do%
{
sqlTable <- strsplit(list_filenames[m],split = ".csv")
sqlTable <- gsub("\\s", "", sqlTable)
sqlTable <- gsub("[^[:alnum:]]", "", sqlTable)
Output <- read.csv(file=list_filespath[m], header=TRUE, sep=",")
sqlDS <- RxSqlServerData(connectionString = sqlConnString,table = sqlTable)
rxDataStep(inData = Output, outFile = sqlDS,overwrite = TRUE)
}
}'
EXEC sp_execute_external_script
@language = N'R'
,@script = @RScript
No comments:
Post a Comment