Read Multiple Files & Append Data

การอ่านไฟล์ Excel ทีละหลายไฟล์ และนำข้อมูลมาต่อกัน ด้วย R

Written by Siwasit Jitpanu 9/12/2021

การสรุปข้อมูลโดย Excel หลายๆครั้ง เราจะต้องนำข้อมูลจากไฟล์ Excel รายวัน หรือรายเดือน มาต่อกันเพื่อนำไปใช้ เพื่อนๆเคยเจอปัญหาการเปิดไฟล์ Excel เพื่อนำมาต่อกัน แล้วค้าง หรือเปิดซ้ำ ทำให้ข้อมูลผิดกันบ้างหรือเปล่า

ในวันนี้ เราจะมาเสนอวิธีการอ่านข้อมูล Excel ทั้งหมดใน Folder และนำข้อมูลมาต่อกันด้วยโปรแกรม R ทำครั้งเดียวใช้ต่อไฟล์ Excel ได้ตลอด ขั้นตอนไม่ยุ่งยาก

ถ้าพร้อมแล้ว ลองทำตาม กันเลย

Package ที่ใช้ในบทความนี้ (dplyr และ openxlsx)

เพื่อนๆ สามารถเข้าไปดูวิธีติดตั้ง และเรียกใช้งาน Package ได้ที่นี่ คลิก

คำสั่งสำหรับติดตั้ง Package แบบตรวจสอบก่อนว่าติดตั้ง Package นั้นๆแล้วหรือยัง

if(!require('dplyr'))install.packages('dplyr')
if(!require('openxlsx'))install.packages('openxlsx',dependencies=TRUE)

(Tip : การใช้คำสั่งลักษณะนี้ทำให้สามารถนำ Code ที่เขียนไป Run เครื่องที่ยังไม่เคยติดตั้ง Package ได้เลย)

คำสั่งเรียกใช้งาน Package

library(dplyr)
library(openxlsx)

ข้อมูลที่ใช้กับบทความนี้

ข้อมูลตัวอย่างจากเว็บไซต์ eforexcel.com โดยใช้ข้อมูล 500,000 Rows มาแบ่งไฟล์และจัดให้อยู่ในรูปแบบที่ใช้ในตัวอย่าง จำนวน 25 ไฟล์

สามารถ Donwload ได้ที่ https://drive.google.com/drive/folders/1SmaqiVZLAyMrIb839_M274VoAdkaPVbc

และดูรายละเอียดของข้อมูลเพิ่มเติม ได้ที่ https://eforexcel.com/wp/downloads-18-sample-csv-files-data-sets-for-testing-sales

การใช้งานโปรแกรม R ในการต่อข้อมูลจากไฟล์ Excel ทั้งหมดใน Folder

คำสั่งที่ใช้ในการต่อข้อมูลจากไฟล์ Excel ทั้งหมดใน Folder

คำสั่งสำหรับการเก็บชื่อไฟล์

setwd("Folder")
fname <- gsub("XLSX","xlsx",list.files(getwd(),".xlsx|.XLSX"))

1. Folder คือ Directory ข้อ Folder ที่เราวางไฟล์ทั้งหมดไว้

2. fname คือ ชื่อไฟล์ Excel ทั้งหมดใน Folder

(Tip 1 : ".xlsx|.XLSX" คือการกรองเฉพาะไฟล์ นามสกุล .xlsx และ .XLSX โดยขั้นด้วยสัญลักษณ์ | เพื่อเก็บทั้ง 2 นามสกุล)

(Tip 2 : คำสั่ง gsub("XLSX","xlsx",...) ใช้เพื่อแปลงนามสกุล XLSX เป็น xlsx เพื่อแก้ไขัญหา R บาง Version ที่ใช้คำสั่ง read.xlsx ได้เฉพาะไฟล์นามสกุล xlsx)

คำสั่งสำหรับการอ่านไฟล์ทั้งหมด และนำมาต่อกัน

RawData <- read.xlsx(fname[1])
for(i in 2:length(fname))

{

RawData <- rbind(RawData,read.xlsx(fname[i]))

}

1. fname[1] คือ ไฟล์แรกสุด

2. RawData คือ ข้อมูลที่นำมาต่อแล้ว

3. i in 2:length(fname) คือ จำนวน fname ทั้งหมด

ตัวอย่างการใช้งานโปรแกรม R ในการต่อข้อมูลจากไฟล์ Excel ทั้งหมดใน Folder

ข้อมูลที่ใช้ใตัวอย่าง

ข้อมูลตัวอย่างจากเว็บไซต์ eforexcel.com

ไฟล์ Excel จำนวน 25 ไฟล์
ขนาดไฟล์รวม 53 MB
จำนวนของแถว 20,000 Rows และ 15 Columns ต่อไฟล์

จะเห็นว่าหากเราเปิดทีละไฟล์ แล้ว Copy มารวมกันจะทำได้ช้าเนื่องจากไฟล์มีขนาดใหญ่ และไฟล์ที่รวมจะใหญ่ขึ้นเรื่อยๆ เสี่ยงต่อการค้างได้

การจัดการ Folder ในการใช้คำสั่งต่อไฟล์

Folder Input เพื่อนำไฟล์ Excel ที่ต้องการต่อไปวาง
Folder Output เพื่อให้โปรแกรม R เขียนไฟล์ Excel ที่ต่อเสร็จแล้ว
Append.R คือ คำสั่งโปรแกรม R
Run(R-1.1.0).bat ไฟล์คำสั่งรัน Append.R

ในการจัดวางไฟล์ลักษณะนี้จะทำให้ทำงานได้ง่าย เมื่อต้องการนำ Excel อื่นๆมาต่อ เพียงแค่ลบไฟล์เก่าใน Input ใส่ไฟล์ใหม่แล้วรัน Run(R-1.1.0).bat

CODE คำสั่งที่ใช้

สามารถ Copy CODE คำสั่ง ด้านล่างได้เลย (กด Expand ทางด้านขวามือ)

#ล้างค่าตัวแปรทังหมด

rm(list = ls())

#คำสั่งสำหรับ ตรวจสอบเพื่อ Install Package

if (!require('dplyr')) install.packages('dplyr')

if (!require('openxlsx')) install.packages('openxlsx', dependencies = TRUE)

#คำสั่งเรียกใช้งาน Package

library(dplyr)

library(openxlsx)

#set Directory จากที่อยู่ของไฟล์ .R ให้เข้าไปใน Folder Input

setwd("Input")

#คำสั่งเพื่ออ่านชื่อไฟล์นามสกุล .XLSX และ .xlsx แล้วนำไปเก็บไว้ในตัวแปร

fname <- gsub("XLSX","xlsx",list.files(getwd(),".XLSX|.xlsx"))

#อ่านข้อมูลไฟล์แรกเพื่อเป็นข้อมูลตั้งต้น

RawData <- read.xlsx(fname[1],detectDates = TRUE)

#อ่านข้อมูลไฟล์ต่อไปเพื่อนำมาต่อกับข้อมูลชุดก่อนหน้าไปจนครบ

for(i in 2:length(fname))

{

RawData <- rbind(RawData,read.xlsx(fname[i],detectDates = TRUE))

}

#ลบตัวแปร i

rm(i)

#ในตัวอย่างนี้ข้อมูลทุกๆไฟล์จะมี Row ที่เป็น Total ซึ่งเราจะตัดออกโดยใช้คำสั่ง filter

RawData <- RawData %>% filter(!is.na(Region))

#ตั้งค่า Directory จากที่อยู่ใน Folder Input ให้ถอยออกมา 1 ขั้น

setwd("..")

#set Directory จากที่อยู่ของไฟล์ .R ให้เข้าไปใน Folder Output

setwd("Output")

#เขียนไฟล์ Excel จากข้อมูลที่ต่อแล้วลงใน Forder Output

write.xlsx(RawData,paste0("Final_",fname[length(fname)]),overwrite=TRUE)

#ตั้งค่า Directory จากที่อยู่ใน Folder Output ให้ถอยออกมา 1 ขั้น

setwd("..")

เพียงเท่านี้ เพื่อนๆ ก็จะสามารถต่อไฟล์ Excel ได้ง่ายไม่เสียเวลา(ในตัวอย่างใช้เวลาประมาณ 1 นาที) และสามารถนำข้อมูลไปใช้ต่อไม่ว่าจะทำใน Excel หรือ โปรแกรม R ต่อได้เลย

Page updated

Google Sites

Report abuse