แปลง Excel เป็น CSV ไม่ให้ตัวอักษรเพี้ยน: UTF-8, BOM และตัวคั่น

ตัวอักษรมีเครื่องหมายกำกับที่เพี้ยนในไฟล์ CSV ส่วนใหญ่มาจากการระบุการเข้ารหัสไม่ตรงกัน ไฟล์ถูกบันทึกเป็น UTF-8 แต่เปิดด้วยการเข้ารหัสแบบ Windows ส่งออกชีตเป็น CSV UTF-8 โดยใส่ byte order mark (BOM) ด้วย หากโปรแกรมที่จะเปิดไฟล์คือ Excel และเลือกตัวคั่นที่โลเคลของคุณใช้ ก่อนส่งออก ให้จัดรูปแบบคอลัมน์อย่างรหัสไปรษณีย์และเบอร์โทรศัพท์เป็นข้อความ เพื่อให้เลขศูนย์ไม่หาย

แปลง Excel เป็น CSVต้องการไฟล์ CSV แต่มีไฟล์ Excelใช้ฟรี

ทำไม "é" ถึงกลายเป็น "é"

อักขระในไฟล์ถูกเก็บเป็นไบต์ ใน UTF-8 ตัวอักษร é ใช้สองไบต์คือ C3 และ A9 โปรแกรมที่อ่านไบต์เหล่านี้ด้วยการเข้ารหัสแบบ Windows-1252 ซึ่งเป็นการเข้ารหัสที่ใช้กันทั่วไปบน Windows จะเห็น C3 เป็น à และ A9 เป็น © ดังนั้น "café" จึงแสดงเป็น "café" คุณลองตรวจสอบเองได้: เข้ารหัส é เป็น UTF-8 แล้วถอดรหัสผลลัพธ์ด้วย Windows-1252 จะได้ é ตัวอักษร ü, ñ หรือ ö ก็เพี้ยนในลักษณะเดียวกัน

วิธีแก้ไม่ใช่การแก้ตัวอักษรเอง แต่คือการบอกโปรแกรมว่าไฟล์ใช้การเข้ารหัสแบบใด

UTF-8 และ BOM

UTF-8 เป็นการเข้ารหัสมาตรฐานสำหรับข้อความบนเว็บและในซอฟต์แวร์สมัยใหม่ส่วนใหญ่ ไฟล์ CSV ธรรมดาไม่มีช่องที่บอกว่าใช้การเข้ารหัสอะไร โปรแกรมจึงต้องเดา BOM คือไบต์สามตัวที่จุดเริ่มต้นของไฟล์ คือ EF BB BF ซึ่งบอกโปรแกรมที่มองหาสัญญาณนี้ว่าเป็น UTF-8 Excel บน Windows มักเปิดไฟล์ UTF-8 ที่ไม่มี BOM ด้วยการเข้ารหัสของเครื่อง ดังนั้น BOM จึงเป็นวิธีแก้ที่พบบ่อย

Excel รุ่นใหม่มีตัวเลือก CSV UTF-8 ในเมนู Save As ซึ่งจะเพิ่ม BOM ให้ คุณยังนำเข้าไฟล์ผ่าน Data แล้วเลือก From Text/CSV และตั้ง File Origin เป็น 65001 ซึ่งคือ UTF-8 ก็ได้ BOM มีข้อเสียอยู่อย่างหนึ่ง: โปรแกรมที่ไม่คาดหวังมันอาจมองว่าเป็นส่วนหนึ่งของชื่อคอลัมน์แรก ตัวแยกข้อมูลและโปรแกรมสเปรดชีตส่วนใหญ่จัดการเรื่องนี้ได้ แต่ถ้าตัวนำเข้าข้อมูลแจ้งว่าหัวตารางแปลก ให้ลบ BOM ออก

จุลภาคหรือเซมิโคลอน

CSV ย่อมาจาก comma-separated values และข้อกำหนด RFC 4180 อธิบายฟิลด์ที่คั่นด้วยจุลภาค ในหลายประเทศ รวมถึงประเทศที่ใช้จุลภาคเป็นจุดทศนิยม เช่น 1,5 แทนหนึ่งจุดห้า ตัวคั่นรายการของ Excel คือเซมิโคลอน เมื่อเป็นเช่นนั้น ไฟล์ที่คั่นด้วยจุลภาคเมื่อเปิดใน Excel อาจแยกผิดพลาด และตัวเลขอย่าง 1,5 จะกลายเป็นสองฟิลด์

เลือกตัวคั่นที่โปรแกรมปลายทางคาดหวัง เครื่องมือแปลง Excel เป็น CSV รองรับจุลภาค เซมิโคลอน และแท็บ หากไฟล์จะใช้กับโปรแกรมในโลเคลที่ใช้จุลภาคเป็นจุดทศนิยม เซมิโคลอนมักเป็นตัวเลือกที่ปลอดภัยกว่า

วันที่กลายเป็นเลขลำดับ

Excel เก็บวันที่เป็นเลขลำดับ โดยนับวันจากจุดเริ่มต้นหนึ่ง ในระบบวันที่แบบ 1900 วันที่ 1 มกราคม 1900 คือลำดับที่ 1 Excel ยังถือว่า 1900 เป็นปีอธิกสุรทิน จึงนับวันที่ 29 กุมภาพันธ์ 1900 ซึ่งไม่เคยมีอยู่จริง เป็นลำดับที่ 60 ทำให้วันที่ 1 มีนาคม 1900 เป็นลำดับที่ 61 และเลื่อนทุกวันที่หลังจากนั้นไปหนึ่งวัน ข้อบกพร่องนี้มาจาก Lotus 1-2-3 และ Microsoft ยังคงระบบเดิมไว้เพื่อความเข้ากันได้ ซอฟต์แวร์อื่นอาจอ่านวันที่ก่อนวันที่ 1 มีนาคม 1900 แล้วคลาดไปหนึ่งวัน

เมื่อส่งออกวันที่ คุณอาจเห็น 44927 แทน 2023-01-01 หรือวันที่เก่าคลาดไปหนึ่งวัน เครื่องมือ จะแปลงวันที่เป็นข้อความที่อ่านได้ หากส่งออกเองด้วยมือ ให้จัดรูปแบบคอลัมน์วันที่เป็นข้อความในรูปแบบ ISO คือ yyyy-mm-dd ซึ่งทุกโปรแกรมอ่านได้ตรงกัน

เลขศูนย์นำหน้าหายไป

รหัสไปรษณีย์อย่าง 02134 เป็นข้อความ แต่ Excel จะถือว่าตัวเลขที่พิมพ์เข้าไปเป็นตัวเลข จึงแสดงเป็น 2134 เลขศูนย์หายไปจากชีต และ CSV ที่เขียนจากชีตนั้นก็จะไม่มีเลขศูนย์เช่นกัน เบอร์โทรศัพท์ที่ขึ้นต้นด้วย 0 และหมายเลข ID ที่ยาวก็เจอปัญหาเดียวกัน ตัวเลขที่ยาวมากอาจสูญหายบางหลักด้วย เพราะ Excel เก็บเลขนัยสำคัญประมาณ 15 หลักเท่านั้น

วิธีแก้คือเก็บคอลัมน์เหล่านั้นเป็นข้อความก่อนป้อนหรือนำเข้าข้อมูล เมื่อเปิด CSV ที่มีเลขศูนย์อยู่แล้ว ให้ใช้ Data แล้วเลือก From Text/CSV และตั้งคอลัมน์เป็น Text แทนการดับเบิลคลิกไฟล์

กฎการใส่เครื่องหมายคำพูด

RFC 4180 กำหนดรูปแบบ CSV ที่ใช้กันทั่วไป กฎหลักของการใส่เครื่องหมายคำพูดมีดังนี้

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

ตัวอย่างเช่น แถว "Müller, Anna","She said ""ok""" มีสองฟิลด์ คือ Müller, Anna และ She said "ok" ประเภท MIME ของ CSV ที่ IANA ลงทะเบียนไว้คือ text/csv

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

รายการตรวจก่อนส่งออก

1. ส่งออกเป็น CSV UTF-8 และใส่ BOM หาก Excel จะเปิดไฟล์
2. เลือกตัวคั่นที่โปรแกรมปลายทางคาดหวัง
3. จัดรูปแบบคอลัมน์ที่เป็นรหัสลักษณะนี้ เช่น รหัสไปรษณีย์และเบอร์โทรศัพท์ เป็นข้อความ
4. เขียนวันที่เป็น yyyy-mm-dd
5. เปิดไฟล์ในโปรแกรมแก้ไขข้อความธรรมดา แล้วตรวจบรรทัดแรกและตัวอย่างชื่อที่มีเครื่องหมายกำกับ

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

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

คำตอบสั้น ๆ

ทำไมไฟล์ของฉันดูถูกต้องในโปรแกรมหนึ่งแต่เพี้ยนใน Excel? เมื่อไฟล์ UTF-8 ไม่มี BOM Excel บน Windows อาจอ่านด้วยการเข้ารหัสของเครื่อง การเพิ่ม BOM หรือการนำเข้าโดยเลือก UTF-8 จะแก้ปัญหานี้ได้

Excel บน Mac ต้องใช้ BOM หรือไม่? บางแหล่งข้อมูลระบุว่าต้องใช้ ดังนั้นควรทดสอบด้วยไฟล์ที่มีชื่อมีเครื่องหมายกำกับก่อนส่งให้ผู้อื่น

แก้วันที่หลังส่งออกได้ไหม? ได้ หากยังเป็นเลขลำดับอยู่ แต่จะดีกว่าหากส่งออกเป็น yyyy-mm-dd ตั้งแต่แรก

ไฟล์สเปรดชีตของฉันถูกอัปโหลดเมื่อแปลงหรือไม่? เครื่องมือทำงานในเบราว์เซอร์ของคุณ ดังนั้นไฟล์จะถูกประมวลผลบนอุปกรณ์ของคุณเอง คุณตรวจสอบเรื่องนี้ได้ด้วย คู่มือตรวจสอบการอัปโหลดไฟล์