英文:
Comparing 2 excel files to extract rows based on a reference number in one file and copy them to a new file
问题
我有2个Excel文件。
文件1是我的主文件，其中包含主要交易数据，第二列有一个唯一的交易参考号。
文件2包含原始交易数据，其中包括一个交易参考号。但是在文件2中，可能会有两行具有相同的交易列。
我想使用Python来比较文件2中的行与文件1，使用交易参考号。如果文件2中的交易参考号存在于文件1中，它不应执行任何操作。然而，如果文件2中的交易参考号在文件1中不存在，则应将包含交易参考号的文件2的整行复制到一个新的Excel文件 - 文件3。
我刚刚开始学习Python，无法完成这个任务。
英文:
I have 2 excel files.
File 1 is my master file and has the master transaction data with a unique transaction reference number in the second column.
File 2 contains raw transaction data which includes a transaction reference number. However in file 2 there maybe 2 rows with same transaction column.
I would like to use Python to compare the rows in File 2 with File 1 using the transaction reference number. If the transaction reference number from file 2 is present in file 1 it should do nothing. However, if the transaction reference number from file 2 is NOT present in File 1 the entire row from file 2 containing the transaction reference number should be copied into a new excel file - File 3
I am just starting to learn Python and am unable to do this.
答案1
得分: 1
"""
author: gorgie7
"""
# 导入这两个包
import xlwings as xw
import xlsxwriter as ex
# 创建第三个文件用于存储不相等的内容
newFile = ex.Workbook("File3.xlsx")
newSheet = newFile.add_worksheet()
# 打开文件1
ws_1 = xw.Book("File1.xlsx").sheets["Sheet1"]
# 创建保存项目的数组
checkArr = []
# 运行第一个文件
# 在范围内，您可以更改行数
for cell in range(1, 3):
# 您可以将"A"替换为您想要的列
item = ws_1.range("A" + str(cell)).value
print(item)
checkArr.append(item)
# 打开文件2
ws_2 = xw.Book("File2.xlsx").sheets["Sheet1"]
# 运行第二个文件
for cell in range(1, 3):
# 与上面相同
item = ws_2.range("A" + str(cell)).value
# 检查项目是否等于文件1
if item == checkArr[cell-1]:
continue
else:
# 写入文件3
# 只需替换"A"和"B"为您想要的列
cell_1 = "A" + str(cell)
cell_2 = "B" + str(cell)
newSheet.write(cell_1, item)
newSheet.write(cell_2, checkArr[cell-1])
# 关闭文件3，这一步很重要
newFile.close()
注意：如果您尚未安装这两个包，请在命令提示符中输入以下命令：
pip install xlwings
pip install xlsxwriter
英文:
"""
author: gorgie7
"""
#import thes 2 packages
import xlwings as xw
import xlsxwriter as ex
#create the third file where the not equals get stored
newFile = ex.Workbook("File3.xlsx")
newSheet = newFile.add_worksheet()
#open file 1
ws_1 = xw.Book("File1.xlsx").sheets["Sheet1"]
#create Array for the saved items
chekArr = []
#run first file
#in range you can change the rows
for cell in range(1, 3):
#you can replace "A" with the colum you want
item = ws_1.range("A"+str(cell)).value
print(item)
chekArr.append(item)
#open file 2
ws_2 = xw.Book("File2.xlsx").sheets["Sheet1"]
#run second file
for cell in range(1,3):
#same thing like above
item = ws_2.range("A"+str(cell)).value
#chek if item is equal to file 1
if item == chekArr[cell-1]:
continue
else:
#writing in file 3
#just replace "A" and "b" with the colums you want
zell_1 = "A" + str(cell)
zell_2 = "B" + str(cell)
newSheet.write(zell_1,item)
newSheet.write(zell_2, chekArr[cell-1])
#closing File 3 important
newFile.close()
Note: if you havent installed the two packages type:
pip install xlwings
pip install xlsxwriter
in command Prompt
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论