Vì sao Excel làm hỏng file CSV của bạn

Hư hại xảy ra ngay khi bạn mở file

Đây là điều khiến vấn đề khó chẩn đoán đến vậy. CSV là file văn bản: mọi giá trị trong đó đều là ký tự, và không có gì trong file cho biết chúng mang ý nghĩa gì. Khi mở một file CSV, Excel đoán kiểu dữ liệu cho từng cột, chuyển đổi giá trị cho khớp, và từ lúc đó chỉ giữ bản đã chuyển đổi. Lưu file lại là các thay đổi đó được ghi vào file.

Vậy là file gốc không có vấn đề, file bạn lưu lại thì có, và không lúc nào có cảnh báo. Người ta kết luận rằng bản xuất bị lỗi, xuất lại, và nhận đúng kết quả cũ — vì dữ liệu đang bị hỏng ngay trên máy của họ, sau khi tải xuống.

Bốn kiểu chuyển đổi gây ra hư hại

Số 0 ở đầu bị cắt bỏ. Mã bưu chính 01234, mã tỉnh của Pháp, số tài khoản, số điện thoại bắt đầu bằng 0 — tất cả trông như số, nên số 0 bị bỏ đi vì bị coi là vô nghĩa. Nó không vô nghĩa; nó là một phần của mã định danh.

Số dài bị làm tròn. Bảng tính lưu số dưới dạng dấu phẩy động với khoảng mười lăm chữ số có nghĩa, nên mã đơn hàng, số thẻ tín dụng, số IMEI hay mã định danh 64 bit đều âm thầm bị làm tròn — các chữ số cuối thành số 0. Ô trông như một con số, nhưng là con số sai.

Mọi thứ trông giống ngày tháng đều bị định dạng lại, theo quy ước của từng địa phương. 03/05 là tháng Ba ở nước này nhưng là tháng Năm ở nước khác, nên cùng một file mở ở hai văn phòng cho ra hai bộ dữ liệu khác nhau. Tệ hơn, cả những giá trị vốn không phải ngày tháng cũng bị chuyển đổi: tên gen như SEPT1 biến thành ngày tháng, một vấn đề dai dẳng đến mức các nhà di truyền học cuối cùng phải đổi tên gen thay vì tiếp tục chống chọi với nó.

Ký tự có dấu biến thành chữ lỗi font. CSV không lưu thông tin về bảng mã ký tự, nên một file lưu dạng UTF-8 nhưng bị đọc bằng bảng mã cũ — hoặc ngược lại — sẽ làm hỏng đúng những dòng có tên chứa dấu.

Cách khắc phục: nhập, đừng mở

Đừng bao giờ nhấn đúp vào file CSV quan trọng. Nhấn đúp là cho phép Excel đoán, và nó đoán rất tự tin.

Thay vào đó, hãy dùng Data → From Text/CSV (Dữ liệu → Từ Văn bản/CSV). Hộp thoại nhập cho phép bạn đặt kiểu dữ liệu cho từng cột trước khi phân tích bất cứ thứ gì — đánh dấu cột mã bưu chính và cột mã định danh là Text (Văn bản), và chúng sẽ giữ nguyên đúng như trong file. Nó cũng cho phép bạn chỉ định bảng mã và ký tự phân cách thay vì để Excel tự suy đoán. Chỉ mất hai mươi giây, và đó là toàn bộ giải pháp.

Google Sheets cũng vậy: File → Import (Tệp → Nhập), rồi tắt “Convert text to numbers, dates and formulas” (Chuyển văn bản thành số, ngày và công thức). LibreOffice Calc hiển thị hộp thoại chọn kiểu cột theo mặc định, một trong số ít điểm mà nó đơn giản là cư xử tốt hơn.

Kiểm tra trước khi nhập

Khi đã có sự cố, bước đầu tiên hữu ích là xem file mà không để bảng tính động vào. Xem file CSV hiển thị giá trị đúng như các byte ghi lại — số 0 ở đầu vẫn còn, số dài vẫn đầy đủ, ngày tháng vẫn là văn bản như lúc được viết. Nhờ đó bạn biết ngay lỗi nằm ở bản xuất hay ở lúc nhập.

Nó cũng cho bạn thấy hai điều mà file CSV không bao giờ tự khai báo: ký tự phân cách — dấu phẩy, dấu chấm phẩy hay tab; các vùng châu Âu xuất bằng dấu chấm phẩy vì dấu phẩy là dấu thập phân của họ — và bảng mã. Biết cả hai trước khi nhập sẽ loại bỏ phần lớn sự phỏng đoán còn lại.

Nếu bạn là người tạo ra file CSV

Một vài quyết định ở khâu xuất sẽ ngăn được toàn bộ những vấn đề này về sau.

Ghi UTF-8 kèm BOM (byte order mark) nếu file sẽ được mở bằng Excel trên Windows. Excel dựa vào dấu này để nhận biết UTF-8 và làm hỏng ký tự có dấu nếu thiếu nó — một trong số hiếm trường hợp thêm BOM là lựa chọn đúng chứ không phải phiền phức.

Đặt trong dấu ngoặc kép mọi trường có thể bị hiểu sai, và luôn làm vậy với các cột mã định danh. Dấu ngoặc kép không ngăn Excel chuyển đổi khi mở, nhưng nó làm rõ ý định cho mọi chương trình đọc khác.

Cân nhắc không dùng CSV. Nếu người nhận dùng được JSON hoặc một bảng tính thực sự, cả hai đều khai báo kiểu dữ liệu rõ ràng và không mắc lỗi nào kể trên. Ưu điểm của CSV là mọi thứ đều đọc được nó; nhược điểm là không có gì thống nhất được nó nói gì.