** New to R and need help with : Loading multiple sheets into DB, assign sheetname as column name


I'm very new to R and Rstudio and am just learning. I have tried to search all the topics but did not find this specific case that I'm looking for.

Here is what I have:
I have multiple xlsx files which I need to read. Each file has multiple sheets, I am only going to read specific sheets with "sample" in sheet names.
Then I need to read these very specific sheets and load info to a table in database.

I have the code ready to do all the above. What I need and can not figure out how to do is "How to add sheetName to the list of columns I am loading to DB".

I really appreciate if someone can help.

Thank you

Here is my sample code:

File1 <- "C:/.../File1.xlsx"

File1_read <- readxl::excel_sheets( File1)
sample_File1_read <- sheets[grepl("sample", File1_read, ignore.case = TRUE)]

File1_sheet <- map_dfr(sample_File1_read
, ~read_excel(path = File1
, col_names = c("ColA"
,"ColB" )
,sheet = .x), id = .x)

Driver = 'ODBC Driver ***',
Server = "MyServer",
Database = "MyDB",
trusted_connection = 'yes'

schemaname <- 'dbo'

tblname_File1 <- 'tRaw_File1'

Id(schema = schemaname, table = tblname_File1),
value = File1_sheet,
overwrite = T)


This text will be hidden

Welcome. Is the sheetname already in the read? I couldn't quite work it out from your code (it is missing something in the sample_file_read.

Anyway, I couldn't also work out if your id = .x was correct, but that should get the sheet name in to a column, that would you could load in to the database.

If that is the issue, then perhaps something like this:


File1 <- "https://www.health.gov.au/sites/default/files/documents/2021/10/covid-19-vaccination-vaccination-data-25-october-2021.xlsx"

temp <- tempfile()
download.file(File1, temp)

File1_read <- readxl::excel_sheets(temp)
sample_File1_read <- File1_read %>% 
  .[grepl("Vacc", .)] %>% 

File1_sheet <- map_df(sample_File1_read
                      , ~read_excel(path = temp,
                                    skip = 2,
                                    sheet = .x) ,
                      .id = "sheet")

# then the rest of it.
1 Like

THANK YOU so much, this is what exactly I was looking for.

Appreciate it

1 Like

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.