Cách chuyển đổi XLSX sang CSV (và tránh các vấn đề đau đầu về encoding)
Tại sao chuyển đổi từ XLSX sang CSV hay gặp lỗi
Trông chúng có vẻ giống nhau. XLSX và CSV đều là bảng dữ liệu, phải không? Nhưng bên dưới, chúng là hai loài hoàn toàn khác biệt. Một file XLSX là một gói phức tạp—một file zip chứa các tài liệu XML lưu trữ nhiều trang tính, định dạng ô phong phú, công thức, và thậm chí cả hình ảnh. Còn một file CSV chỉ đơn thuần là văn bản. Các hàng kết thúc bằng một dòng mới, các cột được phân tách bằng một ký tự như dấu phẩy. Khi bạn làm phẳng cấu trúc XLSX phong phú đó thành văn bản thuần túy, bạn đang mời gọi rắc rối. Vấn đề đau đầu nhất đến từ việc không khớp encoding (mã hóa ký tự). Nếu bảng tính của bạn chứa bất kỳ ký tự đặc biệt nào—chữ có dấu như é hay ñ, ký hiệu tiền tệ như €, hoặc văn bản không phải chữ Latin—file CSV đầu ra bắt buộc phải sử dụng đúng encoding, và đó gần như luôn là UTF-8. Nhiều công cụ mặc định sử dụng các encoding cũ hơn như Windows-1252, sẽ làm hỏng các ký tự đó thành dấu chấm hỏi. Tên khách hàng như 'José García' sẽ trở thành 'Jos? Garc?a', và việc nhập dữ liệu vào CRM của bạn sẽ thất bại. Xung đột ký tự phân cách (delimiter) là một vấn đề kinh điển khác. Nếu file CSV của bạn dùng dấu phẩy để tách các cột, điều gì sẽ xảy ra với một ô chứa dấu phẩy, như địa chỉ '14 Oak Street, Suite 200'? Một trình ghi CSV chuẩn sẽ bao bọc giá trị đó trong dấu ngoặc kép. Nhưng nếu chính dữ liệu lại chứa dấu ngoặc kép, chúng phải được thoát bằng cách nhân đôi lên ('He said "hello"' trở thành '"He said ""hello"""'). Biết trước những 'bãi mìn' này ở đâu trước khi bắt đầu chuyển đổi sẽ giúp bạn tiết kiệm hàng giờ gỡ lỗi đau đớn.
Quyết định về Encoding: UTF-8, UTF-8 BOM, hay loại khác
Trước khi bạn chuyển đổi bất cứ thứ gì, hãy đưa ra một quyết định quan trọng: hệ thống đích của bạn cần encoding nào? Làm đúng điều này, và bạn sẽ tránh được phần lớn các vấn đề chuyển đổi. Đối với gần như mọi hệ thống hiện đại, UTF-8 là câu trả lời. Chấm hết. Cơ sở dữ liệu, script Python, API web—tất cả đều mong đợi và xử lý UTF-8 một cách hoàn hảo. Nếu bạn đang đưa file CSV vào pandas bằng `pd.read_csv()`, UTF-8 là mặc định và nó cứ thế hoạt động. Điều tương tự cũng áp dụng cho lệnh `COPY` của PostgreSQL hay `LOAD DATA INFILE` của MySQL khi bạn chỉ định `CHARACTER SET utf8mb4`. Sau đó là UTF-8 với BOM (Byte Order Mark). Biến thể này thêm ba byte vô hình (0xEF, 0xBB, 0xBF) vào đầu file. Tại sao nó tồn tại? Để giúp Microsoft Excel. Trên Windows, Excel thường mặc định một file CSV được mã hóa bằng Windows-1252 trừ khi nó thấy BOM này, làm sai lệch bất kỳ ký tự đặc biệt nào. Vì vậy, đây là quy tắc đơn giản: nếu người dùng sẽ mở file CSV trực tiếp bằng Excel, hãy dùng UTF-8 BOM. Nếu một script hay chương trình sẽ đọc nó, hãy dùng UTF-8 thường, vì BOM đó có thể làm nghẽn nhiều trình phân tích cú pháp (parser). Các encoding cũ hơn như Windows-1252 (hoặc ANSI) và ISO-8859-1 (Latin-1) là những di vật của quá khứ. Chúng chỉ hỗ trợ các ký tự Tây Âu và sẽ làm hỏng bất kỳ dữ liệu nào từ các ngôn ngữ khác. Bạn có thể gặp chúng với các hệ thống cũ, nhưng bạn không bao giờ nên chọn chúng cho một quy trình làm việc mới. [Công cụ chuyển đổi XLSX sang CSV](/convert/xlsx-to-csv) của CocoConvert cho phép bạn chọn encoding ngay từ đầu, một tính năng quan trọng mà nhiều công cụ miễn phí lại bỏ qua một cách khó hiểu. Chỉ cần chọn UTF-8 cho hệ thống và UTF-8 BOM cho Excel.
Từng bước: Chuyển đổi XLSX sang CSV với CocoConvert
Quy trình rất đơn giản, nhưng chi tiết nhỏ mới là mấu chốt. Một vài cài đặt ở đây sẽ quyết định thành bại của file đầu ra cuối cùng. 1. Truy cập [trang chuyển đổi XLSX sang CSV của CocoConvert](/convert/xlsx-to-csv) và tải lên file của bạn. Bạn có thể xử lý các file lên đến 50 MB mà không cần tài khoản, và các file lớn hơn chỉ cần đăng ký miễn phí. Nếu file XLSX của bạn là một con 'quái vật' thực sự—một số file xuất từ doanh nghiệp có thể lên tới hàng trăm megabyte—bạn sẽ cần sử dụng một công cụ trên máy tính như LibreOffice Calc hoặc một script, chúng ta sẽ đề cập sau. 2. Chọn trang tính để xuất. Một file XLSX có thể chứa nhiều trang tính, nhưng một file CSV chỉ có thể đại diện cho một. CocoConvert liệt kê tất cả các trang tính từ file của bạn trong một danh sách thả xuống. Nếu bạn cần xuất tất cả các trang tính, bạn phải chuyển đổi chúng lần lượt. 3. Chọn ký tự phân cách (delimiter). Dấu phẩy là tiêu chuẩn, nhưng nó là một lựa chọn tồi tệ nếu dữ liệu của bạn chứa đầy chúng (hãy nghĩ đến các mô tả văn bản tự do hoặc địa chỉ). Ở châu Âu, dấu chấm phẩy là một lựa chọn thay thế phổ biến và thường an toàn hơn. Phân tách bằng tab (TSV) là một lựa chọn tuyệt vời khác nếu văn bản của bạn chứa cả dấu phẩy và dấu chấm phẩy. 4. Thiết lập encoding. Bạn đã biết quy tắc: UTF-8 cho mục đích lập trình, UTF-8 BOM nếu file dành cho người dùng Excel. 5. Quyết định cách xử lý công thức. CocoConvert xuất ra *giá trị* đã được tính toán của một ô, chứ không phải bản thân công thức. Một ô có công thức `=SUM(A1:A10)` hiển thị `4500` sẽ trở thành `4500` trong file CSV. Đây chính xác là điều bạn muốn trong 99% trường hợp. Nếu bạn thực sự cần văn bản công thức, thì ngay từ đầu bạn không nên chuyển đổi sang CSV. 6. Tải xuống và xác minh. **Đừng mở file CSV bằng Excel để kiểm tra.** Tin tôi đi. Hãy mở nó trong một trình soạn thảo văn bản thực thụ trước, như Notepad++ trên Windows hoặc BBEdit trên Mac. Những công cụ này cho bạn thấy văn bản thô và cho phép bạn xác nhận encoding là chính xác. Tìm một hàng có các ký tự đặc biệt và đảm bảo chúng trông đúng trước khi bạn nghĩ đến việc nhập nó vào hệ thống đích của mình.
Thực hiện trên Excel và LibreOffice (Khi bạn cần kiểm soát cục bộ)
Đôi khi bạn phải chuyển đổi cục bộ. Có thể file chứa dữ liệu nhạy cảm không thể tải lên, hoặc nó là một file khổng lồ 200 MB làm nghẽn các công cụ trực tuyến. Cả Excel và LibreOffice đều có thể hoàn thành công việc, nhưng mỗi cái đều có những điểm kỳ quặc riêng. Trong Microsoft Excel (Office 365 / Excel 2019+): Mở file XLSX của bạn, điều hướng đến File → Save As, và trong danh sách thả xuống 'Save as type', chọn 'CSV UTF-8 (Comma delimited) (*.csv)'. Tùy chọn này, mà Microsoft *cuối cùng* cũng đã thêm vào trong Excel 2016, sẽ lưu chính xác với encoding UTF-8 BOM. Hãy cẩn thận không chọn tùy chọn 'CSV (Comma delimited)' thông thường; nó lưu bằng encoding Windows-1252 cổ lỗ, một cái bẫy đã khiến không biết bao nhiêu lập trình viên mắc phải. Excel sẽ cảnh báo bạn rằng chỉ có trang tính đang hoạt động được lưu. Nhấp OK và lặp lại cho bất kỳ trang tính nào khác bạn cần. Trong LibreOffice Calc (một công cụ đa nền tảng, miễn phí, tuyệt vời): Mở file, vào File → Save a Copy... (sử dụng 'Save As' sẽ thay đổi file hiện tại của bạn). Chọn 'Text CSV (.csv)' làm định dạng và xác nhận bạn muốn Keep Current Format. Thao tác này sẽ mở ra một hộp thoại mạnh mẽ với các tùy chọn rõ ràng. Bạn có thể đặt Field Delimiter (Ký tự phân cách trường), Text Delimiter (Ký tự phân cách văn bản - hãy để là dấu ngoặc kép), và quan trọng nhất là Character Set (Bộ ký tự). Chọn 'Unicode (UTF-8)'. LibreOffice tạo ra một file UTF-8 thuần túy không có BOM một cách chính xác, làm cho nó trở nên lý tưởng cho việc sử dụng theo chương trình. Đây là hạn chế của cả hai công cụ: không công cụ nào cho bạn quyền kiểm soát cách các giá trị null, ô trống, hoặc giá trị boolean được biểu diễn. Nếu quy trình sau đó của bạn cần chuỗi rỗng cho các giá trị null thay vì chỉ là không có gì, hoặc mong đợi `1`/`0` thay vì `TRUE`/`FALSE`, bạn sẽ không gặp may. Bạn sẽ phải viết một script để dọn dẹp file CSV sau đó hoặc sử dụng một công cụ mạnh mẽ hơn ngay từ đầu.
Chuyển đổi bằng Script Python (Cho các quy trình lặp lại)
Khi bạn phải chuyển đổi cùng một loại file XLSX lặp đi lặp lại—như một báo cáo hàng tuần từ hệ thống kế toán—thao tác nhấp chuột thủ công là công thức cho sai lầm. Một script Python đơn giản sẽ nhanh hơn, đáng tin cậy hơn và chạy trong vài giây. Đầu tiên, bạn sẽ cần các thư viện `openpyxl` và `pandas`. Cài đặt chúng bằng `pip install openpyxl pandas`. Sau đó, mã lệnh cực kỳ gọn gàng: ```python import pandas as pd df = pd.read_excel('report.xlsx', sheet_name='Sales Data', engine='openpyxl') df.to_csv('report.csv', index=False, encoding='utf-8') ``` Tham số `sheet_name` có thể là tên của trang tính dưới dạng chuỗi hoặc chỉ số của nó bắt đầu từ 0. Đừng quên `index=False`; nó ngăn pandas ghi số hàng của chính nó vào cột đầu tiên, điều này chắc chắn sẽ gây nhầm lẫn cho hầu hết các công cụ nhập dữ liệu. Đối với tham số `encoding`, `'utf-8'` tạo ra một file UTF-8 tiêu chuẩn, không có BOM. Nếu bạn biết đầu ra sẽ được gửi trực tiếp cho người dùng Excel, hãy sử dụng `'utf-8-sig'` để bao gồm BOM. Pandas cũng rất thông minh về ngày tháng, phân tích chúng thành các đối tượng `datetime64`. Nó ghi chúng ở định dạng ISO 8601 (`YYYY-MM-DD HH:MM:SS`), định dạng mà hầu hết các cơ sở dữ liệu đều yêu thích. Nếu hệ thống đích của bạn khó tính và cần một định dạng như `MM/DD/YYYY`, chỉ cần thêm đối số `date_format='%m/%d/%Y'` vào lệnh gọi `to_csv()` của bạn. Đối với các file khổng lồ (hơn 50,000 hàng), `openpyxl` tải toàn bộ file vào bộ nhớ, vì vậy `pd.read_excel()` có thể chậm. Việc ghi cũng có thể là một điểm nghẽn. Nếu bạn gặp giới hạn bộ nhớ, bạn có thể ghi file CSV theo từng đoạn. Và nếu bạn đang xử lý các file `.xls` cũ, engine `xlrd` sẽ nhanh hơn, nhưng đối với các file `.xlsx` hiện đại, hãy gắn bó với `openpyxl`. Nếu bạn có nhiều trang tính cần xuất, `pd.read_excel('report.xlsx', sheet_name=None)` là một cứu cánh. Nó trả về một từ điển trong đó các khóa là tên trang tính và các giá trị là các DataFrame. Sau đó, bạn có thể lặp qua từ điển này và lưu mỗi trang tính vào file CSV riêng của nó—cách hoàn hảo để tự động hóa việc xuất toàn bộ workbook.
Kiểm tra file CSV của bạn trước khi nhập vào cơ sở dữ liệu
Một file CSV có thể trông hoàn hảo trong bản xem trước nhưng vẫn làm hỏng quá trình nhập cơ sở dữ liệu của bạn. Bất cứ ai đã từng chứng kiến một công việc nhập dữ liệu thất bại âm thầm sau 20 phút đều biết nỗi đau đó. Một vài bước xác thực nhanh có thể phát hiện sớm những 'yêu tinh' này. Đầu tiên, hãy kiểm tra số lượng hàng. Nếu file XLSX của bạn có 12,847 hàng dữ liệu và một hàng tiêu đề, file CSV của bạn phải có chính xác 12,848 dòng. Trên Linux hoặc macOS, `wc -l report.csv` cho bạn câu trả lời ngay lập tức. Nếu các con số không khớp, có thể một ô nào đó đã chứa ký tự xuống dòng—một vấn đề phổ biến trong các trường 'ghi chú' nơi người dùng chỉ nhấn Enter. Một trình ghi CSV chuẩn nên trích dẫn điều này một cách chính xác, nhưng đó là một điểm thất bại thường xuyên. Tiếp theo, kiểm tra ngẫu nhiên các ký tự đặc biệt của bạn. Tìm một hàng trong file XLSX gốc có dấu, ký hiệu tiền tệ hoặc văn bản không phải chữ Latin. Bây giờ hãy mở file CSV trong một trình soạn thảo văn bản hiển thị encoding của file (cả VS Code và Notepad++ đều làm được). Xác nhận encoding là cái bạn đã chọn và các ký tự được hiển thị chính xác. Kiểm tra ký tự phân cách. Nếu bạn đã sử dụng dấu phẩy, hãy tìm kiếm trong văn bản thô các dấu phẩy không nằm trong dấu ngoặc kép. Một lệnh `grep` nhanh trên Linux (`grep -P '(?<!\" ),[^\"\\n]*(?<!\" ),' report.csv`) có thể giúp tìm các hàng mà việc trích dẫn có thể đã bị bỏ sót, mặc dù bạn có thể cần phải điều chỉnh regex cho dữ liệu cụ thể của mình. Cuối cùng, xác thực một vài kiểu dữ liệu cột. Nếu một cột được cho là toàn số nguyên, hãy kiểm tra nó. Trong Python, bạn có thể tải file CSV và kiểm tra `pd.read_csv('report.csv')['order_id'].dtype`. Nó phải báo là `int64`. Nếu nó báo là `object`, bạn đang có vấn đề—một ký tự văn bản đi lạc, một lỗi `#N/A` bạn đã bỏ sót, hoặc một ký hiệu tiền tệ đã lẻn vào một ô số. Dành năm phút cho những kiểm tra này sẽ giúp bạn tiết kiệm hàng giờ gỡ lỗi một lần tải dữ liệu thất bại.
Các trường hợp đặc biệt và những hạn chế thực tế
Ngay cả với các công cụ phù hợp, một số tính năng của XLSX không thể ánh xạ một cách gọn gàng sang thế giới đơn giản của CSV. Dưới đây là một vài cái bẫy phổ biến. Ô đã gộp (Merged cells): Các ô được gộp của Excel là một thủ thuật trực quan. Khi bạn chuyển đổi sang CSV, chỉ có giá trị từ ô trên cùng bên trái của khu vực đã gộp còn tồn tại; các ô khác trở thành ô trống. Nói thẳng ra là: không có cách sửa tự động nào cho vấn đề này. Bạn phải bỏ gộp các ô trong Excel *trước khi* chuyển đổi (Chọn tất cả, Format → Cells → Alignment, bỏ chọn Merge cells) và điền thủ công vào các ô giờ đã trống. Hàng tiêu đề nhiều dòng: Thường thấy các báo cáo có hai hoặc ba hàng tiêu đề—một danh mục chính, một danh mục phụ, và sau đó là tên cột thực tế. Các trình phân tích CSV được xây dựng cho đúng một hàng tiêu đề. Đây là một công việc dọn dẹp thủ công khác; bạn sẽ phải đơn giản hóa các tiêu đề trong XLSX hoặc xóa các hàng thừa khỏi CSV trước khi nó có thể sử dụng được. Lỗi công thức: Nếu một ô trong Excel hiển thị `#REF!`, `#DIV/0!`, hoặc `#VALUE!`, chính xác văn bản đó sẽ được ghi vào file CSV của bạn. Những chuỗi đó sẽ làm hỏng bất kỳ cột số nào và gây ra lỗi kiểu dữ liệu trong quá trình nhập. Hãy dọn dẹp chúng trong Excel trước, bằng cách sửa dữ liệu nguồn hoặc bọc các công thức trong hàm `IFERROR()`. Các file rất lớn: CocoConvert khá hào phóng, xử lý các file lên đến 50 MB miễn phí và 200 MB với tài khoản. Đối với bất cứ thứ gì lớn hơn, một công cụ trực tuyến không thực tế. Hãy chuyển sang phương pháp Python/pandas hoặc sử dụng công cụ dòng lệnh của LibreOffice (`libreoffice --headless --convert-to csv filename.xlsx`). Các file XLSX được bảo vệ bằng mật khẩu: Không có công cụ trực tuyến nào, kể cả CocoConvert, có thể mở một file được bảo vệ bằng mật khẩu. Bạn phải tự xóa mật khẩu trong Excel (File → Info → Protect Workbook → Encrypt with Password), lưu file, rồi mới tải lên. Hình ảnh và biểu đồ: Chúng sẽ bị loại bỏ một cách âm thầm. Chúng đơn giản là không tồn tại trong định dạng CSV. Nếu trang tính của bạn chủ yếu là một biểu đồ với một bảng dữ liệu nhỏ, biểu đồ sẽ biến mất vào hư không, chỉ để lại phần văn bản. Đó không phải là lỗi, chỉ là một hạn chế cơ bản của CSV.