Export ไฟล์จาก Access ไป Excel แล้ววันที่เป็น #####

Export ไฟล์จาก Access ไป Excel แล้ววันที่เป็น ##### แก้ไขอะไรไม่ได้ เกิดขึ้นจากอะไร? และจะแก้ไขได้อย่างไร?

มีคนถามปัญหามาว่า ได้ Export ไฟล์จาก Access ไป Excel แล้ววันที่กลายเป็น ##### ซึ่งเมื่อได้รู้ว่าข้อผิดพลาดคือเครื่องหมาย # ชาร์ป ตอนแรกคิดว่าเป็นเพราะผู้สอบถาม ตั้งค่าความกว้างคอลัมน์น้อยเกินไป (บางท่านคงคิดว่านี่เป็นการสมมติฐานที่ปัญญาอ่อนไปหน่อย…แต่เชื่อเถอะ บ่อยครั้งที่ข้าพเจ้าเจอคำถามที่ไม่คิดว่าจะเจอ และได้พบหนทางการแก้ปัญหาแบบหญ้าปากคอกจนแทบจะหัวเราะเสียสติ)

แต่ในกรณีนี้ ไม่ได้เกิดจากกรณีนั้น ขอบคุณพระเจ้า! (และถ้าเป็นแบบนั้นข้าพเจ้าคงไม่เอามาเขียนบล็อกหรอกเนอะ 🙂

Export ไฟล์จาก Access ไป Excel แล้ววันที่เป็น #####

พูดุคยกับผู้สอบถามเบื้องต้น ได้ความว่าลองปรับ Format Cell ใน Excel ให้เป็นวันที่แล้ว แต่ก็ไม่สำเร็จ

ตอนแรกข้าพเจ้าคิดว่าฐานข้อมูลจาก Access อาจจะมีปัญหาอะไรหรือเปล่า? จึงขอให้ผู้สอบถามลอง Compact and Repair Database ใน Access เผื่อข้อมูลเสียหายจะได้จัดระเบียบใหม่

วิธีการง่าย ๆ คือ ไปที่ Ribbon Database Tools แล้วคลิก compact and repair a database (ในขณะที่เขียนนี้ ข้าพเจ้าใช้ Access ในเวอร์ชัน Microsoft 365 อยู่นะ

หรือดูอีกที่ จะอยู่ที่ File > Info > Compact & Repair Database

หรือ จะไปตั้งค่าให้มัน Compact Database ในตอนที่ปิดไฟล์ ก็ไปที่ File > Options > Current Database ติ๊กเลือก Compact on Close แบบนี้ก็ได้

TIPS: Database ขนาดใหญ่ที่ทำใน Access อาจจะเสียหายได้จากหลายสาเหตุ การใช้ Compact and Repair Database เป็นหนึ่งในการแก้ไขเบื้องต้นง่าย ๆ ที่มักจะประสบความสำเร็จพอสมควร

TIPS: การ compact ไม่ได้ปรับปรุงหรือย่อขนาด data แต่จะกำจัดช่องว่างที่ไม่ได้ใช้งาน เวลาที่เราใช้ Queary หรือทำอะไรกับฐานข้อมูลมาก ๆ เวลาทำงานกับฐานข้อมูลขนาดใหญ่ ข้าพเจ้ามักจะแนะนำให้ Split Data เพื่อลดความเสียหาย และออกแบบฐานข้อมูลไม่ให้ผู้ใช้เข้าถึง Data โดยตรง

หลังจากให้ผู้สอบถามลอง Compact and Repair Database เรียบร้อยแล้ว พอส่งไฟล์ข้อมูลอีกครั้ง ก็ยังได้ผลลัพธ์เหมือนเดิม ก็เริ่มฉุกคิดอะไรบางอย่างขึ้นมาได้

จึงให้ผู้สอบถามลอง Format Cell ใน Excel เป็นตัวเลข ผลออกมาเป็นตัวเลขติดลบ

เป็นอย่างที่คิดจริง ๆ มิน่า มันจึงแสดงผลออกมาเป็น ######

ดูท่าจะมีปัญหาตรงวันที่จาก Access เก็บผิดรูปแบบหรือใส่ค่าผิดพลาด จึงส่งผลให้การแสดงวันที่ใน Excel ผิดพลาดตามไปด้วย

มาทำความเข้าใจเรื่องการแสดงวันที่ใน Excel กันดีกว่า

Excel เก็บข้อมูลวันที่อย่างไร?

เวลาที่เราเห็น วัน-เดือน-ปี ใน Excel เป็นแค่การแสดงผลตาม Format ของตัวเลขใน Excel นั่นหมายความว่า ความจริง Excel ไม่ได้เก็บ “วันที่” ในรูปแบบ “วันที่” แต่เก็บ “วันที่” ในรูปแบบ “ตัวเลข” ต่างหาก

กล่าวคือ สมมติ ข้าพเจ้าพิมพ์วันที่ 6/11/2020 ลงใน Excel นั้น Excel ไม่ได้เก็บค่า 6/11/2020 ความจริงมันเก็บค่า 44141 ซึ่งตรวจสอบง่าย ๆ ด้วยการเปลี่ยน Format ให้กลายเป็น Number (หรือ General) ก็จะได้เห็นค่านี้ออกมา

ยังไง มันยังไง

ในระบบของ excelจัดเก็บ วัน-เดือน-ปี ในรูปแบบของลำดับวัน คือ วันที่ 1 มกราคม ค.ศ. 1900 จะเก็บเป็น 1

และวันถัดไป ก็จะบวกเพิ่มไปทีละวัน วันที่ 2 มกราคม ค.ศ. 1900 ก็จะเก็บเป็น 2 เรียงลำดับไปเรื่อย ๆ วันที่ 6 พฤศจิกายน ค.ศ. 2020 ก็จะเป็นวันที่ 44141 เพียงแต่ด้วยความที่เป็นโปรแกรมสำเร็จรูป มันจึงมีความชาญฉลาดเพียงพอจะรับข้อมูลที่มีรูปแบบเป็น “วัน-เดือน-ปี” แล้วแปรเป็นข้อมูลเก็บไว้โดยที่ผู้ใช้งานหลายคนไม่ทันสำเหนียกว่าเรากำลังบันทึกข้อมูลเป็น “ตัวเลข” ไม่ใช่ “วัน-เดือน-ปี” อ่านรายละเอียดที่เว็บไซต์ไมโครซอฟต์นะจ๊ะ

นี่คือเหตุผลที่ Export ไฟล์จาก Access ไป Excel แล้ววันที่เป็น #####

โปรดสังเกตอีกอย่างนะจ๊ะ กำลังเน้นว่าเป็น ค.ศ. เพราะนี่บอกกับทุกคนที่ร่วมงานด้วย (แต่ไม่ค่อยได้รับความร่วมมือสักเท่าไหร่) ว่า

“การทำงานกับ Excel หรือ Access ให้งดเว้นความเป็นชาตินิยมไปก่อน เก็บวันที่ในรูปแบบปีคริสต์ศักราชเท่านั้น ถ้าเป็นไปได้อย่าตั้งชื่อ sheet หรือ workbook หรือ table หรือ ฯลฯ เป็นภาษาไทย ขอให้ใช้อักขระโรมัน A B C D ไปเหอะ ขอร้อง กราบงาม ๆ”

ต้นตอความผิดพลาด ที่ Export ไฟล์จาก Access ไป Excel แล้ววันที่เป็น #####

กลับมาที่ปัญหาเรื่องวันที่ข้างต้น เมื่อลองให้ปรับ Format Cell ใน Excel เป็นตัวเลขแล้วผลออกมาเป็นตัวเลขติดลบ ก็หมายความว่าข้อมูลวันที่ใน Access เป็นข้อมูลวันที่ก่อนปีค.ศ. 1900 (เพราะตัวเลขติดลบ) จึงไม่น่าแปลกใจที่พอปรับ format cell เป็น date/time ก็จะเป็น ######## อย่างที่เห็น เพราะ Excel ไม่สามารถแสดงวันที่ก่อนวันที่ 1 มกราคม ค.ศ. 1900 ได้

จึงให้ผู้สอบถามย้อนกลับไปดู Data Type ใน Access พบว่าข้อมูลเก็บในรูปแบบ Date/Time อยู่แล้ว น่าจะถูกต้อง จึงให้ดูตรง format ประกอบ ก็เห็นชัดเจนว่า มันเป็นพุทธศักราช

ตรงนี้แหละที่ผิด

เพราะข้อมูลที่เก็บไว้ในฟิลด์นั้น เป็นตัวเลขของ คริสต์ศักราช เช่น 2016 2017 2018  แต่แทนที่ Access จะเข้าใจว่านี่คือ คริสต์ศักราช มันดันเข้าใจว่านี่คือปี พุทธศักราช

นี่แหละ ปัญหาที่เกิด พอมันคิดว่าเป็น ปีพุทธศักราช 2016 มันก็แปลงกลับไปเป็น คริสต์ศักราชให้ด้วยความฉลาดของมัน ทำให้มันเก็บค่าจริง ๆ เป็นปีคริสต์ศักราช 1473

เมื่อค่าที่เก็บเป็นปีคริสต์ศักราชก่อน 1900 นี่คือเหตุผลที่ Export ไฟล์จาก Access ไป Excel แล้ววันที่เป็น ##### ด้วยเหตุนี้เอง

ทำไมจึงพลาดแบบนี้?

เดาว่า ข้อมูลต้นทางจริง ๆ น่าจะเป็น Text File เมื่อนำเข้ามาเป็น Access นั้น คอมพิวเตอร์เครื่องที่ใช้ทำ Database ได้ตั้งค่าวันที่ในเครื่อง เป็นพุทธศักราช (ตั้ง Region เป็นไทย) ตัวซอฟต์แวร์ Access จะตั้งค่ารูปแบบ วัน-เดือน-ปี ตามระบบคอมพิวเตอร์โดยปริยาย แล้วนำไปประมวลผลเป็นคริสต์ศักราชเอง

นั่นหมายถึงว่า ถ้าต้นทาง (อาจจะเป็น text file) ใส่ค่าเป็นปีค.ศ.มาอยู่แล้ว ผู้นำเข้าได้นำเข้าสู่ Access โดยไม่ได้ตระหนักความจริงข้อนี้ หรือไม่ได้ดูรายละเอียดข้อมูลให้ชัดเจนก่อน รวมถึงไม่ได้ตรวจสอบรายละเอียดของ Data Type ที่ว่าเครื่องที่ใช้งาน Access ขณะนั้นตั้งค่าระบบเป็นพุทธศักราช ทำให้เกิดความผิดพลาดนี้ขึ้น

เรื่องนี้จะโทษคนนำเข้าก็ไม่ได้ ต้องยอมรับว่า ถ้าเป็นบุคคลทั่วไป ที่ไม่ค่อยได้จับงานด้าน Database มักจะไม่ทันสังเกตเรื่องนี้ และถ้ามองผ่าน ๆ ใน Access จะไม่เห็นปัญหา เพราะเก็บข้อมูลเป็น 1 มกราคม 2016 ใครเห็นก็คิดว่าเก็บเป็นคริสต์ศักราช ไม่เห็นความผิดปกติ แต่เมื่อ Export ข้อมูลมายัง Excel จะเป็นการส่งข้อมูลในรูปแบบที่เก็บ ไม่ใช่รูปแบบที่แสดงผล ดังนั้น Access ไม่ได้ส่งข้อมูลเป็น 1 มกราคม 2016 แต่ส่งออกมาเป็น 1 มกราคม 1473 ซึ่งเป็นค่าที่เก็บจริง

เมื่อ Excel ได้รับข้อมูลว่าเป็น 1 มกราคม 1473 ซึ่งเป็นปีก่อนหน้าปีค.ศ. 1900 ข้อมูลวันที่จึงติดลบ และเมื่อแปลง Format เป็น date/time จึงกลายเป็น ######## อย่างที่เห็น

วิธีแก้ไขปัญหา

แก้ปัญหาที่ Access ซึ่งเป็นต้นทาง น่าจะดีที่สุด เพราะไปแก้ปลายทาง เกิดในอนาคตต้องเอาต้นทางมาใช้ก็มีปัญหาอีก

วิธีแรก แก้ Type ของฟิลด์จาก Date/Time ให้เป็น Text ไปเลย วิธีนี้ไม่แนะนำหากต้องนำวันไปช่วยคำนวณระยะเวลา

วิธีที่สอง เมื่อรู้ว่าเหตุอยู่ที่ปีค.ศ. โดนเก็บไว้ในรูปแบบปี พ.ศ. ก็บวก 543 เข้าไป โดยจะใช้ Query แล้ว สร้างช่องใหม่ ใช่ฟังก์ชัน DateAdd() ซึ่งในที่นี้จะเป็น

DateAdd(“yyyy”, 543, [Reg_date])

คำสั่งนี้หมายถึงให้เพิ่ม “ปี” (คือ “yyyy”) เข้าไปอีก 543 ปี โดนกำหนดฟิลด์ต้นทางคือ Reg_Date ซึ่งเป็นฟิลด์ที่เก็บวันที่เอาไว้

ทีนี้จะทำเป็น Field ใหม่ หรือ จะ Update Filed เดิมก็แล้วแต่นะ แต่ในตัวอย่างที่ทำให้ดูนี่ สร้าง Field ใหม่ ไม่ได้อัปเดต Fireld เดิม

วิธีแก้ที่คิดออกก็มีสองวิธีนี้ แต่คงมีวิธีอื่นนอกจากนี้อีกแหละ ไว้ค่อยเขียนถึงอีกที