Làm sạch file CSV bị lộn xộn: các lỗi cần kiểm tra theo thứ tự

Để làm sạch một file CSV bị lộn xộn, hãy sửa theo từng lớp: ký tự trước, sau đó là bố cục, cuối cùng là ý nghĩa của từng cột. CSV là văn bản thuần túy, nên phần lớn lỗi là ký tự sai, dòng hoặc dấu phân cách thừa, hoặc các giá trị mà chương trình đọc khác với cách bạn hiểu. Hãy sửa ký tự trước. Một khoảng trắng ẩn có thể khiến ngày không được nhận là ngày, và các bước sửa sau đó sẽ thất bại mà không rõ lý do.

Làm sạch CSVFile CSV lộn xộn, có ô trống và dòng trùngDùng miễn phí

Ký tự trông đúng nhưng thực ra là sai

Khoảng trắng thừa. Khoảng trắng ở đầu hoặc cuối ô, hay hai khoảng trắng liền nhau giữa một giá trị, khiến "Berlin " và "Berlin" thành hai giá trị khác nhau đối với tra cứu, bộ lọc hay phép nối.

Ký tự ẩn. Một số ký tự không chiếm chỗ trên màn hình:

• Khoảng trắng không ngắt (U+00A0) trông giống khoảng trắng thường nhưng là ký tự khác. Trang web dùng nó để giữ các từ đứng cạnh nhau.
• Khoảng trắng không độ rộng (U+200B) không có chiều rộng nào.
• Dấu đánh thứ tự byte (U+FEFF) có thể nằm ngay đầu file, trước dòng tiêu đề đầu tiên. Cột trông như "id" nhưng thực tế là "id", nên tra cứu "id" sẽ thất bại.

Xóa ký tự ẩn hiển thị những gì nó tìm thấy trước khi thay đổi bất cứ thứ gì.

Chữ bị lỗi. "é" khi bạn mong đợi "é" là lỗi mã hóa, gọi là mojibake. File được lưu dưới dạng UTF-8, nơi é chiếm hai byte, rồi lại được đọc theo Windows-1252, nơi hai byte đó thành hai chữ cái không liên quan. Dấu nháy "’" cũng bị biến thành "’" theo cách tương tự. Cách sửa là đọc file dưới dạng UTF-8, hoặc xuất lại file từ nguồn với UTF-8 được chọn. Sửa văn bản đã được lưu sai rất dễ hỏng, nên hãy giữ bản gốc và làm việc trên một bản sao.

Bố cục: dòng tiêu đề, dòng trống và dòng trùng

Báo cáo thường bắt đầu bằng một tiêu đề, một ngày và một dòng trống phía trên tiêu đề thật. Hãy xóa mọi thứ nằm phía trên dòng tiêu đề, để dòng đầu tiên chứa tên các cột. Mỗi cột cần có một tên, và không tên nào được để trống.

Mỗi dòng nên có cùng số trường. Một dấu phẩy thừa bên trong giá trị không có ngoặc kép sẽ thêm một trường, và mọi giá trị sau đó sẽ dịch sang phải một cột. Hãy đếm số trường trên mỗi dòng. Dòng nào có số lượng khác là dòng bị lỗi.

Dòng trùng có hai loại. Các dòng giống hệt nhau ở mọi cột thường do xuất hoặc dán dữ liệu hai lần, và việc xóa chúng là an toàn. Các dòng có cùng khóa nhưng khác nhau ở các cột khác có thể là bản cập nhật. Xóa nhầm có thể làm mất phiên bản mới nhất, nên hãy kiểm tra trước.

Ngày: 03/04/2026 có thể là hai ngày khác nhau

03/04/2026 là ngày 3 tháng 4 ở quốc gia này nhưng là ngày 4 tháng 3 ở quốc gia khác, và văn bản không cho biết đó là cách nào. Hãy xem các ngày khác trong cùng cột. Nếu có số đầu tiên nào lớn hơn 12, cột đó theo thứ tự ngày trước. Nếu có số thứ hai lớn hơn 12, cột đó theo thứ tự tháng trước. Nếu mọi giá trị đều có cả hai số từ 12 trở xuống, file không thể cho bạn biết, và bạn nên hỏi hệ thống nguồn. Kiểm tra kiểu dữ liệu đánh dấu cột như vậy là không rõ ràng thay vì đoán.

Khi đã biết thứ tự, hãy chuyển mọi ngày sang ISO 8601, dạng năm-tháng-ngày như 2026-04-03. Dạng này không gây nhầm lẫn, và sắp xếp đúng khi xem như văn bản. Hãy chuyển các định dạng hỗn hợp từng cái một, vì một cột có cả hai kiểu sẽ được hầu hết các chương trình đọc không nhất quán.

Số được lưu dưới dạng văn bản

Một số bị lưu thành văn bản khi nó có ký hiệu tiền tệ, dấu phần trăm hoặc khoảng trắng bên trong, chẳng hạn "€ 12", "45%" hoặc "1 234" với khoảng trắng không ngắt. Dấu phân cách hàng nghìn, như trong "1,234.50", thường khiến chương trình cũng đọc giá trị là văn bản. Bảng tính hiển thị số dạng văn bản căn lề trái, và các phép cộng bỏ qua chúng.

Dấu phẩy thập phân là cái bẫy. Trong "1.234,50", dấu phẩy là dấu thập phân, còn dấu chấm nhóm hàng nghìn. Trong "1,234.50" thì ngược lại. Vì vậy "1,234" có thể là 1234 trong file xuất này và là 1.234 trong file xuất khác. Hãy xác định locale của nguồn trước khi chuyển đổi, rồi xóa dấu phân cách hàng nghìn và đổi dấu phẩy thập phân thành dấu chấm.

Mất số 0 đứng đầu

Mã bưu chính, mã sản phẩm và số tài khoản thường bắt đầu bằng số 0. Khi một file CSV mở trong bảng tính, "01234" được đọc thành số 1234, và các số 0 bị mất. Việc lưu file không khôi phục được sự mất mát này, nên hệ thống nguồn là bản sao duy nhất đáng tin cậy.

Một mã định danh là văn bản, ngay cả khi trông giống số, vì bạn không bao giờ thực hiện phép tính với nó. Hãy tắt chuyển đổi số cho các cột như vậy khi bạn làm sạch CSV, và mở lại file từ nguồn thay vì từ một bản sao mà bảng tính đã lưu. Sau đó kiểm tra kiểu dữ liệu của từng cột, để cột mã bưu chính không âm thầm chuyển lại thành số. Excel cũng có thể thay đổi giá trị khi mở file CSV, nên hãy kiểm tra kết quả ở chế độ xem văn bản thuần túy.

Kiểm tra kiểu dữ liệu, rồi phân tích

Sau khi làm sạch, hãy kiểm tra kiểu của từng cột. Chỉ một ô "N/A" trong cột giá sẽ làm cả cột thành văn bản, và tổng của nó sẽ sai. Kiểm tra kiểu dữ liệu gán cho mỗi cột một kiểu và một điểm tin cậy, đồng thời liệt kê các ô không khớp.

Tiếp theo, hãy phân tích các cột. Trình phân tích cột CSV hiển thị các khoảng trống và giá trị bất thường trong từng cột. Một giá trị bất thường có thể là giá trị thật, hoặc là lỗi như một giá tăng gấp một nghìn lần so với các giá khác, thường do dấu thập phân hoặc dấu phân cách bị đặt sai chỗ.

Các trường hợp đặc biệt

• Ngắt dòng trong ngoặc kép. Một giá trị có ngoặc kép có thể chứa ký tự ngắt dòng. Công cụ tách file theo dòng sẽ chia dòng đó thành hai, nên hãy dùng trình đọc hiểu các trường có ngoặc kép.
• File dùng dấu chấm phẩy. Nhiều bảng tính châu Âu lưu CSV với dấu chấm phẩy, vì dấu phẩy là dấu thập phân trong locale của họ. Hãy kiểm tra dấu phân cách trước khi đọc các cột.
• Giữ bản gốc. Ghi kết quả đã làm sạch ra một file mới, và giữ file xuất như ban đầu.

Thứ tự, dưới dạng danh sách kiểm tra

1. Giữ một bản sao của file xuất gốc.
2. Xóa khoảng trắng thừa và ký tự ẩn. Sửa chữ bị lỗi ở nguồn nếu có thể.
3. Xóa các dòng tiêu đề, kiểm tra tên cột, và xác nhận mọi dòng có cùng số trường.
4. Tìm các dòng trùng và quyết định phiên bản nào sẽ giữ lại.
5. Chuyển ngày sang dạng năm-tháng-ngày, xóa dấu phân cách, và đảm bảo mã định danh là văn bản.
6. Kiểm tra kiểu của từng cột, rồi phân tích để tìm khoảng trống và giá trị bất thường.

Câu trả lời nhanh

Vì sao tra cứu của tôi thất bại khi các giá trị trông giống hệt nhau?

Thường là một khoảng trắng, khoảng trắng không ngắt hoặc dấu đánh thứ tự byte nằm trong một trong các ô. Hãy xóa ký tự ẩn rồi thử lại.

Vì sao ngày của tôi bị đổi thành thứ khác?

Một chương trình đã đọc ngày theo thứ tự ngày-tháng hoặc tháng-ngày sai, hoặc đã chuyển văn bản thành định dạng ngày của riêng nó. Hãy tự chuyển ngày sang năm-tháng-ngày trước.