บทความ
ทำไม Excel ถึงทำลายไฟล์ CSV ของคุณ
หมายเลขผู้ป่วยที่ขึ้นต้นด้วยเลขศูนย์สามตัว ยีนชื่อ SEPT2 หมายเลขบัญชีสิบหกหลัก เปิดสิ่งเหล่านี้ในสเปรดชีตเป็นไฟล์ CSV ธรรมดา แล้วตัวนำเข้าของ Excel ไม่ได้แค่แสดงผลมัน มันเขียนใหม่: เลขศูนย์หายไป ชื่อยีนกลายเป็นวันที่ และหมายเลขบัญชีเสียหลักท้ายไปสองสามหลัก ทั้งหมดนี้ไม่ใช่บั๊กจริง ๆ แต่เป็นสิ่งที่เกิดขึ้นเมื่อตัวเดาตัวเลขและวันที่แบบทั่วไปเจอกับข้อความที่แค่ดูเหมือนตัวเลขหรือวันที่ นี่คือสิ่งที่เกิดขึ้นจริงกับไฟล์ CSV ทันทีที่ Excel เปิดมัน และวิธีหยุดมันก่อนที่จะเกิดขึ้น
เลขศูนย์นำหน้าหายไป
พิมพ์ 02134 ลงในเซลล์ ประเภทคอลัมน์เริ่มต้นของ Excel คือ General จะอ่านมันเป็นตัวเลขและตัดเลขศูนย์นำหน้าออก: 2134 สิ่งนี้กระทบกับรหัสใด ๆ ที่เก็บเป็นตัวเลขแต่จริง ๆ ไม่ใช่ปริมาณ เช่นรหัสไปรษณีย์สหรัฐ รหัสไปรษณีย์ฝรั่งเศส หมายเลขพนักงานหรือใบแจ้งหนี้ หมายเลขโทรศัพท์ ในทางเทคนิคยังไม่มีอะไรหายไปจากไฟล์ CSV ต้นฉบับ ข้อความข้างเซลล์ยังคงเดิมจนกว่าคุณจะบันทึกไฟล์งาน ซึ่งตอนนั้น Excel จะเขียนกลับสิ่งที่มันแสดงผล และเลขศูนย์จะหายไปถาวร วิธีแก้คือบอก Excel ว่าคอลัมน์นั้นเป็นข้อความก่อนที่มันจะมีโอกาสเดา: ผ่าน Data > From Text/CSV (หรือ Get Data > From File) คุณสามารถตั้งประเภทข้อมูลของแต่ละคอลัมน์ได้อย่างชัดเจน หรือเพิ่มเครื่องหมายอะพอสทรอฟีก่อนค่าในเซลล์เดียวเพื่อบังคับให้เป็นข้อความ
ข้อความกลายเป็นวันที่โดยไม่ถาม
ตัวเดาเดียวกันนี้ยังมองหาข้อความที่มีรูปแบบคล้ายวันที่ด้วย เซลล์ที่มีคำว่า MAR1 หรือ SEPT2 จะถูกอ่านเป็น 1 มีนาคม หรือ 2 กันยายน แล้วถูกแปลงเป็นค่าวันที่และจัดรูปแบบใหม่โดยไม่มีคำเตือนใด ๆ นี่ไม่ใช่เรื่องสมมติ: ในปี 2020 คณะกรรมการตั้งชื่อยีนมนุษย์ HUGO ได้เปลี่ยนชื่อสัญลักษณ์ยีนมนุษย์ราว 27 ตัว รวมถึง SEPT2 และ MARCH1 โดยเฉพาะเพราะซอฟต์แวร์สเปรดชีตยังคงเปลี่ยนมันเป็นวันที่ทุกครั้งที่เปิดชุดข้อมูล ปัญหาร้ายแรงพอที่การสำรวจงานวิจัยด้าน genomics ในปี 2016 พบข้อผิดพลาดจากการแก้ไขอัตโนมัติแบบนี้ในราวหนึ่งในห้าของสิ่งพิมพ์ที่มีไฟล์ Excel เสริมแนบมา บทเรียนนี้ขยายไปไกลกว่าชื่อยีน: รหัสตัวอักษรและตัวเลขสั้น ๆ ใดก็ตามที่บังเอิญดูเหมือนวันและเดือนมีความเสี่ยงทันทีที่ใครก็ตามเปิดไฟล์ CSV ของคุณในสเปรดชีต ไม่ใช่แค่ของคุณเท่านั้น
ตัวเลขยาวมากเสียหลักท้าย
Excel เก็บทุกตัวเลขเป็นค่าทศนิยมแบบ floating-point ด้วยความแม่นยำ 15 หลักที่มีนัยสำคัญ ซึ่งเป็นข้อจำกัดที่ Microsoft ระบุไว้โดยตรง หมายเลขบัตรหรือบัญชีสิบหกหลักเช่น 4111111111111111 จะถูกปัดเศษให้พอดี ดังนั้นหลักสุดท้ายหนึ่งหรือสองหลักจะกลายเป็นศูนย์โดยไม่รู้ตัว และค่านั้นก็ไม่ใช่ค่าที่คุณพิมพ์อีกต่อไป การจัดรูปแบบเซลล์ทีหลังจะไม่นำหลักที่หายไปกลับมา เพราะตัวเลขที่เก็บไว้ข้างในถูกปัดเศษไปแล้ว วิธีแก้เดียวคือนำเข้าคอลัมน์เป็นข้อความ หรือใส่เครื่องหมายอะพอสทรอฟีนำหน้าค่า ก่อนที่ Excel จะปฏิบัติกับมันเป็นตัวเลข
ข้อความที่มีเครื่องหมายกำกับเสียงกลายเป็นตัวอักษรขยะ
ปัญหาการเข้ารหัสอักขระเป็นรูปแบบความล้มเหลวที่แยกจากการแก้ไขอัตโนมัติ ไฟล์ CSV ที่ไม่มี byte-order mark คือไบต์ธรรมดา หากมันถูกบันทึกเป็น UTF-8 Excel บน Windows มักจะเปิดโดยสันนิษฐานว่าเป็น codepage ของระบบเองแทน ดังนั้นตัวอักษรที่มีเครื่องหมายกำกับเสียง สัญลักษณ์สกุลเงิน และอีโมจิจะออกมาเป็น mojibake ข้อความอย่าง café จะกลายเป็นอะไรที่ใกล้เคียงกับ café วิธีที่ปลอดภัยเมื่อสร้าง CSV สำหรับ Excel คือบันทึกเป็น CSV UTF-8 หรือเพิ่ม UTF-8 byte-order mark วิธีที่ปลอดภัยเมื่อนำเข้าไฟล์คือนำเข้าผ่าน Data > From Text/CSV ซึ่งให้คุณเลือกการเข้ารหัสต้นทางได้อย่างชัดเจนแทนที่จะเชื่อให้การดับเบิลคลิกเดาถูก
ย้ายข้อมูลระหว่างฟอร์แมตโดยไม่ต้องเปิดใน Excel
ทั้งหมดนี้ไม่ใช่บั๊กของ Excel จริง ๆ มันคือสิ่งที่ตัวเดาตัวเลขและวันที่ของสเปรดชีตถูกสร้างมาให้ทำ และมันเกิดขึ้นไม่ว่าไฟล์จะถูกเปิดอย่างไร ไม่ใช่แค่ใน Excel เท่านั้น วิธีที่ปลอดภัยที่สุดในการปรับรูปแบบไฟล์ CSV หรือตรวจสอบว่าคอลัมน์หนึ่ง ๆ ยังคงเป็นข้อความจริง ๆ ก่อนถึงสเปรดชีตใด ๆ คือทำงานกับมันโดยตรง: ตัวแปลงข้อมูลของเราแปลง CSV ด้วยตัวแปลงที่รองรับเครื่องหมายคำพูดมาตรฐานและไม่เคยตีความประเภทของค่าใหม่ ดังนั้นรหัสไปรษณีย์หรือชื่อยีนจึงยังคงเป็นข้อความเดียวกับที่คุณให้ไว้ทุกประการ มันทำงานทั้งหมดในเบราว์เซอร์ของคุณ ไม่มีสิ่งที่คุณวางถูกอัปโหลดไปที่ไหนเลย แต่มันปกป้องเฉพาะขั้นตอนการแปลงเท่านั้น: หากผลลัพธ์ยังจะถูกเปิดในสเปรดชีตทีหลัง ต้องทำเครื่องหมายคอลัมน์ที่ละเอียดอ่อนเป็นข้อความที่นั่นด้วยเช่นกัน
เครื่องมือที่กล่าวถึงในบทความนี้
คำถามที่พบบ่อย
จะหยุด Excel ไม่ให้ลบเลขศูนย์นำหน้าออกจาก CSV ได้อย่างไร
อย่าเปิดไฟล์ด้วยการดับเบิลคลิก ให้นำเข้าผ่าน Data > From Text/CSV (หรือ Get Data) แทน ซึ่งจะแสดงตัวเลือกประเภทข้อมูลทีละคอลัมน์ ตั้งคอลัมน์รหัสไปรษณีย์ ID หรือหมายเลขโทรศัพท์เป็น Text ก่อนจบการนำเข้า วิธีแก้ไขด่วนต่อเซลล์คือพิมพ์เครื่องหมายอะพอสทรอฟีก่อนค่า เช่น '02134 ซึ่งบังคับให้เป็นข้อความโดยไม่เปลี่ยนสิ่งที่แสดงผล
ทำไม Excel ถึงเปลี่ยน ID หรือรหัสของฉันเป็นวันที่
ประเภทคอลัมน์ General ของ Excel มองหาข้อความที่มีรูปแบบคล้ายวันที่อย่างจริงจัง เช่นคำสั้น ๆ บวกตัวเลข หรือตัวเลขสองตัวคั่นด้วยขีดหรือทับ แล้วแปลงทุกอย่างที่ตรงกับรูปแบบนั้นโดยไม่ถามยืนยัน นี่คือสิ่งที่บังคับให้ต้องเปลี่ยนชื่อสัญลักษณ์ยีนอย่าง SEPT2 และ MARCH1 ในปี 2020 นำเข้าคอลัมน์เป็น Text เหมือนกับกรณีเลขศูนย์นำหน้า เพื่อหยุดการเดานี้ก่อนที่จะเกิดขึ้น
การแปลง CSV ด้วยวิธีอื่นช่วยหลีกเลี่ยงปัญหาเหล่านี้ได้ทั้งหมดหรือไม่
มันหลีกเลี่ยงได้เฉพาะขั้นตอนการแปลงนั้น ไม่ใช่ตลอดไป ตัวแปลงที่ปฏิบัติกับทุกฟิลด์เป็นข้อความธรรมดา อย่างตัวแปลงข้อมูลของเรา จะไม่เขียนรหัสไปรษณีย์หรือชื่อยีนใหม่ขณะแปลง CSV เป็น JSON แต่หากผลลัพธ์ยังถูกเปิดในสเปรดชีตทีหลัง ไม่ว่าโดยคุณหรือผู้รับ ตัวนำเข้าของโปรแกรมนั้นก็มีโอกาสอีกครั้งที่จะตีความค่าใหม่ ดังนั้นคอลัมน์ที่ละเอียดอ่อนจึงต้องถูกทำเครื่องหมายเป็นข้อความที่นั่นด้วยเช่นกัน