ทำความสะอาดไฟล์ CSV ที่ยุ่งเหยิง: ปัญหาที่ควรตรวจตามลำดับ

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

ทำความสะอาดไฟล์ CSVไฟล์ CSV ยุ่ง มีช่องว่างและแถวซ้ำใช้ฟรี

อักขระที่ดูถูกต้องแต่ไม่ใช่

ช่องว่างที่เกินมา ช่องว่างที่ต้นหรือท้ายของเซลล์ หรือช่องว่างสองช่องติดกันกลางค่า ทำให้ “Berlin ” กับ “Berlin” กลายเป็นสองค่าที่ต่างกันสำหรับการค้นหา การกรอง หรือการ join

อักขระที่มองไม่เห็น อักขระบางตัวไม่กินพื้นที่บนหน้าจอ:

• ช่องว่างแบบไม่ตัดคำ (U+00A0) ดูเหมือนช่องว่างปกติ แต่เป็นอักขระคนละตัว เว็บไซต์ใช้เพื่อไม่ให้คำขาดออกจากกัน
• อักขระความกว้างเป็นศูนย์ (U+200B) ไม่มีความกว้างเลย
• เครื่องหมายลำดับไบต์ (byte order mark, U+FEFF) อาจอยู่ที่จุดเริ่มต้นของไฟล์ ก่อนหัวคอลัมน์แรก คอลัมน์จึงดูเหมือน “id” แต่จริง ๆ เป็น “id” ทำให้การค้นหา “id” ล้มเหลว

ลบอักขระที่มองไม่เห็น แสดงสิ่งที่พบก่อนจะเปลี่ยนแปลงอะไรทั้งสิ้น

ตัวอักษรเพี้ยน “é” ในตำแหน่งที่ควรเป็น “é” คือปัญหาการเข้ารหัสที่เรียกว่า mojibake ไฟล์ถูกบันทึกเป็น UTF-8 ซึ่ง é ใช้สองไบต์ แล้วถูกอ่านเป็น Windows-1252 ซึ่งสองไบต์นั้นกลายเป็นตัวอักษรสองตัวที่ไม่เกี่ยวข้องกัน เครื่องหมายอัญประกาศเดี่ยว “’” ก็เพี้ยนเป็น “’” ในลักษณะเดียวกัน วิธีแก้คือเปิดไฟล์ด้วย UTF-8 หรือส่งออกใหม่จากต้นทางโดยเลือก UTF-8 การแก้ข้อความที่บันทึกผิดไปแล้วทำได้ยาก จึงควรเก็บต้นฉบับไว้และทำงานบนสำเนา

เค้าโครง: แถวหัวเรื่อง บรรทัดว่าง และข้อมูลซ้ำ

รายงานมักขึ้นต้นด้วยชื่อเรื่อง วันที่ และบรรทัดว่างก่อนถึงแถวหัวคอลัมน์จริง ลบทุกอย่างที่อยู่เหนือแถวหัวคอลัมน์ ให้บรรทัดแรกเป็นชื่อคอลัมน์ ทุกคอลัมน์ต้องมีชื่อหนึ่งชื่อ และไม่มีชื่อใดว่างเปล่า

แต่ละแถวควรมีจำนวนฟิลด์เท่ากัน จุลภาคที่หลงเข้าไปในค่าที่ไม่ได้ครอบด้วยเครื่องหมายคำพูดจะเพิ่มหนึ่งฟิลด์ และทุกค่าหลังจากนั้นจะขยับไปคอลัมน์ถัดไปหนึ่งช่อง นับจำนวนฟิลด์ในแต่ละแถว แถวที่จำนวนไม่ตรงคือแถวที่เสียหาย

ข้อมูลซ้ำมีสองแบบ แถวที่เหมือนกันทุกคอลัมน์มักเกิดจากการส่งออกหรือวางข้อมูลซ้ำสองครั้ง ลบได้อย่างปลอดภัย ส่วนแถวที่มีคีย์เดียวกันแต่ต่างกันในส่วนอื่นอาจเป็นข้อมูลที่อัปเดตแล้ว การลบแถวผิดอาจทำให้เวอร์ชันที่ใหม่ที่สุดหายไป จึงควรตรวจก่อน

วันที่: 03/04/2026 อาจหมายถึงสองวันที่ต่างกัน

03/04/2026 คือวันที่ 3 เมษายนในประเทศหนึ่ง และวันที่ 4 มีนาคมในอีกประเทศหนึ่ง โดยข้อความไม่ได้บอกว่าเป็นแบบใด ให้ดูวันที่อื่นในคอลัมน์เดียวกัน ถ้ามีเลขตัวแรกมากกว่า 12 คอลัมน์นั้นเรียงวันก่อนเดือน ถ้ามีเลขตัวที่สองมากกว่า 12 คอลัมน์นั้นเรียงเดือนก่อนวัน ถ้าทุกค่ามีตัวเลขทั้งสองตัวไม่เกิน 12 ไฟล์ก็บอกไม่ได้ ให้ถามระบบต้นทาง ตรวจชนิดข้อมูล จะทำเครื่องหมายคอลัมน์แบบนี้ว่าคลุมเครือ แทนที่จะเดา

เมื่อรู้ลำดับแล้ว ให้แปลงทุกวันที่เป็น ISO 8601 ในรูปแบบปี-เดือน-วัน เช่น 2026-04-03 รูปแบบนี้ไม่คลุมเครือ และเรียงลำดับได้ถูกต้องเมื่อเป็นข้อความ แปลงทีละรูปแบบหากคอลัมน์มีหลายแบบปนกัน เพราะโปรแกรมส่วนใหญ่จะอ่านคอลัมน์ที่มีสองรูปแบบอย่างไม่สม่ำเสมอ

ตัวเลขที่เก็บเป็นข้อความ

ตัวเลขจะถูกเก็บเป็นข้อความเมื่อมีสัญลักษณ์สกุลเงิน เครื่องหมายเปอร์เซ็นต์ หรือช่องว่างอยู่ข้างใน เช่น “€ 12” “45%” หรือ “1 234” ที่มีช่องว่างแบบไม่ตัดคำ เครื่องหมายคั่นหลักพัน เช่น “1,234.50” มักทำให้โปรแกรมอ่านค่านั้นเป็นข้อความด้วยเช่นกัน สเปรดชีตจะแสดงตัวเลขที่เป็นข้อความชิดซ้าย และการหาผลรวมจะข้ามค่าเหล่านั้นไป

จุลภาคทศนิยมเป็นกับดัก ใน “1.234,50” จุลภาคคือจุดทศนิยม และจุดคือตัวคั่นหลักพัน ส่วน “1,234.50” กลับกัน ดังนั้น “1,234” อาจหมายถึง 1234 ในไฟล์หนึ่ง และ 1.234 ในอีกไฟล์หนึ่ง ให้หาโลเคล (locale) ของต้นทางก่อนแปลง แล้วจึงตัดเครื่องหมายคั่นหลักพันออกและเปลี่ยนจุลภาคทศนิยมเป็นจุด

เลข 0 นำหน้าหายไป

รหัสไปรษณีย์ รหัสสินค้า และเลขบัญชีมักขึ้นต้นด้วยเลข 0 เมื่อไฟล์ CSV เปิดในสเปรดชีต “01234” จะถูกอ่านเป็นตัวเลข 1234 และเลข 0 หายไป การบันทึกไฟล์จะคงการสูญหายนี้ไว้ ระบบต้นทางจึงเป็นสำเนาเดียวที่เชื่อถือได้

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

ตรวจชนิดข้อมูลก่อน แล้วจึงวิเคราะห์

หลังทำความสะอาด ให้ตรวจชนิดข้อมูลของแต่ละคอลัมน์ “N/A” เพียงค่าเดียวในคอลัมน์ราคาจะทำให้ทั้งคอลัมน์กลายเป็นข้อความ และผลรวมจะผิด ตรวจชนิดข้อมูล ให้แต่ละคอลัมน์มีชนิดและคะแนนความมั่นใจ และแสดงเซลล์ที่ไม่ตรงกับชนิดนั้น

จากนั้นจึงวิเคราะห์คอลัมน์ ตัววิเคราะห์คอลัมน์ CSV แสดงช่องว่างและค่าที่ผิดปกติในแต่ละคอลัมน์ ค่าที่ผิดปกติอาจเป็นค่าจริง หรือความผิดพลาด เช่น ราคาที่มากกว่าค่าอื่นหนึ่งพันเท่า ซึ่งมักเกิดจากจุดทศนิยมหรือตัวคั่นที่วางผิดที่

กรณีพิเศษ

• การขึ้นบรรทัดใหม่ภายในเครื่องหมายคำพูด ค่าที่อยู่ในเครื่องหมายคำพูดอาจมีการขึ้นบรรทัดใหม่ เครื่องมือที่แบ่งไฟล์ตามบรรทัดจะตัดแถวนั้นออกเป็นสองส่วน ให้ใช้โปรแกรมที่เข้าใจฟิลด์ที่อยู่ในเครื่องหมายคำพูด
• ไฟล์ที่ใช้เครื่องหมายอัฒภาค สเปรดชีตยุโรปหลายแห่งบันทึก CSV โดยใช้อัฒภาค เพราะในโลเคลของพวกเขาจุลภาคคือตัวคั่นทศนิยม ตรวจตัวคั่นก่อนอ่านคอลัมน์
• เก็บต้นฉบับไว้ เขียนผลลัพธ์ที่ทำความสะอาดแล้วลงในไฟล์ใหม่ และเก็บไฟล์ที่ส่งออกมาไว้ในสภาพเดิม

ลำดับขั้นตอนแบบเช็กลิสต์

1. เก็บสำเนาของไฟล์ที่ส่งออกมาไว้เป็นต้นฉบับ
2. ลบช่องว่างที่เกินมาและอักขระที่มองไม่เห็น แก้ตัวอักษรที่เพี้ยนที่ต้นทางหากทำได้
3. ลบแถวหัวเรื่อง ตรวจชื่อคอลัมน์ และยืนยันว่าทุกแถวมีจำนวนฟิลด์เท่ากัน
4. หาข้อมูลซ้ำ และตัดสินใจว่าจะเก็บเวอร์ชันใด
5. แปลงวันที่เป็นปี-เดือน-วัน ลบตัวคั่น และทำให้ตัวระบุเป็นข้อความ
6. ตรวจชนิดข้อมูลของแต่ละคอลัมน์ แล้ววิเคราะห์เพื่อหาช่องว่างและค่าที่ผิดปกติ

คำตอบสั้น ๆ

ทำไมการค้นหาล้มเหลวทั้งที่ค่าดูเหมือนกันทุกประการ

มักมีช่องว่าง ช่องว่างแบบไม่ตัดคำ หรือ BOM อยู่ในเซลล์ใดเซลล์หนึ่ง ลบอักขระที่มองไม่เห็นแล้วลองใหม่

ทำไมวันที่ของฉันเปลี่ยนเป็นค่าอื่น

โปรแกรมอาจอ่านวันที่แบบวันก่อนหรือเดือนก่อนผิดลำดับ หรือแปลงข้อความเป็นรูปแบบวันที่ของตัวเอง ให้แปลงวันที่เองเป็นปี-เดือน-วันก่อน