Why Excel does NOT support RFC 4180 standard for CSV?

เมื่อวานนี้มีผู้ใช้แจ้งปัญหาว่าข้อมูลจาก Spreadsheet โหลดเข้าโปรแกรมแล้วรหัสไม่ถูกต้อง สาเหตุเกิดจากโปรแกรมที่เพิ่งอัพเดทใหม่ มีการปรับเปลี่ยนรายละเอียดบางอย่างเมื่อส่งข้อมูลออก (export) ไปที่ชีท จนส่งผลกระทบตอนโหลดข้อมูลนั้นกลับเข้ามา (import) กลายเป็นรหัสไม่ถูกต้อง
 
ผมจั่วหัวเรื่องด้วยคำถามจาก Microsoft Community เพราะเป็นปัญหาที่ปวดหัวโดยเฉพาะผู้ดูแลดาต้าเบส ด้วยความสับสนว่ามันเป็นข้อมูล (data interchange format) ให้คอมพิวเตอร์อ่าน หรือรายงาน (data representation) ให้มนุษย์อ่าน

ปัญหาเรื่องปรับรูปแบบข้อมูลที่ใช้กับชีททั้ง Excel และ Sheets สำหรับ export และ import เป็นเรื่องที่ดูเหมือนเล็กน้อย จนผมก็ไม่ทันได้ใส่ใจรายละเอียด มักจะเป็นการปรับแก้ตามที่ผู้ใช้ร้องขอมา โดยไม่ทันคิดว่าจะกระทบกับกรณีอื่นอย่างไรบ้าง (บางครั้งก็จินตนาการไม่ได้ว่าเอาชีทนั้นไปทำอะไรบ้าง)
 
เลขผู้เสียภาษีของนิติบุคคลขึ้นต้นด้วย 0 เช่น 01055562184604 เป็นเลขตามใบกำกับภาษีของร้านกาแฟที่ผมนั่งอยู่ตอนนี้ หากส่งข้อมูลตัวเลขอย่างนี้ไปเข้าชีทจะถูกแปลงเป็นค่าตัวเลข (Number value) ทำให้เลข 0 ข้างหน้าหายไป 1055562184604 รวมทั้งอาจสั่งให้จัดรูปแบบมีเครื่องหมายคั่นและ ทศนิยมด้วย Number Format 1,055,562,184,604.00
 
การรักษารูปแบบของข้อความที่เป็นตัวเลขล้วน ไม่ให้ถูกตีความว่าเป็นค่าตัวเลข จะต้องทำอย่างไร?
 
ใช้เครื่องหมาย "" ครอบเหมือนกับที่ใช้ในภาษาเขียนโปรแกรมทั้งหลาย?
 
เสียใจด้วย "0123" กลายเป็น 123 อ่านเข้ามาในชีต
 
วิธีที่ทำได้คือ ใส่ติ่ง ' (Single Quote) ไว้ข้างหน้า เพื่อบังคับชีตแสดงผลโดยไม่พยายามปรับฟอร์แมตเป็นตัวเลข เช่น '0123
 
Presentable vs Interchageable format
 
ความผิดพลาดของผมอยู่ที่คิดน้อยไปหน่อย นึกถึงแค่กรณีเอาชีตไปใช้สรุปหรือพิมพ์ออกมาสวย ๆ ไม่ทันนึกถึงกรณีของการนำไปใช้ในฐานะเครื่องมือจัดเตรียมข้อมูล สามารถนำกลับมาเข้าดาต้าเบสได้ และถ้าบังเอิญตัวเลขนั้นเป็นรหัสที่ใช้อ้างอิง ก็อาจมีปัญหาทำให้ดาต้าเบสผิดเพี้ยนได้
 
ในความเป็นจริง CSV เป็นตัวกลางที่ไม่ดีเท่าไหร่ในการใช้แลกเปลี่ยนข้อมูลระหว่างชีต กับโปรแกรมภายนอกอื่น ๆ เพราะไม่สามารถควบคุม data type ว่าควรเป็น text หรือ number แต่ก็เป็นทางเลือกเดียวที่ทำได้ง่ายที่สุด เพราะคุณไม่ต้องแกะฟอร์แมตของไฟล์ xlsx
 
เพราะมาตรฐานเพิ่งกำหนดออกมาได้ไม่ถึง 10 ปี แต่ excel ใช้ CSV มากว่า 30 ปี ต้นกำเนิดย้อนไปตั้งแต่ยุค Fortran 50 ปีที่แล้ว สมัยนั้นโลกของโปรแกรมก็ต่างคนต่างอยู่ ไม่ได้สนใจแลกเปลี่ยนข้อมูลกัน เหมือนกับโลกยุคที่ผู้คนยังไม่เดินทางไปมาหาสู่กันอย่าทุกวันนี้ มาตรฐานเพื่อบังคับให้โปรแกรมอื่นทำเหมือนกันจึงไม่ใช่สิ่งจำเป็น CSV จึงเป็นเรื่องของเริ่มต้นจากอะไรง่าย ๆ แล้วก็หาทางแก้ปัญหาแบบ workaround กันไปเรื่อย ๆ ไม่นึกว่าจะยืนยงใช้กันมานานขนาดนี้
 
เพราะ CSV ออกแบบให้ใช้ , (comma) คั่นระหว่างข้อมูล แล้วถ้าภายในข้อความมี comma อยู่จะทำอย่างไร จึงกำหนดให้ใช้ " (double quote) ครอบเพื่อบอกให้รู้ว่า comma ที่อยู่ระหว่าง double quote ไม่ใช่ตัวคั่นข้อมูล
 
ทีนี้ปัญหาก็ตามมา แล้วถ้าข้อความนั้นมี double quote ล่ะ เอางี้ไม่ต้องคิดมาก กำหนดให้ doubled double quote เช่น """Hello""" หมายถึงคำว่า "Hello"

ความไม่คิดเยอะของชีต มีตั้งแต่คำถามว่าค่าที่อยู่ในเซลล์ คือข้อมูลหรือคำสั่งแสดงผล เพื่อแก้ปัญหาให้การแสดงผลในชีตสามารถแสดงเลข 0 นำหน้าได้จึงออกแบบให้สามารถใส่ single quote นำหน้าอย่างที่กล่าวมาแล้ว แปลว่าข้อความที่อยู่ในเซลล์มี single quote เพื่อทำให้เราเห็นโดยไม่มี single quote 
 
สมมติว่าคุณสั่งบันทึกข้อมูลจากชีตมาเป็น CSV บังเอิญว่าภายในชีตมีเซลที่มีค่า
 
'003 , "Hello, World" , 1750.80 

หากคุณเอาไฟล์ CSV นี้ไปโหลดเข้าชีตใหม่ จะได้ผลลัพธ์เหมือนต้นฉบับหรือไม่
 
คำตอบคือ ไม่มีวันเหมือน ค่า id บรรทัดที่ 3 และ 6 จะกลายเป็นค่าตัวเลข 2 และ 3 ตามลำดับ ขณะที่ข้อความ "Hello, World" แปลง double quote ให้ แต่ทำไมข้อความตัวเลขที่มี 0 นำหน้าไม่เป็น '003 
 
เพื่อจะดีลกับชีทผ่าน CSV โปรแกรมภายนอกจะต้องแยกเงื่อนไขการใช้งานให้ชัดเจน
 
CSV ที่โปรแกรมต้อง export เพื่อนำไปใช้กับชีท
CSV ที่โปรแกรมต้อง import มาจากชีท
 
ไม่ควรมี CSV ที่ออกจากโปรแกรมสำหรับใช้กับโปรแกรม เพราะจะสับสนกับสองกรณีแรก (หาทางใช้ format อื่นเถอะ JSON, YAML หรือ XML ก็ได้)
 
CSV export to Workheet
 
มีข้อมูลบางอย่างที่ควรระมัดระวัง และหากเป็นไปได้ไม่ควรคาดหวังว่า worksheet ที่อ่านข้อมูลของผู้ที่นำข้อมูลจาก CSV นี้ไปใช้จะตั้งค่าสภาพแวดล้อมโดยเฉพาะ locale เหมือนกันทุกคน ความแตกต่างเหล่านี้มีผลกับการนำเข้าข้อมูล
 
ตัวเลข ในความหมายของ "ตัวอักษร" เมื่อจำเป็นต้องใช้ตัวเลขเพื่อเป็น ID หรือรหัสอะไรบางอย่าง ไม่ใช่ค่าที่ใช้คำนวณ วิธีที่ดีที่สุดคือ ใส่ "ติ่ง" นำหน้าเพื่อป้องกัน ตัวอย่างที่พบเจอบ่อยสำหรับผม การ export ข้อมูลรายงานภาษี ที่มีคอลัมน์เลขผู้เสียภาษี 13 ตัวที่มีเลข 0 นำหน้า เมื่อเข้าไปในชีทกลายเป็นเลข 12 ตัว
 
ตัวเลข ในความหมายของ "ค่าตัวเลข" อย่าพยายามจัดฟอร์แมตใด ๆ เช่น ใส่ , คั่นหลักพันและล้าน ใช้ค่าเลขแบบดิบ ๆ นั่นแหละ ไม่ว่าจะยาวแค่ไหน มีทศนิยมกี่ตำแหน่ง เพราะสุดท้ายเมื่อเข้าไปในชีท ผู้ใช้จะเลือกฟอร์แมตของตัวเลขได้เอง
 
วันที่ เป็นตัวอักษรที่มีรูปแบบพิเศษ และแตกต่างกันแต่ละประเทศ และเช่นเดียวกับกรณีตัวเลขในความหมายของ "ค่า" ไม่ควรใช้รูปแบบที่มนุษย์ชอบเพราะมีหลากหลายรูปแบบ บางคนใช้ชื่อเดือนย่อ ชื่อเดือนเต็ม ปีพ.ศ. หรือ ค.ศ. มีทั้งเลขปี 4 หลัก และเลขปี 2 หลัก หากผู้ใช้ชีทตั้ง locale เป็น en-us รูปแบบของวันที่จะกลายเป็น เดือน/วัน/ปี กรณีของข้อมูลวันที่เมื่อส่งมาในรูปแบบ CSV ผมจะใช้มาตรฐาน ISO Date "YYYY-MM-DD"
 
ข้อความ ที่ไม่สามารถตีความเป็นตัวเลขล้วน คำแนะนำเบื้องต้นหากไม่อยากเขียนโค้ดตรวจอะไรมากก็ครอบด้วย "ฟันหนู" (double quote) จุดที่ต้องระวังหากภายในข้อความมีฟันหนู จะต้องหลบ (escape) ด้วยการขยายให้เป็นฟันหนูคู่ "" ข้อดีของการใช้ฟันหนูจะทำให้ค่าในเซลเป็น ว่างแบบไม่มีตัวอักษร (empty string) ไม่ใช่ว่างแบบไม่มีค่า (null) เรื่องเล็กน้อยพวกนี้บางครั้งมีผลกระทบต่อสูตรคำนวณในชีท ทำให้ได้ผลลัพธ์ไม่เหมือนกัน
 
ค่าเปอร์เซนต์ อย่าส่งออกไปในรูปแบบที่มีสัญญลักษณ์ % ควรแปลงให้เป็นค่าตัวเลขทศนิยม เช่น 10% คือ 0.1 

 
CSV import from Worksheet
 
ความสับสนของโปรแกรม Worksheet เมื่อพยายามบันทึกข้อมูลออกมาเป็น CSV อยู่ตรงที่ข้อมูลบางอย่างก็ส่งออกมาเป็นค่าภายใน ข้อมูลอย่างก็ส่งออกมาตามรูปแบบที่เห็นในชีท
 
ปกติเมื่อเราไม่จำเป็นต้องเขียนโค้ดอ่านไฟล์ CSV แบบดิบ ๆ เอง สามารถหา libary ที่อ่านและถอดรหัส CSV ออกมาเป็นข้อมูลได้อยู่แล้ว แต่ก็ต้องมีความยืดหยุ่นพอที่จะจัดการรูปแบบที่เป็น presentation format ในบางกรณี
 
ข้อความที่ขึ้นต้นฟันหนู "(double quote) จะต้องสแกนจนกว่าจะพบฟันหนูสิ้นสุด โดยข้ามลูกน้ำ (comma) และดับเบิ้ลฟันหนู และตัวขึ้นบรรทัดใหม่ เมื่อสะกัดมาได้แล้ว ก็ต้องทำความสะอาด (clean) เอาฟันหนูที่ครอบออก เปลี่ยนดับเบิ้ลฟันหนู ให้กลายเป็นฟันหนูเดี่ยว โชคดีที่ libary สามารถจัดการตรงนี้ให้ได้

 
ข้อความที่ถูกบังคับด้วยติ่ง ' (single quote) เมื่อเป็น CSV จะถูกตัดติ่งออก ทำให้สูญเสียการคงรูปของข้อมูล ดังที่กล่าวมาแล้ว เลขผู้เสียภาษีที่มี 0 นำหน้า เมื่อส่งออกมาเป็น CSV แล้วเอาไปโหลดเข้าชีทใหม่ เลข 0 จะหายไป ไม่ต้องทำอะไร
 
สิ่งที่ควรระวัง หากเป็นการอ่าน CSV แบบอิสระ ไม่สามารถทำ validate ด้วย schema จากโปรแกรมได้ ผลลัพธ์ที่ได้จะต้องมาทำ type casting เอง ตัวเลขทั้งหมดจะอยู่ในรูปแบบของข้อความ หากต้องการใช้เป็น integer หรือ float ก็ต้องคลีนเองอีกรอบ
 
ตัวเลข ในความหมายของ "ค่าตัวเลข" บางครั้งอาจได้มาในรูปแบบที่สวยงามอ่านง่ายสำหรับมนุษย์ เพราะผู้ใช้ต้องตรวจสอบตัวเลขก่อนส่งออกมาให้โปรแกรม เช่น มีลูกน้ำคั่นหลักพันและล้าน หรืออาจได้รับมาในรูปแบบที่มีสัญญลักษณ์เปอร์เซนต์ ตรงนี้โปรแกรมควรมีความสามารถทำความสะอาดได้
 
วันที่ เช่นเดียวกัน อาจเจอวันที่ในรูปแบบสวยงามหลากหลาย ก็หาทาง cast ให้เป็น Date type เพื่อใช้ในโปรแกรมกันเอง หรือบังคับต้นทางให้ใช้รูปแบบวันที่ ที่โปรแกรมอ่านรู้เรื่อง
 
ติ่งนำหน้า อาจไม่เจอใน CSV ที่ออกมาจากชีท แต่ผมเสนอให้ทำเผื่อไว้ ตรวจและตัดติ่งก่อนเอาไปใช้ ช่วยให้รองรับ CSV ที่มาจากโปรแกรมที่ export to worksheet ซึ่งต้องมีติ่งนำหน้า
 
วิธีการหนึ่งที่ผมใช้คาดเอา data type ของข้อมูล CSV ที่ไม่รู้ schema คือการสำรวจจากข้อมูลในคอลัมน์นั้น ๆ เพื่อกำหนด type ให้
 
ระวังกรณีรูปแบบสวยงามของตัวเลข ที่ยังต้องถือว่าเป็นตัวเลข
ระวังกรณีของรูปแบบสวยงามของวันที่ ที่ยังต้องถือว่าเป็นวันที่
หากสแกนแล้วทุกแถวไม่ใช่ตัวเลข ไม่ใช่วันที่ ทั้งหมด ก็ควรตีความว่าเป็นข้อความ (เคยเจอไหม ช่องจำนวนเงิน แต่ใส่คำว่า "ยกเลิก")
 
ลองไปทดสอบกันดูว่า โปรแกรมที่คุณใช้ สื่อสารกับ Excel หรือ Sheets ของผู้ใช้เก่งแค่ไหน
 
อ้างอิง
 
Microsoft Community - Why Excel https://answers.microsoft.com/en-us/msoffice/forum/all/why-excel-does-not-support-rfc-4180-standard-for/d0e379b1-ec3e-40d0-ad4f-33b20693c030
 
RFC 4180 - This memo provides information for the Internet community. It does not specify an Internet standard of any kind. https://datatracker.ietf.org/doc/html/rfc4180
 
Wikipedia - Comma Seperated Value https://en.wikipedia.org/wiki/Comma-separated_values