将xlsb文件文件夹导入R [英] Import Folder of xlsb files into R

问题描述

我有一个Excel XLSB格式的每日报告文件夹，现在我正尝试导入该文件夹中的所有文件并绑定到R中的一个数据框中.我有将多个CSV文件的文件夹导入R中的经验，代码如下:

I have a folder of daily report in Excel XLSB format, now I am trying to import all files in the folder and bind into one data frame in R. I have experience with importing a folder of multiple CSV files into R, the code as below:

library(tidyverse)
setwd("C:/Folder_Path")
file_path <- list.files(pattern="*.csv")
combined_df <- lapply(file_path, read_csv) %>% bind_rows

我试图在这种情况下实现此代码以导入XLSB文件，我需要的电子表格为"Sheet1"，并且标题从第4行开始，因此我创建了一个自定义函数来做到这一点:

I tried to implement this code into this case to import XLSB files, the spreadsheet I need is "Sheet1" and the header starts from row #4, therefore i created a custom function to do this:

library(tidyverse)
binary_import <- function(x){
    readxl::read_excel(x, sheet="Sheet1", skip=3)}
setwd("C:/Folder_Path")
file_path <- list.files(pattern="*.csv")
combined_df <- lapply(file_path, binary_import) %>% bind_rows

然后我注意到readxl软件包不支持xlsb扩展.因为有成百上千个文件，所以有什么替代方法可以让我完成工作，而不是手动将文件转换为csv格式.

Then i noticed that readxl package does not support xlsb extension. Is there any workarounds available for me to complete the job instead of manually converting the files into csv format because there are hundreds of files.

推荐答案

您的代码可以正常工作，但xlsb部分除外.您可以使用excel.link包读取xlsb

Your code works except for the xlsb part. You could use excel.link package for reading xlsb

library(tidyverse)
library (excel.link)

setwd("C:/Folder_Path")
file_path <- list.files(pattern="*\\.xlsb")
allxlsb <- NULL


for(i in 1:length(file_path)){
  temp <- xl.read.file(filename = file_path[i], xl.sheet = "Sheet1", top.left.cell = "A4")
  allxlsb <- rbind(allxlsb, temp)
}

这篇关于将xlsb文件文件夹导入R的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！