R:需要帮助将Excel工作表转换为CSV,同时正确转换列类型&检查.xls或.xlsx扩展名

3z6pesqy  于 12个月前  发布在  其他
关注(0)|答案(1)|浏览(125)

我对R & scripting相当陌生,我正在尝试编写一个脚本,将Excel文件中的每个工作表转换为自己的CSV文件。这个脚本将用于许多Excel文件,这些文件的工作表数量,每行/列数不同,并且可能有空值的列。此外,除了我自己,其他人可能也会使用这个脚本。所以我不想使用太多的软件包,他们将不得不安装。
以下是我希望脚本做的事情的概述:
1.接受用户输入的文件名
1.检查文件的扩展名是.xls还是.xlsx
1.将正确的扩展名(.xls或.xlsx)附加到文件名
1.读取excel文件中的每张工作表
1.保持列数据类型相同(我稍后会详细解释)
1.将每个工作表写入其自己的CSV文件
我在网上搜索并尝试了各种方法来将excel工作表转换为csv文件,但我发现的大多数方法都没有太大的运气。其中一种方法是Yifu Yan's answer here。然而,正如他提到的那样,列类型没有正确转换。
我尝试修改Yifu Yan的代码,试图自己解决列类型转换,但没有成功。下面是我使用的代码(使用readxl和purrr包):

file <- readline("Enter file name: ")
# Use if/else, for, or while loop to check file extension, which will replace “.xlsx” in the following line with the determined extension
path <- paste0(file, “.xlsx”)
sheet_names <- readxl::excel_sheets(path)
df <- purrr::map(sheet_names,  ~readxl::read_excel(path, .x, col_types = "guess", col_names = FALSE))
purrr::walk2(df, sheet_names, write.csv(.x, paste0(file, "-", .y, ".csv"), col_names = FALSE))

字符串
当我用Excel文件测试代码时,我发现有些列转换得很好,但有些列没有。下面是转换后数据应该是什么样子的例子:
| 第1栏|第2栏|第3栏|第4栏|第5栏|
| --|--|--|--|--|
| 二二三三点零九| 501 | 1234567 |AB| 2011年3月20日6:09:17|
| 1122.09| 502 | 1234569 |AC| 2011年3月20日6:12:25|
它实际上是什么样子的:
| 第1栏|第2栏|第3栏|第4栏|第5栏|
| --|--|--|--|--|
| 2233.090000000001| 501 | 1234567 |AB| 40622.25644675926|
| 1122.090000000002| 502 | 1234569 |AC|电话:40622.258622685185传真:40622.258622685185|
我需要帮助的三件事:
1.如何确保列类型/值在转换过程中保持完全相同
1.我可以使用什么函数来检查文件扩展名(如果需要的话,我可以自己弄清楚如何编写循环)
1.如果可能的话,使用base而不是purrr。我更希望唯一的非base包是readxl(或xlsx),但如果不是也没关系
编辑:因为我不能得到任何其他的工作,而只使用一个包(除了工具),我将只使用readxl和tidyverse(包括readr和purrr)。不理想,但我需要它。

xdnvmnnf

xdnvmnnf1#

列类型

第一个是最难的,我担心你不会找到一个适合所有的解决方案。我认识的大多数Excel读者都很努力地确定列类型。这不是一件容易的事情,因为Excel单元格包含任意值,和R必须找到一个合适的启发式算法来得到正确的细胞类型。在我看来,这种猜测在大多数情况下都有效,即使在它不起作用的情况下,通常情况下,R是正确的,只是Excel中的格式是误导。
例如,你的第一列在我看来是完全有效的。R识别数字格式并读入浮点数。
在浮点运算(cf. Circle 1 - Falling into the Floating Point Trap)中,这两个值实际上是相同的:

all.equal(2233.0900000000001, 2233.09)
# [1] TRUE

字符串
对于你的日期问题,它是非常相似的。在Excel内部,日期存储为数字(时间存储为分数),在下面的截图中,AB列包含相同的数字,但B列具有datetime格式:


的数据
转换有点棘手,并且需要知道Excel日期从1899-12-30开始(无论出于何种原因)


as.POSIXct(40622.25644675926 * 24 *60*60, origin = "1899-12-30", tz = "GMT")
# [1] "2011-03-20 06:09:17 GMT"


有了这些微妙之处,既然你对CSV格式感兴趣(除了数字和文本之外,它不知道其他类型),为什么不对所有列使用文本呢?

readxl::read_excel(path, .x, col_types = "text")

文件扩展名

您可以使用tools::file_exttools是一个基本库,因此无论如何都应该安装,但实现非常简单:

function (x) {
  pos <- regexpr("\\.([[:alnum:]]+)$", x)
  ifelse(pos > -1L, substring(x, pos + 1L), "")
}


因此,您可以轻松地通过tools::file_ext(file)获得文件扩展名。

base循环

您可以使用lapply作为mapwalk的直接“替换”(实际上是反过来,purrr::maplapply的替换):

df <- lapply(sheet_names,  
             \(.x) readxl::read_excel(path, .x, col_types = "text", 
                                      col_names = FALSE))
lapply(names(df), 
       \(.y) sheet_names, write.csv(df[.y], paste0(file, "-", .y, ".csv"), 
                                    col_names = FALSE))

相关问题