Excel hiển thị ký tự lộn xộn trong CSV? Cách sửa lỗi với UTF-8 BOM
Tại sao file CSV của bạn trông ổn ở mọi nơi, trừ Excel
Bạn xuất một file CSV từ cơ sở dữ liệu hoặc CRM của mình. Bạn mở nó trong một trình soạn thảo văn bản và mọi thứ đều hoàn hảo. Các ký tự có dấu, chữ Kanji tiếng Nhật, ký hiệu Euro – tất cả đều hiển thị đúng. Sau đó, bạn nhấp đúp để mở trong Excel, và mọi thứ trở nên hỗn loạn. Bạn đang nhìn chằm chằm vào các chuỗi ký tự bị lỗi như 'é' thay vì 'é', hoặc '¥' thay vì '¥', hoặc cả một cột đầy dấu hỏi. Bản thân file không hề thay đổi. Vấn đề nằm ở Excel. Khi bạn mở một file CSV bằng cách nhấp đúp, Microsoft Excel – đặc biệt trên Windows – không tự động hiểu đó là UTF-8. Nó sẽ quay về sử dụng bảng mã cũ (legacy code page) của hệ thống bạn. Đối với hầu hết người dùng phương Tây, đó là Windows-1252 (còn gọi là CP1252). Đối với người dùng Nhật Bản, đó là Shift-JIS. Khi một file UTF-8 bị ép đọc theo cách hiểu của Windows-1252, mọi ký tự sử dụng nhiều hơn một byte đều bị biến dạng, tạo ra những ký tự vô nghĩa được gọi là mojibake. Đây không phải là một lỗi mới. Đây là một vấn đề gây khó chịu đã tồn tại từ Excel 2010, 2013, 2016, 2019 và vẫn xuất hiện trong Microsoft 365 tính đến năm 2025. Nếu bạn chỉ nhấp đúp vào một file CSV UTF-8 thông thường, bạn đang đánh cược. Mặc dù Microsoft đã bổ sung một số tính năng phát hiện UTF-8 tốt hơn trong các bản dựng M365 gần đây, hành vi này lại cực kỳ không nhất quán, tùy thuộc vào cài đặt khu vực, phiên bản Office của bạn, và đôi khi, dường như còn phụ thuộc vào... chu kỳ mặt trăng nữa. Giải pháp đáng tin cậy là sử dụng UTF-8 BOM – Byte Order Mark. Đây là một chuỗi ba byte đặc biệt, vô hình (0xEF, 0xBB, 0xBF) nằm ngay đầu file, hoạt động như một tín hiệu gửi đến Excel, nói rằng 'Này! File này là UTF-8 đấy, hãy đọc nó theo cách đó nhé.' Excel tôn trọng tín hiệu này, ngay cả trong các phiên bản cũ hơn. Phần còn lại của bài viết này sẽ giải thích cách thêm nó, khi nào *không* nên thêm nó, và cách CocoConvert có thể giúp bạn xử lý vấn đề này.
BOM thực sự là gì (và không phải là gì)
Byte Order Mark ban đầu xuất phát từ thế giới UTF-16 và UTF-32, nơi thứ tự byte (big-endian so với little-endian) là một vấn đề đáng quan tâm. BOM cho chương trình biết thứ tự của các byte. Nhưng đối với UTF-8, thứ tự byte không phải là vấn đề; nó luôn giống nhau. Vì vậy, từ góc độ kỹ thuật thuần túy, UTF-8 BOM (ký tự U+FEFF được mã hóa thành ba byte: EF BB BF) hoàn toàn không cần thiết. Nó không cần thiết, nhưng lại trở thành 'mật mã' giúp Excel hoạt động đúng. Khi Excel nhìn thấy ba byte đó ở đầu file, nó ngay lập tức chuyển sang chế độ UTF-8. Nếu không có chúng, nó sẽ mặc định theo cài đặt khu vực của mình, và bạn sẽ lại thấy những ký tự mojibake quen thuộc đó. Đây là vấn đề: BOM giúp sửa lỗi Excel nhưng lại có thể làm hỏng nhiều phần mềm khác. Đây là điểm khiến rất nhiều quy trình xử lý dữ liệu tự động gặp trục trặc. Hàm `open()` tiêu chuẩn của Python, nếu bạn quên chỉ định `encoding='utf-8-sig'`, sẽ đọc BOM như một phần của trường dữ liệu đầu tiên của bạn. Câu lệnh `LOAD DATA INFILE` của MySQL sẽ nghĩ BOM là một phần của tên cột đầu tiên, làm hỏng tiêu đề của bạn. Nhiều công cụ dòng lệnh Linux cổ điển như `grep`, `awk` và `wc` không xử lý tốt các file có tiền tố BOM. Lệnh `COPY` của PostgreSQL thậm chí còn nghiêm ngặt hơn và sẽ lỗi ngay ở tiêu đề cột đầu tiên. Nguyên tắc chung của tôi rất đơn giản: chỉ thêm BOM nếu bạn biết đích đến cuối cùng của file là một người dùng nhấp đúp vào nó trong Excel. Nếu file CSV của bạn dành cho việc nhập vào cơ sở dữ liệu, một script Python hoặc một pipeline Unix, bạn muốn một file UTF-8 sạch *không* có BOM. Bạn vẫn có thể mở nó đúng cách trong Excel, bạn chỉ cần sử dụng Trình hướng dẫn nhập văn bản (Text Import Wizard), điều mà chúng ta sẽ đề cập sau.
Ba cách để thêm UTF-8 BOM thủ công
Nếu bạn đang mắc kẹt với một file CSV bị lỗi ký tự và cần sửa ngay lập tức, bạn không cần một dịch vụ phức tạp nào cả. Dưới đây là ba cách đáng tin cậy để bạn tự thêm BOM. **Sử dụng Notepad++ trên Windows:** Đây thường là cách sửa lỗi nhanh nhất. Mở file CSV của bạn trong Notepad++. Vào menu `Encoding`. Bạn có thể sẽ thấy nó đã được đặt là 'UTF-8'. Đó chính là vấn đề – nó là UTF-8 *không* có BOM. Nhấp vào tùy chọn 'Encode in UTF-8 BOM' và sau đó lưu file. Xong. File của bạn giờ đây đã có tiền tố ba byte 'thần kỳ' và Excel sẽ mở nó đúng cách. **Với một lệnh Python một dòng:** Nếu bạn quen thuộc với terminal, lệnh đơn này là một cách mạnh mẽ để chuyển đổi bất kỳ file UTF-8 nào sang UTF-8 có BOM. Nó hoạt động trên mọi hệ điều hành có Python 3. ``` python3 -c "open('output.csv','wb').write(b'\xef\xbb\xbf'+open('input.csv','rb').read())" ``` Lệnh này đọc `input.csv` của bạn dưới dạng byte thô, thêm ba byte BOM vào phía trước, và ghi mọi thứ vào `output.csv`. Không cần thư viện bổ sung nào. **Với Trình hướng dẫn nhập văn bản của Excel:** Thay vì thay đổi file, bạn có thể chỉ cần hướng dẫn Excel cách đọc nó một cách chính xác. Vào `Data → Get & Transform Data → From Text/CSV` (trong Excel hiện đại) hoặc `Data → Get External Data → From Text` (trong các phiên bản cũ hơn). Bước quan trọng là tìm cài đặt 'File Origin' trong hộp thoại nhập và thay đổi nó thành `65001: Unicode (UTF-8)`. Điều này buộc Excel sử dụng đúng mã hóa. Nhược điểm lớn là: cách khắc phục này chỉ tạm thời và chỉ áp dụng cho phiên nhập dữ liệu của bạn. Người tiếp theo nhấp đúp vào file sẽ thấy cùng một mớ hỗn độn ký tự bị lỗi. Không có phương pháp thủ công nào trong số này thực sự tốt cho một quy trình có thể lặp lại. Đó là lúc việc tự động hóa chuyển đổi, với BOM là một tùy chọn, thực sự bắt đầu có ý nghĩa.
CocoConvert xử lý UTF-8 BOM trong quá trình chuyển đổi file như thế nào
Khi bạn sử dụng CocoConvert để chuyển đổi một file sang định dạng CSV – dù là từ Excel, JSON, XML hay bất kỳ định dạng nào khác – chúng tôi cung cấp cho bạn quyền kiểm soát trực tiếp vấn đề này. Trong phần cài đặt đầu ra, bạn sẽ tìm thấy một nút gạt 'Thêm UTF-8 BOM để tương thích với Excel'. Chúng tôi để nó tắt theo mặc định, bởi vì như chúng ta đã thấy, BOM có thể gây ra nhiều vấn đề như nó giải quyết trong các môi trường không phải Excel. Nhưng nếu bạn cần, chỉ cần bật công tắc. Đối với bất kỳ quy trình làm việc nào mà kết thúc bằng việc ai đó trong bộ phận kế toán mở một file, quy trình này rất đơn giản. Tải lên file nguồn của bạn, chọn CSV làm định dạng đầu ra, bật nút gạt BOM, và tải xuống. File CSV kết quả sẽ mở hoàn hảo trong Excel chỉ với một cú nhấp đúp, không cần trình hướng dẫn nhập thủ công. Cài đặt này cũng áp dụng cho chuyển đổi hàng loạt, vì vậy nếu bạn có 50 file xuất sản phẩm từ một cửa hàng Shopify, bạn có thể xử lý tất cả cùng lúc và có tất cả chúng sẵn sàng cho Excel. Điều quan trọng là phải rõ ràng về những gì công cụ của chúng tôi làm và không làm. CocoConvert không thể 'phép thuật' sửa chữa các vấn đề mã hóa đã có sẵn trong file nguồn của bạn. Nếu một hệ thống cũ cung cấp cho bạn một file CSV đã bị hỏng do xuất lỗi từ Windows-1252, chúng tôi sẽ cố gắng hết sức để chuyển đổi ký tự, nhưng một số dữ liệu có thể bị mất. Bạn sẽ nhận được cảnh báo nếu điều đó xảy ra. Chúng tôi cũng không đoán xem bạn có cần BOM hay không; đó là quyết định của bạn, dựa trên nơi file sẽ được sử dụng. Công cụ cung cấp tùy chọn, nhưng bạn phải hiểu quy trình làm việc của riêng mình. Cuối cùng, nếu bạn đang chuyển đổi một định dạng đã biết mã hóa của nó, như file XLSX, chúng tôi sẽ đọc thông tin đó một cách chính xác. Nút gạt BOM trong trường hợp đó hoàn toàn chỉ để làm cho file CSV *đầu ra* tương thích với Excel, chứ không phải để sửa lỗi file nguồn.
Trình hướng dẫn nhập văn bản của Excel: Khi nào nên sử dụng thay thế
Đôi khi, việc thêm BOM vào file CSV của bạn là một bước đi sai lầm, và trình hướng dẫn nhập riêng của Excel mới là lựa chọn đúng đắn. Kịch bản phổ biến nhất là khi bạn nhận các file CSV từ một hệ thống bên ngoài mà bạn không kiểm soát. Nếu hệ thống đó tạo ra các file UTF-8 sạch *không* có BOM, bạn không nên phải chạy tất cả chúng qua một công cụ riêng biệt chỉ để thêm ba byte. Trong Excel 2016 và các phiên bản cũ hơn, hãy điều hướng đến `Data → From Text`. Khi Trình hướng dẫn nhập văn bản khởi chạy, bước đầu tiên có một danh sách thả xuống 'File origin'. Bạn cần thay đổi cài đặt này từ mặc định (thường là 'Windows (ANSI)') thành `65001: Unicode (UTF-8)`. Sau đó, hoàn thành trình hướng dẫn như bình thường, và dữ liệu của bạn sẽ hiển thị đúng. Trong Microsoft 365 và Excel 2019, đường dẫn là `Data → Get Data → From File → From Text/CSV`. Trình nhập Power Query mới hơn này tốt hơn trong việc tự động phát hiện UTF-8, nhưng nó không hoàn hảo. Nếu bản xem trước trông không đúng, hãy tìm danh sách thả xuống 'File Origin' hoặc 'Encoding' trong hộp thoại và đặt thủ công thành UTF-8. Hạn chế lớn, như chúng ta đã đề cập, là cách khắc phục này không 'dính'. Bản thân file vẫn không thay đổi. Nếu bạn gửi email cho đồng nghiệp, họ sẽ nhấp đúp vào nó và thấy cùng một văn bản bị lỗi. Trình hướng dẫn là một công cụ tuyệt vời nếu bạn là người duy nhất xử lý file. Nếu bạn đang phân phối nó, bạn thực sự cần nhúng BOM vào chính file đó. Trình hướng dẫn cũng là lựa chọn đúng đắn khi file CSV của bạn cần phải sạch sẽ cho các quy trình khác, như nhập cơ sở dữ liệu, nhưng bạn chỉ cần xem nhanh trong Excel.
Các vấn đề mã hóa ký tự ngoài BOM
Khắc phục sự cố UTF-8 BOM giải quyết vấn đề ký tự phổ biến nhất của Excel, nhưng nó còn xa mới là cơn đau đầu về mã hóa duy nhất bạn sẽ gặp phải với các file CSV. Dưới đây là một vài 'thủ phạm' khác cần chú ý. **File nguồn Windows-1252**: Nhiều hệ thống cũ hơn, đặc biệt là các hệ thống ERP kế thừa và các nền tảng thương mại điện tử thế hệ đầu, vẫn xuất dữ liệu ở định dạng Windows-1252. Mã hóa này xử lý các ký tự Tây Âu như é, ü và ñ rất tốt, nhưng nó hoàn toàn không hoạt động với bất kỳ ngôn ngữ nào nằm ngoài bộ đó. Nếu bạn đang cố gắng hợp nhất dữ liệu này với một nguồn UTF-8, bạn cần một bước mã hóa lại thực sự, chứ không chỉ là thêm BOM. CocoConvert có thể xử lý việc này nếu bạn chỉ định mã hóa nguồn, hoặc nó sẽ cố gắng tự động phát hiện – điều mà các thử nghiệm của chúng tôi cho thấy hoạt động khoảng 94% thời gian. Các lỗi xảy ra với các file về mặt kỹ thuật hợp lệ trong nhiều mã hóa cùng một lúc. **Nhầm lẫn về dấu phân cách**: Bất cứ ai đã dành hàng giờ để gỡ lỗi một vấn đề 'mã hóa' chỉ để rồi phát hiện ra đó là dấu chấm phẩy thay vì dấu phẩy đều hiểu nỗi đau này. Nếu một file CSV sử dụng dấu chấm phẩy làm dấu phân cách nhưng cài đặt Excel của bạn lại mong đợi dấu phẩy, tất cả dữ liệu sẽ bị dồn vào cột đầu tiên. Nó trông giống như một mớ hỗn độn ký tự, nhưng đó không phải là vấn đề mã hóa. Cách khắc phục là sử dụng trình hướng dẫn nhập và chỉ định dấu phân cách chính xác. **'Dấu ngoặc kép thông minh' và dấu gạch ngang đặc biệt của Excel**: Khi dữ liệu đã đi qua Microsoft Word hoặc Outlook, nó thường 'nhặt' các dấu ngoặc kép xoăn "smart quotes" và dấu gạch ngang dài (em dashes). Đây là các ký tự UTF-8 hợp lệ và trông ổn trong hầu hết các ứng dụng hiện đại, nhưng chúng sẽ làm hỏng các truy vấn cơ sở dữ liệu và script mong đợi các dấu câu ASCII đơn giản. CocoConvert cung cấp tính năng 'chuẩn hóa dấu ngoặc kép thông minh' tùy chọn cho đầu ra CSV, thay thế chúng bằng phiên bản ASCII thuần túy. Đây là một thay đổi phá hủy dữ liệu của bạn, vì vậy chúng tôi để nó là tùy chọn. **Byte NULL trong dữ liệu**: Một số bản xuất cơ sở dữ liệu có thể nhúng byte NULL (0x00) vào các trường văn bản. Đây là một 'điểm dừng tuyệt đối' đối với hầu hết mọi trình phân tích CSV trên thế giới. Không có phép thuật mã hóa nào có thể sửa một file có byte NULL; chúng phải được loại bỏ hoặc thay thế trước khi file có thể được sử dụng.
Danh sách kiểm tra thực tế trước khi bạn chuyển đổi hoặc mở một file CSV
Sau khi 'vật lộn' với các vấn đề mã hóa qua hàng ngàn lần chuyển đổi file, chúng tôi đã phát hiện ra danh sách kiểm tra này giúp phát hiện phần lớn các vấn đề ký tự CSV trước khi chúng bắt đầu. **Trước khi bạn xuất từ một hệ thống nguồn:** Hãy tìm tùy chọn mã hóa. Các nền tảng hiện đại như Salesforce, HubSpot và Shopify đều cho phép bạn chọn UTF-8 cho các bản xuất. Hãy sử dụng nó. Nếu tùy chọn duy nhất là 'default' hoặc 'system encoding', hãy nghi ngờ. Mở file đầu ra trong một trình soạn thảo văn bản như VS Code hoặc Notepad++ hiển thị mã hóa trước khi bạn gửi nó cho bất kỳ ai. **Trước khi bạn mở một file CSV trong Excel:** Tự hỏi bản thân: file này có BOM không? Trong VS Code, mã hóa nằm ngay trên thanh trạng thái. Trong Notepad++, hãy kiểm tra menu Encoding. Nếu nó ghi 'UTF-8' và bạn cần sử dụng Excel, lựa chọn của bạn là tự thêm BOM hoặc sử dụng trình hướng dẫn nhập. Đừng bao giờ chỉ nhấp đúp và cầu mong điều tốt đẹp nhất. **Trước khi bạn đưa một file CSV vào một script hoặc cơ sở dữ liệu:** Hãy chú ý đến BOM, đặc biệt nếu file đó đến từ người dùng Windows. Trong Python, sử dụng `encoding='utf-8-sig'` là cách sạch nhất để xử lý nó tự động. Đối với MySQL, bạn sẽ cần loại bỏ BOM trước khi nhập hoặc sử dụng câu lệnh `LOAD DATA` chỉ định `CHARACTER SET utf8mb4`. Đối với PostgreSQL, chỉ cần loại bỏ nó; lệnh `COPY` không 'khoan dung'. Khi sử dụng CocoConvert, hãy nhớ quy tắc: chỉ bật nút gạt UTF-8 BOM nếu bạn biết file sẽ trực tiếp đến tay người dùng Excel và họ sẽ nhấp đúp vào nó. Đối với bất kỳ đích đến nào khác – cơ sở dữ liệu, API, script – hãy để nó tắt. Nếu bạn nghi ngờ file nguồn của mình có vấn đề, hãy dành thêm mười giây để chỉ định rõ ràng mã hóa của nó. Việc này nhanh hơn nhiều so với việc sửa một bản chuyển đổi bị lỗi. BOM là một thứ rất nhỏ – chỉ ba byte. Nhưng nó nằm ngay tại ranh giới giữa các giả định khác nhau về cách các file văn bản nên hoạt động, gây ra một lượng lớn sự khó chịu không tương xứng. Biết khi nào nên sử dụng, khi nào nên tránh, và cách khắc phục nó là chìa khóa để giữ cho dữ liệu CSV của bạn lưu chuyển sạch sẽ giữa các công cụ.