Reading multiple Excel files and merge them sheet-wise

0 votes
I have about 50 Excel documents. They contain the ensuing sheets (Sheet1, Sheet2, Sheet3, and Sheet4). Additionally, each sheet's columns are the same across all 50 files. I must open each spreadsheet file and combine the data from the four sheets into four separate dataframes and excel files.

To read the 50 files and store the workbook as a temporary variable, I attempted to include a for loop I'm having trouble reading each page from them and binding it to a final variable, though.
Dec 15, 2022 in Others by Kithuzzz
• 38,010 points
517 views

1 answer to this question.

0 votes

This will analyse each Excel and combine its sheets into a single DF. A list of dataframes will be returned by the function. After the function returns the list, the dataframes are bound together.

library(readxl)
library(tidyverse)

files <- list.files(path="C:/data/55423/originals/r_test/",
                        pattern="*.xlsx",
                        full.names=TRUE)

allsheets <- function(filename) {
    sheets <- readxl::excel_sheets(filename)
    x <- lapply(sheets, function(x) 
        transform(readxl::read_excel(filename, sheet=x), sheetname=x))
    return(x)
}

df <- lapply(files, allsheets)%>% bind_rows()

I hope this helps you.

answered Dec 15, 2022 by narikkadan
• 63,420 points

Related Questions In Others

0 votes
1 answer

Reading/parsing Excel (xls) files with Python

I highly recommend xlrd for reading .xls files. But there are ...READ MORE

answered Sep 21, 2022 in Others by narikkadan
• 63,420 points
475 views
0 votes
1 answer

Download multiple excel files linked through urls in R

Try something along the lines of: for (i ...READ MORE

answered Sep 23, 2022 in Others by narikkadan
• 63,420 points
988 views
0 votes
1 answer

Convert Excel and Word files to PDF Using ruby

 You can combine some: For excel files - ...READ MORE

answered Sep 26, 2022 in Others by narikkadan
• 63,420 points
900 views
0 votes
1 answer
0 votes
1 answer

Excel, How to split cells by comma delimiter into new cells

The Excel manual method: choose Text to Column ...READ MORE

answered Oct 3, 2022 in Others by narikkadan
• 63,420 points
594 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
876 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
3,183 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
480 views
0 votes
1 answer

How do I merge multiple excel files to a single excel file

You copy a worksheet from before each ...READ MORE

answered Dec 24, 2022 in Others by narikkadan
• 63,420 points
573 views
0 votes
1 answer

Crawling through multiple excel files, match and copy data to master file

One application only. It would be quicker ...READ MORE

answered Jan 12, 2023 in Others by narikkadan
• 63,420 points
356 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP