In continuation to my previous posts on SQL Server Export/Import excel using R script Part 1, Part 2, Part 3 & Part 4. In this post, let us see how to import multiple excel files in a folder (including sub folders) into different SQL Server table created dynamically based on excel file structure.
For this example, I am going to make use of excel files generated using second script in Part 2 post.
Also I have created sub folder named "Copy" and copied the same set of files into it, I have done this to ensure script has traversed through all the folders and works fine.
Also to test if the script can ignore the empty sheets, I have manually opened 1 or 2 excel files added an empty sheet and saved it.
In this example, SQL Server table name is made as file name + sheet name and removed all special characters and spaces if any from file/sheet name to make sure SQL Server table name is unique and as per MSSQL standard.
As you can see in above screenshot, table names with Copy as prefix are imported from sub folders.
Below is the script that iterates through multiple files with multiple sheets and create them as separate table in SQL Server:
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(readxl);
library(foreach);
sqlConnString <- "Driver=SQL Server;Server=''LAPTOP-LCUB6HBB''; Database=Staging;Uid=sa;Pwd=***"
list_filespath <- list.files(path = "H:/Temp/Blog/ImportExportExcel", pattern = "*.xlsx", 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 = "*.xlsx", 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%
{
filepath_var <- list_filespath[m]
tab_names <- excel_sheets(path = filepath_var)
foreach(n = 1:length(tab_names)) %do%
{
sqlTable <- paste(strsplit(list_filenames[m],split = ".xlsx"),tab_names[n],sep = "_")
sqlTable <- gsub("\\s", "", sqlTable)
sqlTable <- gsub("[^[:alnum:]]", "", sqlTable)
Output <- read_excel(path = filepath_var, sheet = n)
#check if the sheet has header
if (dim(Output)[2] != 0) {
sqlDS <- RxSqlServerData(connectionString = sqlConnString,table = sqlTable)
rxDataStep(inData = Output, outFile = sqlDS,overwrite = TRUE)
}
}
}
}'
EXEC sp_execute_external_script
@language = N'R'
,@script = @RScript
More information on this topic, to be continued in part 6 ..
For this example, I am going to make use of excel files generated using second script in Part 2 post.
Also I have created sub folder named "Copy" and copied the same set of files into it, I have done this to ensure script has traversed through all the folders and works fine.
Also to test if the script can ignore the empty sheets, I have manually opened 1 or 2 excel files added an empty sheet and saved it.
In this example, SQL Server table name is made as file name + sheet name and removed all special characters and spaces if any from file/sheet name to make sure SQL Server table name is unique and as per MSSQL standard.
As you can see in above screenshot, table names with Copy as prefix are imported from sub folders.
Below is the script that iterates through multiple files with multiple sheets and create them as separate table in SQL Server:
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(readxl);
library(foreach);
sqlConnString <- "Driver=SQL Server;Server=''LAPTOP-LCUB6HBB''; Database=Staging;Uid=sa;Pwd=***"
list_filespath <- list.files(path = "H:/Temp/Blog/ImportExportExcel", pattern = "*.xlsx", 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 = "*.xlsx", 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%
{
filepath_var <- list_filespath[m]
tab_names <- excel_sheets(path = filepath_var)
foreach(n = 1:length(tab_names)) %do%
{
sqlTable <- paste(strsplit(list_filenames[m],split = ".xlsx"),tab_names[n],sep = "_")
sqlTable <- gsub("\\s", "", sqlTable)
sqlTable <- gsub("[^[:alnum:]]", "", sqlTable)
Output <- read_excel(path = filepath_var, sheet = n)
#check if the sheet has header
if (dim(Output)[2] != 0) {
sqlDS <- RxSqlServerData(connectionString = sqlConnString,table = sqlTable)
rxDataStep(inData = Output, outFile = sqlDS,overwrite = TRUE)
}
}
}
}'
EXEC sp_execute_external_script
@language = N'R'
,@script = @RScript
More information on this topic, to be continued in part 6 ..
No comments:
Post a Comment