Bài viết này mình sẽ hướng dẫn các bạn cập nhật dữ liệu mới giữa 2 bảng Excel nhanh chóng, chính xác giúp tiết kiệm thời gian mà không cần phải biết nhiều kiến thức về các hàm trong excel.
Bài toán mình đặt ra trong bài viết này là: giả sử bạn có một tệp dữ liệu file1.xlsx đã sử dụng nhiều năm, trong đó có một số trường dữ liệu đã cũ không còn phù hợp và bạn muốn chỉnh sửa cập nhật các dữ liệu này mới hơn.
File dữ liệu này rất lớn có đến hàng chục hoặc hàng trăm nghìn dòng với nhiều cột khác nhau, bạn chỉ muốn sửa dữ liệu ở một cột cho file. Nếu bạn sử dụng các công cụ như chính excel thì việc xử lý dữ liệu lớn mất rất nhiều thời gian và dễ bị nhầm lẫn, đơ treo bị tắt phần mềm excel dẫn đến bị mất các chỉnh sửa trước đó.
Bạn đã có một danh sách khác file2.xlsx gồm các dữ liệu cần sửa với vài chục hoặc vài trăm dòng và bạn muốn cập nhật chính xác các dữ liệu cần sửa này vào file file1.xlsx trên. Nhưng trong file1.xlsx có hàng nghìn dữ liệu cần sửa do nhiều dữ liệu trùng nội dung với 1 dữ liệu ở file file2.xlsx. Nếu bạn dùng tính năng tìm kiếm và thay thế thì chỉ có thể làm từng dữ liệu một rất mất thời gian để làm tất cả.
Để dễ hiểu mình đưa ra một ví dụ: file file1.xlsx có các cột từ khóa và url trong đó có các hàng là các từ khóa khác nhau tương ứng với đó sẽ có 1 url nhưng có thể có nhiều từ khóa chung một url.
Bạn có một tệp file2.xlsx có các cột url1 và url2, trong đó cột url1 là dữ liệu cũ cần chỉnh sửa (nó giống với một phần dữ liệu ở cột url ở file1.xlsx), cột url2 là dữ liệu đã chỉnh sửa tương ứng với url1 và cần cập nhật sang file1.xlsx
Cách cập nhật dữ liệu từ tệp file2.xlsx sang tệp file1.xlsx và xuất ra file mới
Bước 1: chuẩn bị file dữ liệu
– Chuẩn bị tệp file1.xlsx chứa các cột url có giá trị cần thay thế
– Chuẩn bị tệp file2.xlsx chứa các cột url1 và url2
+ Cột url1: chứa các url gốc cần thay thế
+ Cột url2: chứa các url đã chỉnh sửa muốn thay thế
Lưu ý: 2 file này cần ở trong cùng thư mục ví dụ như C:\Users\dudn\Documents\Code như ảnh bên dưới.
Bước 2: tạo các file python để xử lý dữ liệu
Bạn có thể sử dụng phần mềm notepad++ nếu có sẵn, nếu chưa có thì có thể tải trên mạng về và cài đặt, nó cũng rất dễ cài đặt thôi. Hoặc bạn có thể cài đặt phần mềm Sublime Text nó cũng tương tự như notepad++ nhưng giao diện của các dòng code trông sẽ đẹp hơn.
– Cách 1: Bạn tạo file mới lưu tên là replace_urls.py chứa các đoạn code sau: (dùng để thay thế các url và giữ nguyên toàn bộ các dòng không có trường bị thay thế và xuất ra file mới có tên là file1_updated.xlsx )
import pandas as pd
# Đọc file 1 (chứa cột keyword và url)
file1_path = 'file1.xlsx' # Đường dẫn tới file 1
df1 = pd.read_excel(file1_path)
# Đọc file 2 (chứa cột url1 và url2)
file2_path = 'file2.xlsx' # Đường dẫn tới file 2
df2 = pd.read_excel(file2_path)
# Tạo từ điển thay thế từ file 2
replace_dict = pd.Series(df2['url2'].values, index=df2['url1']).to_dict()
# Thay thế các giá trị trong cột 'url' của file 1
df1['url'] = df1['url'].replace(replace_dict)
# Lưu kết quả vào file Excel mới
output_path = 'file1_updated.xlsx' # Đường dẫn file kết quả
df1.to_excel(output_path, index=False)
print(f"Kết quả đã được lưu tại: {output_path}")
– Cách 2: Tạo file filter_replace_urls.py chứa các đoạn code sau: (dùng để thay thế các url và xuất ra file mới chỉ có các hàng có dữ liệu được thay thế có tên là updated_urls.xlsx )
import pandas as pd
# Đường dẫn file
file1_path = 'file1.xlsx' # File chứa các cột keyword, url
file2_path = 'file2.xlsx' # File chứa các cột url1, url2
output_path = 'updated_urls.xlsx' # File xuất ra chứa các hàng được thay thế
# Đọc dữ liệu từ file Excel
df1 = pd.read_excel(file1_path) # Đọc file 1
df2 = pd.read_excel(file2_path) # Đọc file 2
# Tạo từ điển thay thế từ file 2
replacement_dict = dict(zip(df2['url1'], df2['url2']))
# Sao chép dữ liệu gốc để lưu lại bản thay đổi
df1['new_url'] = df1['url'].replace(replacement_dict)
# Lọc ra các hàng có sự thay đổi URL
updated_rows = df1[df1['url'] != df1['new_url']]
# Lưu các hàng đã thay đổi vào file Excel mới
updated_rows.to_excel(output_path, index=False)
print(f"Các hàng được thay thế đã được lưu vào file: {output_path}")
Bước 3: cài đặt thư viện excel cho python và chạy các file replace_urls.py và filter_replace_urls.py
– Mở cửa sổ CMD lên (bấm phím windows trên bàn phím và gõ cmd và ấn enter) và chạy các dòng lệnh sau:
pip install pandas
(Pandas là Thư viện trong Python để xử lý dữ liệu)
Như ở ảnh trên ở cuối có dòng thông báo notice rằng mình đang dùng phiên bản Python 24.0 và cần update lên phiên bản 24.3.1 nên mình sẽ thực hiện update lên bản này. Nếu máy tính của bạn chưa cài python thì nó sẽ có thông báo cho bạn dòng lệnh cần thiết để cài đặt Python và bạn làm theo hướng dẫn là được.
pip install openpyxl
(Thư viện này là cần thiết để làm việc với file Excel trong pandas)
– Chạy các file replace_urls.py và filter_replace_urls.py
Vào thư mục chứa các file trên nếu đang ở ngoài thư mục này bằng câu lệnh:
Vd đang ở thư mục C:\Users\dudn\Documents> muốn vào thư mục C:\Users\dudn\Documents\Code thì gõ lệnh: cd Code
Sau khi vào các thư mục chứa các file cần chạy thì nhập lần lượt từng dòng lệnh bên dưới rồi enter,
python replace_urls.py
python filter_replace_urls.py
Bạn đợi vài giây để xử lý dữ liệu, xử lý xong sẽ hiển thị thông báo thành công như hình trên và đồng thời xuất ra các tệp file1_updated.xlsx và updated_urls.xlsx vào thư mục C:\Users\dudn\Documents\Code như bạn thấy ở bức ảnh trên đầu bài viết.
Giờ bạn có thể thử kiểm tra xem nó hoạt động có chính xác hay không bằng cách mở file2.xlsx ra và copy thử một url đã chỉnh sửa rồi vào 2 tệp trên xem nó chính xác chưa. Mình đã kiểm tra và rất chính xác.
Quá tuyệt vời phải không các bạn, giờ bạn đã có công cụ để xử lý update dữ liệu của riêng mình. Lần sau bạn chỉ cần copy 2 file khác mà bạn muốn update cho vào cùng thư mục trên và để với tên là file1.xlsx và file2.xlsx và mở cửa sổ CMD lên, gõ lệnh cd Documents ; cd Code để di chuyển đến thư mục Code và nhập hai dòng lệnh python replace_urls.py ; python filter_replace_urls.py như ở trên là ngay lập tức sẽ có được file mà mình mong muốn.
Lưu ý quan trọng: nếu tên file và các cột của bạn không giống với ví dụ ở trên thì bạn cần sửa lại đoạn code trên trước khi chạy, thay thế các giá trị file1.xlsx ; file2.xlsx ; url ; url1 ; url2 ; bằng tên file và tên các cột của bạn.
