Excel to CSV Conversion: Common Pitfalls and How to Avoid Them - CSV-X.com

Tôi vẫn nhớ ngày tôi khiến công ty của mình mất 47.000 USD chỉ vì một xuất CSV từ Excel có vẻ vô hại. Đó là năm 2016, và tôi đã làm việc được ba năm trong vai trò chuyên gia tích hợp dữ liệu tại một công ty dịch vụ tài chính vừa và nhỏ. Chúng tôi đang chuyển dữ liệu khách hàng sang một hệ thống CRM mới, và tôi được giao nhiệm vụ chuẩn bị 180.000 hồ sơ khách hàng để nhập khẩu. Tệp Excel trông rất hoàn hảo. Tệp CSV mà tôi xuất ra cũng trông hoàn hảo khi tôi mở nó trong Notepad. Nhưng khi quá trình nhập diễn ra vào lúc 3 giờ sáng thứ Bảy, nó đã làm hỏng 23% số điện thoại khách hàng của chúng tôi, hợp nhất các trường địa chỉ theo cách vô lý và biến các định dạng ngày tháng được chúng tôi duy trì cẩn thận thành một mớ hỗn độn không thể nhận diện.

Quá trình khôi phục mất hai tuần, bao gồm việc xác minh thủ công hàng nghìn hồ sơ, và yêu cầu chúng tôi phải hoãn ra mắt CRM của mình một tháng. Kinh nghiệm đó đã biến tôi từ một người nghĩ rằng "Lưu Dưới Dạng CSV" chỉ là một cú nhấp chuột đơn giản thành người bị ám ảnh với việc hiểu từng sắc thái trong chuyển đổi từ Excel sang CSV. Trong suốt mười một năm qua, tôi đã giúp hơn 40 công ty tránh những thảm họa tương tự và tôi đã chứng kiến hầu như mọi cách mà quá trình này có thể sai lệch.

Dưới đây là điều mà hầu hết mọi người không nhận ra: Excel và tệp CSV là hai thực thể hoàn toàn khác biệt, và chức năng xuất CSV của Excel đưa ra hàng chục giả định có thể làm hỏng dữ liệu của bạn một cách âm thầm. Tôi sẽ hướng dẫn bạn qua bảy cạm bẫy phổ biến nhất mà tôi đã gặp phải và cung cấp cho bạn các chiến lược đã được thử nghiệm để tránh chúng.

Cạm Bẫy Mã Hóa Ký Tự: Khi Dữ Liệu Của Bạn Nói Một Ngôn Ngữ Sai

Mã hóa ký tự là kẻ giết người âm thầm trong các chuyển đổi CSV. Trong thực tiễn tư vấn của tôi, tôi ước tính rằng 60% các vấn đề "CSV bị hỏng" mà tôi điều tra đều xuất phát từ các vấn đề mã hóa. Dưới đây là lý do tại sao điều này quan trọng: Excel thường lưu các tệp CSV trong mã hóa mặc định của hệ thống bạn, mà trong Windows thường là Windows-1252 hoặc ANSI. Nhưng hầu hết các ứng dụng web hiện đại, cơ sở dữ liệu và công cụ xử lý dữ liệu đều mong đợi mã hóa UTF-8.

Triệu chứng thì không thể nhầm lẫn một khi bạn biết điều gì cần tìm. Tên khách hàng như "José García" trở thành "José García". Ký hiệu tiền tệ biến thành dấu hỏi hoặc hình vuông. Các ký tự có dấu trong các ngôn ngữ châu Âu biến thành gibberish. Tôi từng làm việc với một nhà cung cấp dịch vụ chăm sóc sức khỏe mà hồ sơ bệnh nhân của họ bao gồm tên từ 47 quốc gia khác nhau. Khi họ xuất sang CSV bằng cách sử dụng cài đặt mặc định của Excel, khoảng 8.000 trong số 95.000 tên bệnh nhân của họ chứa ký tự bị hỏng.

Việc khắc phục yêu cầu hiểu rằng tùy chọn "Lưu Dưới Dạng CSV" của Excel không cho bạn quyền kiểm soát mã hóa. Thay vào đó, bạn cần sử dụng "Lưu Dưới Dạng" và chọn "CSV UTF-8 (Dấu phẩy phân cách)" từ menu loại tệp. Tùy chọn này đã được thêm vào trong Excel 2016, và nó là một . Đối với các phiên bản Excel trước đó, bạn sẽ cần sử dụng một giải pháp thay thế: lưu dưới dạng Văn bản Unicode, sau đó sử dụng một trình chỉnh sửa văn bản hoặc ngôn ngữ lập trình để chuyển đổi sang định dạng CSV UTF-8 phù hợp.

Nhưng đây là vấn đề khiến cả những người dùng có kinh nghiệm cũng gặp khó khăn: Tùy chọn CSV UTF-8 của Excel bao gồm một BOM (Byte Order Mark) ở đầu tệp. Trong khi điều này giúp một số ứng dụng nhận biết mã hóa, nó gây ra vấn đề với những ứng dụng khác. Tôi đã thấy các hệ thống dựa trên Unix gặp khó khăn với các tệp có BOM, coi ba byte đầu tiên đó là dữ liệu thực tế. Nếu bạn đang làm việc với các hệ thống không xử lý BOM tốt, bạn sẽ cần loại bỏ nó bằng cách sử dụng một trình chỉnh sửa văn bản hỗ trợ thao tác mã hóa, hoặc sử dụng một đoạn mã đơn giản.

Đề xuất của tôi: luôn kiểm tra việc nhập CSV của bạn với một tệp mẫu nhỏ trước. Nhập 100 hồ sơ, xác minh rằng các ký tự đặc biệt hiển thị chính xác, sau đó tiếp tục với tập dữ liệu đầy đủ. Bài kiểm tra năm phút này đã giúp khách hàng của tôi tiết kiệm vô số giờ công việc dọn dẹp.

Thảm Sát Số Không Dẫn Đầu: Khi Excel Quyết Định Dữ Liệu Của Bạn Nên Thế Nào

Việc Excel diễn giải kiểu dữ liệu một cách hung hăng có lẽ đã phá hủy nhiều tính toàn vẹn dữ liệu hơn bất kỳ tính năng đơn lẻ nào khác. Vấn đề này thì đơn giản nhưng tinh vi: Excel nhìn vào dữ liệu của bạn và quyết định kiểu dữ liệu mà nó nên có, thường chuyển đổi những thứ bạn muốn thành văn bản thành số. Nạn nhân phổ biến nhất? Số không dẫn đầu.

"Nút 'Lưu Dưới Dạng CSV' của Excel không phải là công cụ xuất dữ liệu—đó là một mìn biến đổi dữ liệu khiến cho những giả định im lặng về mã hóa, ký tự phân cách và định dạng có thể làm hỏng hàng ngàn hồ sơ trong tích tắc."

Tôi đã làm việc với một công ty viễn thông có cơ sở dữ liệu gồm 340.000 số điện thoại. Nhiều số trong số này bắt đầu bằng số không, như thường thấy trong các mã quay số quốc tế và một số định dạng khu vực. Khi họ xuất bảng tính Excel của mình sang CSV, mọi số không dẫn đầu đều biến mất. Số điện thoại như "0412345678" trở thành "412345678". Mã ZIP như "02134" trở thành "2134". Mã sản phẩm như "00456-B" trở thành "456-B".

Ảnh hưởng tài chính là rất đáng kể. Trung tâm cuộc gọi của họ không thể liên lạc với 18% khách hàng của họ vì các số điện thoại không đầy đủ. Họ đã phải so sánh với các hệ thống sao lưu, tái cấu trúc dữ liệu một cách thủ công và thực hiện các quy trình xác minh mới. Dự án tiêu tốn 200 giờ công việc và đã làm chậm một chiến dịch marketing lớn.

Dưới đây là điều gì đang xảy ra phía sau: khi bạn mở một tệp CSV trong Excel, nó tự động diễn giải dữ liệu. Các số bắt đầu bằng số không sẽ được chuyển đổi sang định dạng số, từ đó làm mất các số không dẫn đầu. Khi bạn lưu trở lại dưới dạng CSV, những số không đó sẽ biến mất mãi mãi. Cùng điều đó xảy ra với các chuỗi số dài như số thẻ tín dụng hoặc ID tài khoản—Excel chuyển đổi chúng thành ký hiệu khoa học (1.23E+15), và bạn mất đi độ chính xác.

Giải pháp yêu cầu một cách tiếp cận đa hướng. Đầu tiên, nếu bạn đang tạo dữ liệu trong Excel sẽ được xuất sang CSV, hãy định dạng các cột đó thành Văn bản trước khi nhập dữ liệu. Nhấp chuột phải vào cột, chọn Định dạng Ô, và chọn Văn bản. Điều này cho Excel biết rằng hãy coi mọi thứ như văn bản nguyên văn, giữ lại các số không dẫn đầu và ngăn chặn ký hiệu khoa học.

Thứ hai, nếu bạn mở một tệp CSV hiện có trong Excel để thực hiện chỉnh sửa, đừng chỉ nhấp đúp vào nó. Thay vào đó, hãy mở Excel trước, sau đó sử dụng trình trợ lý nhập "Từ Văn bản/CSV" trong tab Dữ liệu. Điều này cho bạn kiểm soát cách mà mỗi cột được diễn giải. Bạn có thể chỉ định rằng một số cột nên được xử lý như văn bản, duy trì định dạng ban đầu của chúng.

Thứ ba, hãy cân nhắc liệu bạn có thực sự cần mở CSV trong Excel hay không. Đối với các chỉnh sửa đơn giản, một trình chỉnh sửa văn bản có thể an toàn hơn. Đối với các chuyển đổi phức tạp, một ngôn ngữ lập trình như Python hoặc một trình chỉnh sửa CSV chuyên dụng sẽ cung cấp cho bạn nhiều quyền kiểm soát hơn mà không có sự "hỗ trợ" của Excel trong việc tự động chuyển đổi.

Nỗi Dilemma Về Ký Tự Phân Cách: Khi Dấu Phẩy Không Phải Là Dấu Phẩy

"C" trong CSV là viết tắt của "dấu phẩy," nhưng đây là một bí mật gây ra sự nhầm lẫn vô tận: Excel không phải lúc nào cũng sử dụng dấu phẩy làm ký tự phân cách khi lưu tệp CSV. Thay vào đó, nó sử dụng cài đặt phân cách danh sách của hệ thống bạn, có sự khác biệt theo khu vực. Tại Hoa Kỳ, đó là dấu phẩy. Ở nhiều nước châu Âu, đó là dấu chấm phẩy. Ở một số khu vực, đó là ký tự tab.

Tôi đã khám phá điều này theo cách khó khăn khi tư vấn cho một tập đoàn đa quốc gia có văn phòng ở 12 quốc gia. Văn phòng của họ ở Đức đã xuất một tệp CSV mà văn phòng của họ ở Mỹ không thể nhập chính xác. Tệp đã mở trong Excel khá ổn, nhưng khi nhập vào hệ thống cơ sở dữ liệu của họ, mọi hàng đều trở thành một trường duy nhất. Vấn đề ở đây? Hệ thống Đức sử dụng dấu chấm phẩy làm ký tự phân cách, nhưng công cụ nhập của Mỹ mong đợi dấu phẩy.

Vấn đề này ảnh hưởng đến khoảng 30% các chuyển giao dữ liệu quốc tế mà tôi đã làm việc. Triệu chứng thì khác nhau: đôi khi việc nhập hoàn toàn thất bại, đôi khi nó thành công nhưng đưa toàn bộ dữ liệu vào cột đầu tiên, và đôi khi nó tạo ra những chia sẻ trường lạ lùng khi dấu phẩy trong dữ liệu được hiểu là ký tự phân cách.

Nguyên nhân gốc rễ là việc xuất CSV của Excel sử dụng danh sách phân cách cài đặt khu vực của Windows. Bạn có thể kiểm tra của mình bằng cách vào Bảng điều khiển > Khu vực > Cài đặt bổ sung. Nhưng việc thay đổi cài đặt này trên toàn hệ thống ảnh hưởng đến các ứng dụng khác và không phải là giải pháp thực tiễn cho hầu hết người dùng.