Excel sang CSV không lỗi dấu: UTF-8, BOM và dấu phân cách

Phần lớn lỗi dấu trong CSV đến từ việc lệch bảng mã: tệp được lưu dạng UTF-8 nhưng lại được mở bằng bảng mã Windows. Hãy xuất bảng tính sang CSV UTF-8, có kèm dấu thứ tự byte (BOM) nếu Excel là phần mềm sẽ mở tệp, và chọn dấu phân cách mà ngôn ngữ vùng của bạn quen dùng. Trước khi xuất, hãy định dạng các cột như mã bưu chính và số điện thoại thành văn bản để giữ lại số 0.

Chuyển Excel sang CSVCần CSV, nhưng có file ExcelDùng miễn phí

Vì sao "é" thành "é"

Một ký tự trong tệp được lưu dưới dạng các byte. Trong UTF-8, chữ é chiếm hai byte là C3 và A9. Một chương trình đọc các byte đó theo Windows-1252, một bảng mã phổ biến trên Windows, sẽ thấy C3 thành à và A9 thành ©. Vì vậy "café" hiển thị thành "café". Bạn có thể tự kiểm tra: mã hóa é theo UTF-8 rồi giải mã kết quả theo Windows-1252 sẽ ra é. Các chữ như ü, ñ hay ö cũng bị lỗi theo cách tương tự.

Cách sửa không phải là chỉnh sửa từng ký tự. Hãy cho chương trình biết tệp đang dùng bảng mã nào.

UTF-8 và BOM

UTF-8 là bảng mã tiêu chuẩn cho văn bản trên web và trong hầu hết phần mềm hiện đại. Một tệp CSV thuần túy không có trường nào cho biết nó dùng bảng mã nào, nên chương trình phải đoán. BOM là ba byte đặt ở đầu tệp, EF BB BF, báo cho các chương trình biết tìm kiếm rằng đây là UTF-8. Excel trên Windows thường mở tệp UTF-8 không có BOM bằng bảng mã địa phương, vì vậy thêm BOM là cách sửa phổ biến.

Trong các phiên bản Excel mới, menu Save As có tùy chọn CSV UTF-8 để thêm BOM. Bạn cũng có thể nhập tệp qua Data, From Text/CSV, rồi đặt File Origin thành 65001, tức UTF-8. BOM có một nhược điểm: chương trình không mong đợi nó có thể coi nó là một phần của tên cột đầu tiên. Hầu hết bộ phân tích cú pháp và phần mềm bảng tính xử lý được, nhưng nếu công cụ nhập dữ liệu báo tiêu đề lạ, hãy xóa BOM.

Dấu phẩy hay dấu chấm phẩy

CSV là viết tắt của comma-separated values, tức các giá trị được ngăn cách bằng dấu phẩy, và đặc tả RFC 4180 mô tả các trường được ngăn cách bằng dấu phẩy. Ở nhiều quốc gia, trong đó có những nơi dùng dấu phẩy thập phân như 1,5 cho một rưỡi, dấu phân cách danh sách của Excel là dấu chấm phẩy. Khi đó, một tệp ngăn cách bằng dấu phẩy mở trong Excel có thể bị tách sai, và một số như 1,5 bị biến thành hai trường.

Hãy chọn dấu phân cách mà chương trình đích yêu cầu. Công cụ Excel sang CSV hỗ trợ dấu phẩy, dấu chấm phẩy và tab. Nếu tệp dành cho chương trình dùng dấu phẩy thập phân, dấu chấm phẩy thường là lựa chọn an toàn hơn.

Ngày tháng thành số serial

Excel lưu ngày tháng dưới dạng số serial, đếm số ngày từ một mốc bắt đầu. Trong hệ thống ngày 1900, ngày 1 tháng 1 năm 1900 có số serial là 1. Excel còn coi năm 1900 là năm nhuận, nên nó đếm cả ngày 29 tháng 2 năm 1900 chưa từng tồn tại, và gán cho nó số serial 60. Nhờ vậy ngày 1 tháng 3 năm 1900 có số serial là 61 và làm lệch mọi ngày sau đó đi một ngày. Lỗi này xuất phát từ Lotus 1-2-3, và Microsoft giữ nguyên hệ thống này để đảm bảo tương thích. Các phần mềm khác có thể đọc ngày trước 1 tháng 3 năm 1900 lệch một ngày.

Khi xuất ngày, bạn có thể thấy 44927 thay vì 2023-01-01, hoặc những ngày cũ bị lệch một ngày. Công cụ chuyển ngày thành văn bản dễ đọc. Nếu bạn xuất thủ công, hãy định dạng cột ngày thành văn bản theo dạng ISO yyyy-mm-dd, mà mọi chương trình đều đọc giống nhau.

Số 0 đầu biến mất

Mã bưu chính như 02134 là văn bản, nhưng Excel coi số được gõ vào là số nên hiển thị 2134. Số 0 biến mất khỏi bảng tính, và tệp CSV xuất ra từ đó cũng sẽ không có nó. Số điện thoại bắt đầu bằng 0 và các mã số định danh dài cũng gặp lỗi tương tự. Số rất dài còn có thể mất chữ số, vì Excel chỉ giữ khoảng 15 chữ số có nghĩa.

Cách sửa là lưu các cột đó dưới dạng văn bản trước khi nhập hoặc nhập dữ liệu. Khi mở một tệp CSV đã có sẵn số 0, hãy dùng Data, From Text/CSV và đặt cột thành Text, thay vì nhấp đúp vào tệp.

Quy tắc đặt dấu ngoặc kép

RFC 4180 định nghĩa định dạng CSV phổ biến. Các quy tắc chính về dấu ngoặc kép gồm:

• Trường có chứa dấu phẩy, dấu ngoặc kép hoặc ngắt dòng phải được đặt trong dấu ngoặc kép.
• Dấu ngoặc kép bên trong trường đã có ngoặc kép được viết thành hai dấu ngoặc kép.
• Trong đặc tả, mỗi dòng kết thúc bằng CRLF, dù hầu hết công cụ đều chấp nhận LF.

Ví dụ, dòng "Müller, Anna","She said ""ok""" có hai trường: Müller, Anna và She said "ok". Kiểu MIME được IANA đăng ký cho CSV là text/csv.

RFC 4180 không cho phép dấu ngoặc kép xuất hiện trong trường không có ngoặc kép, và nhiều bộ phân tích cú pháp sẽ từ chối dòng như vậy hoặc tách sai. Hãy đặt trường trong ngoặc kép đúng cách, hoặc xóa ký tự thừa đó.

Danh sách kiểm tra trước khi xuất

1. Xuất dưới dạng CSV UTF-8, có BOM nếu Excel sẽ mở tệp.
2. Chọn dấu phân cách mà chương trình đích yêu cầu.
3. Định dạng các cột giống mã định danh, như mã bưu chính và số điện thoại, thành văn bản.
4. Ghi ngày theo dạng yyyy-mm-dd.
5. Mở tệp bằng trình soạn thảo văn bản thuần túy và kiểm tra dòng đầu cùng một vài tên có dấu.

Các ký tự ẩn như khoảng trắng không ngắt dòng cũng có thể gây lỗi khi nhập. Công cụ xóa ký tự ẩn tìm và xóa chúng, còn ký tự ẩn trong văn bản giải thích chúng xuất hiện từ đâu. Khi tệp đã sạch, CSV sang JSON có thể chuyển đổi tệp cho lập trình viên, như được mô tả trong CSV sang JSON và ngược lại.

Trước khi xuất, cũng nên xem lại chính các ô: công cụ kiểm tra công thức Excel liệt kê các công thức hiển thị lỗi hoặc trỏ đến tệp khác, và chính đó là những ô sẽ xuất hiện trống trong CSV. Sau khi xuất, hãy so sánh hai bảng tính để xem có gì thay đổi giữa các phiên bản.

Trả lời nhanh

Vì sao tệp của tôi hiển thị đúng ở một chương trình nhưng bị lỗi trong Excel? Khi tệp UTF-8 không có BOM, Excel trên Windows có thể đọc nó bằng bảng mã địa phương. Thêm BOM, hoặc nhập tệp với UTF-8 được chọn, sẽ khắc phục lỗi này.

Excel trên Mac có cần BOM không? Một số nguồn cho rằng có, vì vậy hãy thử trước bằng một tệp chứa tên có dấu trước khi gửi đi.

Tôi có thể sửa ngày sau khi xuất không? Có, nếu chúng vẫn là số serial, nhưng tốt hơn là xuất ngày theo dạng yyyy-mm-dd ngay từ đầu.

Bảng tính của tôi có được tải lên khi chuyển đổi không? Công cụ chạy trong trình duyệt của bạn, nên tệp được xử lý ngay trên thiết bị của chính bạn. Bạn có thể kiểm tra điều này bằng hướng dẫn kiểm tra việc tải tệp lên.