Bài viết
Tại sao Excel làm hỏng tệp CSV của bạn
Một mã bệnh nhân bắt đầu bằng ba số 0. Một gen tên SEPT2. Một số tài khoản mười sáu chữ số. Mở bất kỳ thứ nào trong số này trong bảng tính dưới dạng tệp CSV thuần và bộ nhập của Excel không chỉ hiển thị chúng, nó viết lại chúng: số 0 biến mất, tên gen thành ngày tháng, và số tài khoản mất vài chữ số cuối. Không điều nào trong số này thực sự là lỗi, đó là điều xảy ra khi một bộ đoán số-và-ngày-tháng đa dụng gặp phải văn bản chỉ trông giống một con số hoặc một ngày. Đây là điều thực sự xảy ra với tệp CSV ngay khi Excel mở nó, và cách ngăn chặn trước khi nó xảy ra.
Số 0 đứng đầu biến mất
Gõ 02134 vào một ô và kiểu cột mặc định của Excel, General, đọc nó như một con số và bỏ số 0 đứng đầu: 2134. Điều này ảnh hưởng đến bất kỳ mã nào được lưu dưới dạng chữ số nhưng thực chất không phải là một số lượng, mã ZIP của Mỹ, mã bưu chính Pháp, mã nhân viên hoặc hóa đơn, số điện thoại. Về mặt kỹ thuật không có gì mất đi khỏi tệp CSV bên dưới, văn bản cạnh ô vẫn không đổi, cho đến khi bạn lưu workbook, lúc đó Excel ghi lại bất cứ gì nó đã hiển thị, và số 0 mất vĩnh viễn. Cách khắc phục là báo cho Excel biết cột đó là văn bản trước khi nó có cơ hội đoán: qua Data > From Text/CSV (hoặc Get Data > From File) bạn có thể đặt rõ kiểu dữ liệu từng cột, hoặc thêm dấu nháy đơn trước một giá trị trong một ô riêng lẻ để ép thành văn bản.
Văn bản biến thành ngày tháng mà không hỏi ý kiến
Cùng bộ đoán đó cũng tìm văn bản có hình dạng ngày tháng. Một ô chứa MAR1 hay SEPT2, đọc như ngày 1 tháng 3 hay ngày 2 tháng 9, âm thầm bị chuyển thành giá trị ngày và định dạng lại, không có cảnh báo nào hiện ra. Đây không phải giả thuyết: năm 2020 Ủy ban Danh pháp Gen Người (HUGO) đã đổi tên khoảng 27 ký hiệu gen người, bao gồm SEPT2 và MARCH1, cụ thể là vì phần mềm bảng tính liên tục biến chúng thành ngày tháng mỗi khi một tập dữ liệu được mở, một vấn đề nghiêm trọng đến mức một khảo sát năm 2016 về các bài báo hệ gen học phát hiện lỗi tự động sửa kiểu này trong khoảng một phần năm số ấn phẩm có kèm tệp Excel bổ sung. Bài học này áp dụng rộng hơn tên gen: bất kỳ mã chữ-số ngắn nào tình cờ trông giống một ngày và một tháng đều gặp rủi ro ngay khi ai đó mở tệp CSV của bạn trong bảng tính, không chỉ tệp của riêng bạn.
Số rất dài mất các chữ số cuối
Excel lưu mọi con số dưới dạng giá trị dấu phẩy động với 15 chữ số có nghĩa, một giới hạn Microsoft ghi rõ trực tiếp. Một số thẻ hoặc tài khoản mười sáu chữ số như 4111111111111111 bị làm tròn cho vừa, nên một hoặc hai chữ số cuối âm thầm thành số 0 và giá trị không còn là giá trị bạn đã gõ. Định dạng lại ô sau đó không đưa các chữ số bị mất trở lại, vì con số lưu trữ bên dưới đã bị làm tròn; cách khắc phục duy nhất là nhập cột dưới dạng văn bản, hoặc thêm dấu nháy đơn trước giá trị, trước khi Excel từng đối xử với nó như một con số.
Văn bản có dấu biến thành ký tự lỗi
Mã hóa ký tự là một kiểu lỗi khác, tách biệt với việc tự động sửa. Một tệp CSV không có byte-order mark chỉ là các byte thuần; nếu nó được lưu dưới dạng UTF-8, Excel trên Windows thường sẽ mở nó với giả định dùng bảng mã (codepage) riêng của hệ thống thay vào đó, nên các chữ có dấu, ký hiệu tiền tệ và emoji ra thành ký tự lỗi (mojibake), một chuỗi như café hiện ra thành thứ gì đó gần với café. Cách an toàn khi tạo một CSV cho Excel là lưu nó dưới dạng CSV UTF-8 hoặc thêm byte-order mark UTF-8; cách an toàn khi dùng một tệp như vậy là nhập qua Data > From Text/CSV, cho phép bạn chọn rõ mã hóa nguồn thay vì tin vào việc nhấp đúp đoán đúng.
Di chuyển dữ liệu giữa các định dạng mà không mở nó trong Excel
Không điều nào trong số này thực sự là lỗi của Excel, đó là điều mà bộ đoán số-và-ngày-tháng của một bảng tính được xây dựng để làm, và nó áp dụng bất kể tệp được mở thế nào, không chỉ trong Excel. Cách an toàn nhất để định dạng lại một tệp CSV hoặc kiểm tra xem một cột có thực sự giữ nguyên dạng văn bản trước khi nó tới bất kỳ bảng tính nào là xử lý nó trực tiếp: bộ chuyển đổi dữ liệu của chúng tôi phân tích CSV bằng một trình phân tích chuẩn có nhận biết dấu ngoặc kép và không bao giờ diễn giải lại kiểu của một giá trị, nên một mã ZIP hay tên gen vẫn y hệt văn bản bạn đưa vào. Nó chạy hoàn toàn trong trình duyệt của bạn, không gì bạn dán vào bị tải lên đâu cả. Tuy nhiên điều đó chỉ bảo vệ bước chuyển đổi: nếu đầu ra vẫn sẽ được mở trong một bảng tính sau đó, hãy đánh dấu các cột nhạy cảm là văn bản ở đó luôn.
Công cụ trong bài viết này
Câu hỏi thường gặp
Làm sao để ngăn Excel xóa số 0 đứng đầu khỏi CSV?
Đừng mở tệp bằng cách nhấp đúp vào nó. Thay vào đó, hãy nhập qua Data > From Text/CSV (hoặc Get Data), việc này hiển thị bộ chọn kiểu dữ liệu theo từng cột; đặt cột mã ZIP, ID hay số điện thoại thành Text trước khi hoàn tất việc nhập. Một cách sửa nhanh theo từng ô là gõ dấu nháy đơn trước giá trị, như '02134, việc này ép thành văn bản mà không đổi những gì hiển thị.
Tại sao Excel biến ID hay mã số của tôi thành ngày tháng?
Kiểu cột General của Excel chủ động tìm văn bản có hình dạng ngày tháng, các mẫu như một từ ngắn cộng một con số, hoặc hai con số ngăn cách bởi gạch ngang hay gạch chéo, và chuyển đổi bất cứ gì khớp, không hỏi xác nhận. Đây chính xác là điều đã buộc phải đổi tên các ký hiệu gen như SEPT2 và MARCH1 vào năm 2020. Nhập cột dưới dạng Text, giống như với số 0 đứng đầu, để ngăn việc đoán trước khi nó xảy ra.
Chuyển đổi CSV của tôi theo cách khác có tránh hoàn toàn những vấn đề này không?
Nó tránh được cho bước chuyển đổi đó, không phải mãi mãi. Một trình phân tích coi mọi trường là văn bản thuần, như bộ chuyển đổi dữ liệu của chúng tôi, sẽ không viết lại mã ZIP hay tên gen khi biến CSV thành JSON. Nhưng nếu đầu ra vẫn cuối cùng được mở trong bảng tính sau đó, bởi bạn hoặc bởi ai đó nhận nó, bộ nhập riêng của chương trình đó lại có một cơ hội khác để diễn giải lại các giá trị, nên các cột nhạy cảm cần được đánh dấu là văn bản ở đó luôn.