R - "Error in file(file, ifelse(append, "a", "w")) : cannot open the connection"

Question

I have a data frame of this form:

X1                                      X2 X3                       X4
R290601 WOVEN TWILL                    001  6                      231
009-1373  *with perforated L3-0,3      152 NA                     <NA>
R481400 THREAD                       A1282 12                    A0399
0091375 PURE SOCK                      001  6                      072
R282380 SOFTLIN W/FELT                 007  6                      072
R282480 MICROFIBRE                     001  6                      F72
R281200 ARTIFICIAL                   A0638  6                      072

I want to loop through the rows, for every row check the name of the 1st column (X1) & create a folder in my computer that will have the same name and inside this folder, create sub folders that will have the same names as their respective columns (X2), (X3), (X4). When I run the script, I can only see folder R290601 WOVEN TWILL is created, with sub folders 001, 6 and 231, but not the rest, and I get this error :

Error in file(file, ifelse(append, "a", "w")) : cannot open the connection

Additionally, I get this warning for the 2nd row :

In dir.create(paste0(pth, df$X1[i])) : cannot create dir 'C:\Users\Dev\Desktop\Joe\009-1373 *with perforated L3-0,3', reason 'Invalid argument'

My code is this:

getwd()
setwd("C:/Users/Dev/Desktop/Joe")

library(xlsx)
library(rJava)
library(xlsxjars)

#get file names
f = list.files("./")
#read files
dat = lapply(f, function(i){
  x = read.xlsx(i, sheetIndex = 1, sheetName = NULL, startRow = 24,
                endRow = NULL, as.data.frame = TRUE, header = FALSE)
  #return columns with names and colors
  x = x[, c(1, 2, 3, 4), drop=FALSE] 
  #return the data
  x
})

library(plyr)
df1 <- ldply(dat, data.frame) ## convert list into a dataframe

#remove NA's
complete.cases(df1)
x <- df1[complete.cases(df1),]
str(x)

#show only rows that start with numbers or 1 letter and then numbers
df <-df1[grepl("^[0-9]|^[a-zA-Z][0-9].*", df1$X1), ]
print(df)

pth <- "C:/Users/Dev/Desktop/Joe/"

# Iterate within each row of df
for(i in 1:nrow(df)){
  # Create 1st path
  dir.create(paste0(pth , df$X1[i]))
  # Create 2nd and 3rd paths
  dir.create(paste0(pth, df$X1[i], "/",df$X2[i]))
  dir.create(paste0(pth, df$X1[i], "/",df$X3[i]))
  dir.create(paste0(pth, df$X1[i], "/",df$X4[i]))
 
  # write data.frame row as txt
  write.table(df[i, ], file=paste0(pth, df$X1[i], "/", df$X1[i],".txt"), sep=";")
}

Why do I get this error and how could I see all the folders with their corresponding sub folders?

Answer 1

library(readxl)
library(tidyverse)
## First read the dataframe taht contains Folder & Sub-folder names
df <- read_excel("C:/Users/Dev/Desktop/Joe/df.xlsx") 


## Special characters like "\ / : * ? " < > |" cann't be present in filename,
## So first remove it from the df for every column.
df <- df %>% 
  mutate(across(everything(), .fns = function(x) gsub('[[:punct:]]','', x))) ## '[[:punct:]]' for the special character


setwd('C:/Users/Dev/Desktop/Joe') ## Replace with your working directory



for(i in 1:nrow(df)){
  
  df_name <- df[i, ] %>% 
    select_if(~ !is.na(.x)) %>% 
    select_if(~ .x != 'NA') ## if NA is character & you don't need it
  
  for(j in 1:ncol(df_name)){
    
    if(!is.na(df_name[,j])){
      if(!dir.exists(as.character(df_name[,j]))){
        dir.create(as.character(df_name[,j]), recursive = TRUE)
        setwd(as.character(df_name[,j])) ## Set wd to the newly created dir
        ## X1 --> X2 --> X3 --> X4
      }
    }
    
  }
  
  setwd('C:/Users/Dev/Desktop/Joe') ## Again go back to the main wd

}

## IF you want the folder like this : X1 --> X2, X3, X4 then 

for(i in 1:nrow(df)){
  
  df_name <- df[i, ] %>% 
    select_if(~ !is.na(.x)) %>% 
    select_if(~ .x != 'NA') ## if NA is character & you don't need it
  
  if(!is.na(df_name[,1])){
    if(!dir.exists(as.character(df_name[,1]))){
      dir.create(as.character(df_name[,1]), recursive = TRUE)
      setwd(as.character(df_name[,1])) ## Set wd to the newly created dir
    }
  }
  
  for(j in 2:ncol(df_name)){
    
    if(!is.na(df_name[,j])){
      if(!dir.exists(as.character(df_name[,j]))){
        dir.create(as.character(df_name[,j]), recursive = TRUE)
      }
    }
    
  }
  
  setwd('C:/Users/Dev/Desktop/Joe') ## Again go back to the main wd
  
}