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

[KatZ]

Hello,

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)

con<-DBI::dbConnect(odbc::odbc(),
Driver = 'ODBC Driver ***',
Server = "MyServer",
Database = "MyDB",
trusted_connection = 'yes'
)

schemaname <- 'dbo'

tblname_File1 <- 'tRaw_File1'

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

Summary

This text will be hidden

[williaml]

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:

library(readxl)
library(tidyverse)

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", .)] %>% 
  set_names()


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

# then the rest of it.

[KatZ]

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

Appreciate it