xls转换成csv

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
# 加载必要的库
library(readxl)
library(tools)

# 设置输入和输出文件夹路径
input_folder <- "C:/Users/Administrator/Desktop/raw/raw"
output_folder <- "C:/Users/Administrator/Desktop/raw/out_csv"

# 创建输出文件夹（如果不存在）
if (!dir.exists(output_folder)) {
  dir.create(output_folder)
}

# 获取输入文件夹中的所有文件
files <- list.files(input_folder, full.names = TRUE)

# 遍历每个文件
for (file in files) {
  file_name <- basename(file)
  file_ext <- tools::file_ext(file_name)
  
  base_name <- file_path_sans_ext(file_name)
  output_file <- file.path(output_folder, paste0(base_name, "_out_csv.csv"))
  
  if (file_ext == "xlsx") {
    # 读取Excel文件
    df <- read_excel(file)
    
    # 写入CSV文件
    write.csv(df, output_file, row.names = FALSE, na = "")
    
  } else if (file_ext == "csv") {
    # 复制CSV文件并重命名
    file.copy(file, output_file, overwrite = TRUE)
  }
}

print("转换和复制完成！")


转换

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
print(getwd())
library(tidyr)
library(dplyr)
library(tools)

input_dir <- "C:\\Users\\Administrator\\Desktop\\raw\\out_csv"
output_dir <- "C:\\Users\\Administrator\\Desktop\\raw\\out_chang"

files <- list.files(input_dir, pattern = "\\.csv$", full.names = TRUE)

if (!dir.exists(output_dir)) {
  dir.create(output_dir)
}

for (file in files) {
  base_filename <- file_path_sans_ext(basename(file))
  
  print(paste("Processing file:", base_filename))  # Debug: Print filename being processed
  
  data <- read.csv(file, header = TRUE)
  
  long_data <- data %>%
    pivot_longer(
      # 因为csv的数字是以X开头 所以用starts_with
      cols = starts_with("X"),
      names_to = "year",
      names_prefix = "X",
      values_to = base_filename
    )
  
  output_file <- file.path(output_dir, paste0(base_filename, "_out.csv"))
  
  print(paste("Writing output to:", output_file))  # Debug: Print output file path
  
  write.csv(long_data, output_file, row.names = FALSE)
  
  print(paste("Finished processing:", base_filename))  # Debug: Print completion message
}