r - How to Read Data from Multiple Workbooks having multiple Worksheets into R?

I am trying to read data from Multiple Workbooks which have Multiple Worksheets using r. There are 10 workbooks and each has data in two worksheets.

Following code works in pulling data from the first sheet. However I'd like to also pull the data on another sheet in the same workbook. I am not sure how to specify the sheet name in the following code.

library(purrr)

library(readxl)

library(dplyr)

library(tidyr)



data_path <- "C:/Desktop/Test"



files <- dir(data_path, pattern = "*.xlsx")





weights_data <- data.frame(filename = files) %>%

               mutate(file_contents = map(filename,

                                                 ~ read_excel(file.path

                                                              (data_path,  .))))



View(unnest(weights_Data))

edited Jan 2 at 22:30

Axeman

19.3k54559

asked Jan 2 at 22:25

Prah

495

read_excel(file.path, sheet=NULL) is the default, have you tried changing sheet=? If in doubt, for each file readxl::excel_sheets(file.path) will return a vector of all of the sheets (their names, I believe).

– r2evans
Jan 2 at 22:30

add a comment |

I am trying to read data from Multiple Workbooks which have Multiple Worksheets using r. There are 10 workbooks and each has data in two worksheets.

library(purrr)

library(readxl)

library(dplyr)

library(tidyr)



data_path <- "C:/Desktop/Test"



files <- dir(data_path, pattern = "*.xlsx")





weights_data <- data.frame(filename = files) %>%

               mutate(file_contents = map(filename,

                                                 ~ read_excel(file.path

                                                              (data_path,  .))))



View(unnest(weights_Data))

edited Jan 2 at 22:30

Axeman

19.3k54559

asked Jan 2 at 22:25

Prah

495

read_excel(file.path, sheet=NULL) is the default, have you tried changing sheet=? If in doubt, for each file readxl::excel_sheets(file.path) will return a vector of all of the sheets (their names, I believe).

– r2evans
Jan 2 at 22:30

add a comment |

I am trying to read data from Multiple Workbooks which have Multiple Worksheets using r. There are 10 workbooks and each has data in two worksheets.

library(purrr)

library(readxl)

library(dplyr)

library(tidyr)



data_path <- "C:/Desktop/Test"



files <- dir(data_path, pattern = "*.xlsx")





weights_data <- data.frame(filename = files) %>%

               mutate(file_contents = map(filename,

                                                 ~ read_excel(file.path

                                                              (data_path,  .))))



View(unnest(weights_Data))

edited Jan 2 at 22:30

Axeman

19.3k54559

asked Jan 2 at 22:25

Prah

495

I am trying to read data from Multiple Workbooks which have Multiple Worksheets using r. There are 10 workbooks and each has data in two worksheets.

library(purrr)

library(readxl)

library(dplyr)

library(tidyr)



data_path <- "C:/Desktop/Test"



files <- dir(data_path, pattern = "*.xlsx")





weights_data <- data.frame(filename = files) %>%

               mutate(file_contents = map(filename,

                                                 ~ read_excel(file.path

                                                              (data_path,  .))))



View(unnest(weights_Data))

edited Jan 2 at 22:30

Axeman

19.3k54559

asked Jan 2 at 22:25

Prah

495

edited Jan 2 at 22:30

Axeman

19.3k54559

asked Jan 2 at 22:25

Prah

495

edited Jan 2 at 22:30

Axeman

19.3k54559

edited Jan 2 at 22:30

Axeman

19.3k54559

edited Jan 2 at 22:30

Axeman

19.3k54559

asked Jan 2 at 22:25

Prah

495

asked Jan 2 at 22:25

Prah

495

asked Jan 2 at 22:25

Prah

495

read_excel(file.path, sheet=NULL) is the default, have you tried changing sheet=? If in doubt, for each file readxl::excel_sheets(file.path) will return a vector of all of the sheets (their names, I believe).

– r2evans
Jan 2 at 22:30

add a comment |

read_excel(file.path, sheet=NULL) is the default, have you tried changing sheet=? If in doubt, for each file readxl::excel_sheets(file.path) will return a vector of all of the sheets (their names, I believe).

– r2evans
Jan 2 at 22:30

read_excel(file.path, sheet=NULL) is the default, have you tried changing sheet=? If in doubt, for each file readxl::excel_sheets(file.path) will return a vector of all of the sheets (their names, I believe).

– r2evans
Jan 2 at 22:30

add a comment |

1 Answer
1

active

oldest

votes

read_excel takes another argument that lets you specify a specific sheet:

sheet: Sheet to read. Either a string (the name of a sheet), or an

       integer (the position of the sheet). Ignored if the sheet is

       specified via 'range'. If neither argument specifies the

       sheet, defaults to the first sheet.

With that, we need to expand your frame of paths to include sheets, easily done with readxl::excel_sheets which for a single path returns a vector of sheet names.

Iteratively, for discussion/walk-through, though only the last block is required:

library(tibble)

library(dplyr)

library(tidyr)

library(purrr)

library(readxl)



data_frame(

  path = list.files(path = "~/StackOverflow/Prah/", pattern = "*.xlsx", full.names = TRUE)

) %>%

  mutate(sheets = map(path, excel_sheets))

# # A tibble: 3 x 2

#   path                                        sheets   

#   <chr>                                       <list>   

# 1 "C:\Users\r2/StackOverflow/Prah/mt1.xlsx" <chr [2]>

# 2 "C:\Users\r2/StackOverflow/Prah/mt2.xlsx" <chr [2]>

# 3 "C:\Users\r2/StackOverflow/Prah/mt3.xlsx" <chr [2]>

This alone isn't immediately helpful, but we can unnest it:

data_frame(

  path = list.files(path = "~/StackOverflow/Prah/", pattern = "*.xlsx", full.names = TRUE)

) %>%

  mutate(sheets = map(path, excel_sheets)) %>%

  unnest(sheets)

# # A tibble: 6 x 2

#   path                                        sheets

#   <chr>                                       <chr> 

# 1 "C:\Users\r2/StackOverflow/Prah/mt1.xlsx" Sheet1

# 2 "C:\Users\r2/StackOverflow/Prah/mt1.xlsx" Sheet2

# 3 "C:\Users\r2/StackOverflow/Prah/mt2.xlsx" Sheet1

# 4 "C:\Users\r2/StackOverflow/Prah/mt2.xlsx" Sheet2

# 5 "C:\Users\r2/StackOverflow/Prah/mt3.xlsx" Sheet1

# 6 "C:\Users\r2/StackOverflow/Prah/mt3.xlsx" Sheet2

Now it should become clear that we now just need to iterate over each row using map2 or similar, and we get a nested tidy frame with data:

data_frame(

  path = list.files(path = "~/StackOverflow/Prah/", pattern = "*.xlsx", full.names = TRUE)

) %>%

  mutate(sheets = map(path, excel_sheets)) %>%

  unnest(sheets) %>%

  mutate(data = map2(path, sheets, ~ read_excel(path = .x, sheet = .y)))

# # A tibble: 6 x 3

#   path                                        sheets data              

#   <chr>                                       <chr>  <list>            

# 1 "C:\Users\r2/StackOverflow/Prah/mt1.xlsx" Sheet1 <tibble [32 x 11]>

# 2 "C:\Users\r2/StackOverflow/Prah/mt1.xlsx" Sheet2 <tibble [32 x 11]>

# 3 "C:\Users\r2/StackOverflow/Prah/mt2.xlsx" Sheet1 <tibble [32 x 11]>

# 4 "C:\Users\r2/StackOverflow/Prah/mt2.xlsx" Sheet2 <tibble [32 x 11]>

# 5 "C:\Users\r2/StackOverflow/Prah/mt3.xlsx" Sheet1 <tibble [32 x 11]>

# 6 "C:\Users\r2/StackOverflow/Prah/mt3.xlsx" Sheet2 <tibble [32 x 11]>

(I made a few excel workbooks, each had two sheets, each sheet had mtcars on it. Nothing fancy.)

answered Jan 2 at 22:40

r2evans

28.1k33159

Thanks this is perfect! However I do get this error mainly due to the data type: Error in View : Column X__6 can't be converted from character to numeric

– Prah
Jan 3 at 19:04

That sounds like a problem with one of your worksheets. Once you figure out which worksheet(s) show this problem, you should look into questions on SO about incorrect column classes (there are several on such).

– r2evans
Jan 3 at 19:16

If that error is solely in View, then I'd say it's not really a problem with the process or your data. I think I used View a few times in my first year of R, I haven't looked at it since. I tend to use str(data), and I think a lot of people like dplyr::glimpse.

– r2evans
Jan 3 at 21:32

I get the same error when I use glimpse. I am trying to pull data from only the sheets I really need instead of all sheets.

– Prah
Jan 4 at 1:50

This is the error message I get "Error in bind_rows_(x, .id) : Column X__6 can't be converted from character to numeric"

– Prah
Jan 4 at 2:17

|
show 1 more comment

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54013961%2fr-how-to-read-data-from-multiple-workbooks-having-multiple-worksheets-into-r%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

read_excel takes another argument that lets you specify a specific sheet:

sheet: Sheet to read. Either a string (the name of a sheet), or an

       integer (the position of the sheet). Ignored if the sheet is

       specified via 'range'. If neither argument specifies the

       sheet, defaults to the first sheet.

With that, we need to expand your frame of paths to include sheets, easily done with readxl::excel_sheets which for a single path returns a vector of sheet names.

Iteratively, for discussion/walk-through, though only the last block is required:

library(tibble)

library(dplyr)

library(tidyr)

library(purrr)

library(readxl)



data_frame(

  path = list.files(path = "~/StackOverflow/Prah/", pattern = "*.xlsx", full.names = TRUE)

) %>%

  mutate(sheets = map(path, excel_sheets))

# # A tibble: 3 x 2

#   path                                        sheets   

#   <chr>                                       <list>   

# 1 "C:\Users\r2/StackOverflow/Prah/mt1.xlsx" <chr [2]>

# 2 "C:\Users\r2/StackOverflow/Prah/mt2.xlsx" <chr [2]>

# 3 "C:\Users\r2/StackOverflow/Prah/mt3.xlsx" <chr [2]>

This alone isn't immediately helpful, but we can unnest it:

data_frame(

  path = list.files(path = "~/StackOverflow/Prah/", pattern = "*.xlsx", full.names = TRUE)

) %>%

  mutate(sheets = map(path, excel_sheets)) %>%

  unnest(sheets)

# # A tibble: 6 x 2

#   path                                        sheets

#   <chr>                                       <chr> 

# 1 "C:\Users\r2/StackOverflow/Prah/mt1.xlsx" Sheet1

# 2 "C:\Users\r2/StackOverflow/Prah/mt1.xlsx" Sheet2

# 3 "C:\Users\r2/StackOverflow/Prah/mt2.xlsx" Sheet1

# 4 "C:\Users\r2/StackOverflow/Prah/mt2.xlsx" Sheet2

# 5 "C:\Users\r2/StackOverflow/Prah/mt3.xlsx" Sheet1

# 6 "C:\Users\r2/StackOverflow/Prah/mt3.xlsx" Sheet2

Now it should become clear that we now just need to iterate over each row using map2 or similar, and we get a nested tidy frame with data:

data_frame(

  path = list.files(path = "~/StackOverflow/Prah/", pattern = "*.xlsx", full.names = TRUE)

) %>%

  mutate(sheets = map(path, excel_sheets)) %>%

  unnest(sheets) %>%

  mutate(data = map2(path, sheets, ~ read_excel(path = .x, sheet = .y)))

# # A tibble: 6 x 3

#   path                                        sheets data              

#   <chr>                                       <chr>  <list>            

# 1 "C:\Users\r2/StackOverflow/Prah/mt1.xlsx" Sheet1 <tibble [32 x 11]>

# 2 "C:\Users\r2/StackOverflow/Prah/mt1.xlsx" Sheet2 <tibble [32 x 11]>

# 3 "C:\Users\r2/StackOverflow/Prah/mt2.xlsx" Sheet1 <tibble [32 x 11]>

# 4 "C:\Users\r2/StackOverflow/Prah/mt2.xlsx" Sheet2 <tibble [32 x 11]>

# 5 "C:\Users\r2/StackOverflow/Prah/mt3.xlsx" Sheet1 <tibble [32 x 11]>

# 6 "C:\Users\r2/StackOverflow/Prah/mt3.xlsx" Sheet2 <tibble [32 x 11]>

(I made a few excel workbooks, each had two sheets, each sheet had mtcars on it. Nothing fancy.)

answered Jan 2 at 22:40

r2evans

28.1k33159

Thanks this is perfect! However I do get this error mainly due to the data type: Error in View : Column X__6 can't be converted from character to numeric

– Prah
Jan 3 at 19:04

That sounds like a problem with one of your worksheets. Once you figure out which worksheet(s) show this problem, you should look into questions on SO about incorrect column classes (there are several on such).

– r2evans
Jan 3 at 19:16

If that error is solely in View, then I'd say it's not really a problem with the process or your data. I think I used View a few times in my first year of R, I haven't looked at it since. I tend to use str(data), and I think a lot of people like dplyr::glimpse.

– r2evans
Jan 3 at 21:32

I get the same error when I use glimpse. I am trying to pull data from only the sheets I really need instead of all sheets.

– Prah
Jan 4 at 1:50

This is the error message I get "Error in bind_rows_(x, .id) : Column X__6 can't be converted from character to numeric"

– Prah
Jan 4 at 2:17

|
show 1 more comment

read_excel takes another argument that lets you specify a specific sheet:

sheet: Sheet to read. Either a string (the name of a sheet), or an

       integer (the position of the sheet). Ignored if the sheet is

       specified via 'range'. If neither argument specifies the

       sheet, defaults to the first sheet.

With that, we need to expand your frame of paths to include sheets, easily done with readxl::excel_sheets which for a single path returns a vector of sheet names.

Iteratively, for discussion/walk-through, though only the last block is required:

library(tibble)

library(dplyr)

library(tidyr)

library(purrr)

library(readxl)



data_frame(

  path = list.files(path = "~/StackOverflow/Prah/", pattern = "*.xlsx", full.names = TRUE)

) %>%

  mutate(sheets = map(path, excel_sheets))

# # A tibble: 3 x 2

#   path                                        sheets   

#   <chr>                                       <list>   

# 1 "C:\Users\r2/StackOverflow/Prah/mt1.xlsx" <chr [2]>

# 2 "C:\Users\r2/StackOverflow/Prah/mt2.xlsx" <chr [2]>

# 3 "C:\Users\r2/StackOverflow/Prah/mt3.xlsx" <chr [2]>

This alone isn't immediately helpful, but we can unnest it:

data_frame(

  path = list.files(path = "~/StackOverflow/Prah/", pattern = "*.xlsx", full.names = TRUE)

) %>%

  mutate(sheets = map(path, excel_sheets)) %>%

  unnest(sheets)

# # A tibble: 6 x 2

#   path                                        sheets

#   <chr>                                       <chr> 

# 1 "C:\Users\r2/StackOverflow/Prah/mt1.xlsx" Sheet1

# 2 "C:\Users\r2/StackOverflow/Prah/mt1.xlsx" Sheet2

# 3 "C:\Users\r2/StackOverflow/Prah/mt2.xlsx" Sheet1

# 4 "C:\Users\r2/StackOverflow/Prah/mt2.xlsx" Sheet2

# 5 "C:\Users\r2/StackOverflow/Prah/mt3.xlsx" Sheet1

# 6 "C:\Users\r2/StackOverflow/Prah/mt3.xlsx" Sheet2

Now it should become clear that we now just need to iterate over each row using map2 or similar, and we get a nested tidy frame with data:

data_frame(

  path = list.files(path = "~/StackOverflow/Prah/", pattern = "*.xlsx", full.names = TRUE)

) %>%

  mutate(sheets = map(path, excel_sheets)) %>%

  unnest(sheets) %>%

  mutate(data = map2(path, sheets, ~ read_excel(path = .x, sheet = .y)))

# # A tibble: 6 x 3

#   path                                        sheets data              

#   <chr>                                       <chr>  <list>            

# 1 "C:\Users\r2/StackOverflow/Prah/mt1.xlsx" Sheet1 <tibble [32 x 11]>

# 2 "C:\Users\r2/StackOverflow/Prah/mt1.xlsx" Sheet2 <tibble [32 x 11]>

# 3 "C:\Users\r2/StackOverflow/Prah/mt2.xlsx" Sheet1 <tibble [32 x 11]>

# 4 "C:\Users\r2/StackOverflow/Prah/mt2.xlsx" Sheet2 <tibble [32 x 11]>

# 5 "C:\Users\r2/StackOverflow/Prah/mt3.xlsx" Sheet1 <tibble [32 x 11]>

# 6 "C:\Users\r2/StackOverflow/Prah/mt3.xlsx" Sheet2 <tibble [32 x 11]>

(I made a few excel workbooks, each had two sheets, each sheet had mtcars on it. Nothing fancy.)

answered Jan 2 at 22:40

r2evans

28.1k33159

Thanks this is perfect! However I do get this error mainly due to the data type: Error in View : Column X__6 can't be converted from character to numeric

– Prah
Jan 3 at 19:04

That sounds like a problem with one of your worksheets. Once you figure out which worksheet(s) show this problem, you should look into questions on SO about incorrect column classes (there are several on such).

– r2evans
Jan 3 at 19:16

If that error is solely in View, then I'd say it's not really a problem with the process or your data. I think I used View a few times in my first year of R, I haven't looked at it since. I tend to use str(data), and I think a lot of people like dplyr::glimpse.

– r2evans
Jan 3 at 21:32

I get the same error when I use glimpse. I am trying to pull data from only the sheets I really need instead of all sheets.

– Prah
Jan 4 at 1:50

This is the error message I get "Error in bind_rows_(x, .id) : Column X__6 can't be converted from character to numeric"

– Prah
Jan 4 at 2:17

|
show 1 more comment

read_excel takes another argument that lets you specify a specific sheet:

sheet: Sheet to read. Either a string (the name of a sheet), or an

       integer (the position of the sheet). Ignored if the sheet is

       specified via 'range'. If neither argument specifies the

       sheet, defaults to the first sheet.

With that, we need to expand your frame of paths to include sheets, easily done with readxl::excel_sheets which for a single path returns a vector of sheet names.

Iteratively, for discussion/walk-through, though only the last block is required:

library(tibble)

library(dplyr)

library(tidyr)

library(purrr)

library(readxl)



data_frame(

  path = list.files(path = "~/StackOverflow/Prah/", pattern = "*.xlsx", full.names = TRUE)

) %>%

  mutate(sheets = map(path, excel_sheets))

# # A tibble: 3 x 2

#   path                                        sheets   

#   <chr>                                       <list>   

# 1 "C:\Users\r2/StackOverflow/Prah/mt1.xlsx" <chr [2]>

# 2 "C:\Users\r2/StackOverflow/Prah/mt2.xlsx" <chr [2]>

# 3 "C:\Users\r2/StackOverflow/Prah/mt3.xlsx" <chr [2]>

This alone isn't immediately helpful, but we can unnest it:

data_frame(

  path = list.files(path = "~/StackOverflow/Prah/", pattern = "*.xlsx", full.names = TRUE)

) %>%

  mutate(sheets = map(path, excel_sheets)) %>%

  unnest(sheets)

# # A tibble: 6 x 2

#   path                                        sheets

#   <chr>                                       <chr> 

# 1 "C:\Users\r2/StackOverflow/Prah/mt1.xlsx" Sheet1

# 2 "C:\Users\r2/StackOverflow/Prah/mt1.xlsx" Sheet2

# 3 "C:\Users\r2/StackOverflow/Prah/mt2.xlsx" Sheet1

# 4 "C:\Users\r2/StackOverflow/Prah/mt2.xlsx" Sheet2

# 5 "C:\Users\r2/StackOverflow/Prah/mt3.xlsx" Sheet1

# 6 "C:\Users\r2/StackOverflow/Prah/mt3.xlsx" Sheet2

Now it should become clear that we now just need to iterate over each row using map2 or similar, and we get a nested tidy frame with data:

data_frame(

  path = list.files(path = "~/StackOverflow/Prah/", pattern = "*.xlsx", full.names = TRUE)

) %>%

  mutate(sheets = map(path, excel_sheets)) %>%

  unnest(sheets) %>%

  mutate(data = map2(path, sheets, ~ read_excel(path = .x, sheet = .y)))

# # A tibble: 6 x 3

#   path                                        sheets data              

#   <chr>                                       <chr>  <list>            

# 1 "C:\Users\r2/StackOverflow/Prah/mt1.xlsx" Sheet1 <tibble [32 x 11]>

# 2 "C:\Users\r2/StackOverflow/Prah/mt1.xlsx" Sheet2 <tibble [32 x 11]>

# 3 "C:\Users\r2/StackOverflow/Prah/mt2.xlsx" Sheet1 <tibble [32 x 11]>

# 4 "C:\Users\r2/StackOverflow/Prah/mt2.xlsx" Sheet2 <tibble [32 x 11]>

# 5 "C:\Users\r2/StackOverflow/Prah/mt3.xlsx" Sheet1 <tibble [32 x 11]>

# 6 "C:\Users\r2/StackOverflow/Prah/mt3.xlsx" Sheet2 <tibble [32 x 11]>

(I made a few excel workbooks, each had two sheets, each sheet had mtcars on it. Nothing fancy.)

answered Jan 2 at 22:40

r2evans

28.1k33159

read_excel takes another argument that lets you specify a specific sheet:

sheet: Sheet to read. Either a string (the name of a sheet), or an

       integer (the position of the sheet). Ignored if the sheet is

       specified via 'range'. If neither argument specifies the

       sheet, defaults to the first sheet.

With that, we need to expand your frame of paths to include sheets, easily done with readxl::excel_sheets which for a single path returns a vector of sheet names.

Iteratively, for discussion/walk-through, though only the last block is required:

library(tibble)

library(dplyr)

library(tidyr)

library(purrr)

library(readxl)



data_frame(

  path = list.files(path = "~/StackOverflow/Prah/", pattern = "*.xlsx", full.names = TRUE)

) %>%

  mutate(sheets = map(path, excel_sheets))

# # A tibble: 3 x 2

#   path                                        sheets   

#   <chr>                                       <list>   

# 1 "C:\Users\r2/StackOverflow/Prah/mt1.xlsx" <chr [2]>

# 2 "C:\Users\r2/StackOverflow/Prah/mt2.xlsx" <chr [2]>

# 3 "C:\Users\r2/StackOverflow/Prah/mt3.xlsx" <chr [2]>

This alone isn't immediately helpful, but we can unnest it:

data_frame(

  path = list.files(path = "~/StackOverflow/Prah/", pattern = "*.xlsx", full.names = TRUE)

) %>%

  mutate(sheets = map(path, excel_sheets)) %>%

  unnest(sheets)

# # A tibble: 6 x 2

#   path                                        sheets

#   <chr>                                       <chr> 

# 1 "C:\Users\r2/StackOverflow/Prah/mt1.xlsx" Sheet1

# 2 "C:\Users\r2/StackOverflow/Prah/mt1.xlsx" Sheet2

# 3 "C:\Users\r2/StackOverflow/Prah/mt2.xlsx" Sheet1

# 4 "C:\Users\r2/StackOverflow/Prah/mt2.xlsx" Sheet2

# 5 "C:\Users\r2/StackOverflow/Prah/mt3.xlsx" Sheet1

# 6 "C:\Users\r2/StackOverflow/Prah/mt3.xlsx" Sheet2

Now it should become clear that we now just need to iterate over each row using map2 or similar, and we get a nested tidy frame with data:

data_frame(

  path = list.files(path = "~/StackOverflow/Prah/", pattern = "*.xlsx", full.names = TRUE)

) %>%

  mutate(sheets = map(path, excel_sheets)) %>%

  unnest(sheets) %>%

  mutate(data = map2(path, sheets, ~ read_excel(path = .x, sheet = .y)))

# # A tibble: 6 x 3

#   path                                        sheets data              

#   <chr>                                       <chr>  <list>            

# 1 "C:\Users\r2/StackOverflow/Prah/mt1.xlsx" Sheet1 <tibble [32 x 11]>

# 2 "C:\Users\r2/StackOverflow/Prah/mt1.xlsx" Sheet2 <tibble [32 x 11]>

# 3 "C:\Users\r2/StackOverflow/Prah/mt2.xlsx" Sheet1 <tibble [32 x 11]>

# 4 "C:\Users\r2/StackOverflow/Prah/mt2.xlsx" Sheet2 <tibble [32 x 11]>

# 5 "C:\Users\r2/StackOverflow/Prah/mt3.xlsx" Sheet1 <tibble [32 x 11]>

# 6 "C:\Users\r2/StackOverflow/Prah/mt3.xlsx" Sheet2 <tibble [32 x 11]>

(I made a few excel workbooks, each had two sheets, each sheet had mtcars on it. Nothing fancy.)

answered Jan 2 at 22:40

r2evans

28.1k33159

answered Jan 2 at 22:40

r2evans

28.1k33159

answered Jan 2 at 22:40

r2evans

28.1k33159

answered Jan 2 at 22:40

r2evans

28.1k33159

Thanks this is perfect! However I do get this error mainly due to the data type: Error in View : Column X__6 can't be converted from character to numeric

– Prah
Jan 3 at 19:04

That sounds like a problem with one of your worksheets. Once you figure out which worksheet(s) show this problem, you should look into questions on SO about incorrect column classes (there are several on such).

– r2evans
Jan 3 at 19:16

If that error is solely in View, then I'd say it's not really a problem with the process or your data. I think I used View a few times in my first year of R, I haven't looked at it since. I tend to use str(data), and I think a lot of people like dplyr::glimpse.

– r2evans
Jan 3 at 21:32

I get the same error when I use glimpse. I am trying to pull data from only the sheets I really need instead of all sheets.

– Prah
Jan 4 at 1:50

This is the error message I get "Error in bind_rows_(x, .id) : Column X__6 can't be converted from character to numeric"

– Prah
Jan 4 at 2:17

|
show 1 more comment

Thanks this is perfect! However I do get this error mainly due to the data type: Error in View : Column X__6 can't be converted from character to numeric

– Prah
Jan 3 at 19:04

That sounds like a problem with one of your worksheets. Once you figure out which worksheet(s) show this problem, you should look into questions on SO about incorrect column classes (there are several on such).

– r2evans
Jan 3 at 19:16

If that error is solely in View, then I'd say it's not really a problem with the process or your data. I think I used View a few times in my first year of R, I haven't looked at it since. I tend to use str(data), and I think a lot of people like dplyr::glimpse.

– r2evans
Jan 3 at 21:32

I get the same error when I use glimpse. I am trying to pull data from only the sheets I really need instead of all sheets.

– Prah
Jan 4 at 1:50

This is the error message I get "Error in bind_rows_(x, .id) : Column X__6 can't be converted from character to numeric"

– Prah
Jan 4 at 2:17

Thanks this is perfect! However I do get this error mainly due to the data type: Error in View : Column X__6 can't be converted from character to numeric

– Prah
Jan 3 at 19:04

That sounds like a problem with one of your worksheets. Once you figure out which worksheet(s) show this problem, you should look into questions on SO about incorrect column classes (there are several on such).

– r2evans
Jan 3 at 19:16

If that error is solely in View, then I'd say it's not really a problem with the process or your data. I think I used View a few times in my first year of R, I haven't looked at it since. I tend to use str(data), and I think a lot of people like dplyr::glimpse.

– r2evans
Jan 3 at 21:32

I get the same error when I use glimpse. I am trying to pull data from only the sheets I really need instead of all sheets.

– Prah
Jan 4 at 1:50

This is the error message I get "Error in bind_rows_(x, .id) : Column X__6 can't be converted from character to numeric"

– Prah
Jan 4 at 2:17

|
show 1 more comment

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

Search This Blog

Ufyukyu