SQL Server Export/Import excel using R script - Part 5

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:



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 ..


See Also:

No comments: