0

Let's say I have file1.xlsx where A2 = 5 and file2.xlsx where A2=7.

I want to be able to merge them to create a file3.xlsx where A2=12 (7+5)

Thank you for your help

--

tried this but still not working :

import pandas as pd

file1 = pd.read_excel('C:\test\file1.xlsx', index_col=None, header=None)
file2 = pd.read_excel('C:\test\file2.xlsx', index_col=None, header=None)

'C:\test\file3.xlsx' = file1.add(file2, fill_value=0)

2 Answers2

0

Does this satisfy your requirement?

import pandas as pd

file1 = pd.read_excel('file1.xlsx', index_col=None, header=None)
file2 = pd.read_excel('file2.xlsx', index_col=None, header=None)

file3 = file1.add(file2, fill_value=0)
Damzaky
  • 6,073
  • 2
  • 11
  • 16
  • Thanks for your answer but it's not working. Getting some kind of error " File "C:\Users\Mark\Anaconda3\lib\ssl.py", line 98, in import _ssl # if we can't import it, let the error propagate ImportError: DLL load failed: The specified module could not be found." –  Mar 31 '20 at 14:56
  • it's a different problem, seems like that error is caused by anaconda setting, please refer to [this](https://stackoverflow.com/questions/54175042/python-3-7-anaconda-environment-import-ssl-dll-load-fail-error) – Damzaky Mar 31 '20 at 14:58
  • Fixed it. But now i'm getting –  Mar 31 '20 at 15:05
  • try to specify the full file directory like: 'D:\folder1\file1.xlsx' – Damzaky Mar 31 '20 at 15:06
  • I updated my question, it's still not working. Sorry for bothering you so much!.. –  Mar 31 '20 at 15:22
  • you mean that you want to save the file3? at least check the output of the file3 variable first and see if it shows what you need – Damzaky Mar 31 '20 at 15:26
  • im not getting any output. I'm getting 'file3.xlsx' = file1.add(file2, fill_value=0) ^ SyntaxError: can't assign to literal –  Mar 31 '20 at 15:28
  • try to use all my code and add `print(file3)` after the last line, your code is assigning something to a string, not to a variable – Damzaky Mar 31 '20 at 15:47
  • It worked. Im just a newb and didnt put the xlsx files in the py project lol. thank you ! –  Mar 31 '20 at 15:55
  • Im sorry to bother you, but one last thing, How can I save the final output in a file? –  Mar 31 '20 at 16:00
  • add this at the last `file3.to_excel("file3.xlsx")` – Damzaky Apr 01 '20 at 03:26
0

Using Openpyl

from openpyxl import load_workbook, Workbook

# Input
wb1 = load_workbook('file1.xlsx')
wb2 = load_workbook('file2.xlsx')

# Assuming data on Sheet1 in both
ws1 = wb1['Sheet1']
ws2 = wb2['Sheet1']

# Output - New workbook
wb = Workbook()
ws = wb.active
ws['A2'] = ws1['A2'].value + ws2['A2'].value

wb.save("file3.xlsx")
DarrylG
  • 16,732
  • 2
  • 17
  • 23
  • it's not working, I even remplaced 'file1.xlsx', file2.xlsx, by it's full file directory! I'm getting "OSError: [Errno 22] Invalid argument: 'C:\test\x0cile1.xlsx'", but when I use your code, i'm getting : FileNotFoundError: [Errno 2] No such file or directory: 'file1.xlsx' –  Mar 31 '20 at 15:22
  • @ChNa--This is my [test code](https://repl.it/@DarrylGurganiou/LawngreenSaddlebrownInstance) that works. – DarrylG Mar 31 '20 at 15:36
  • @ChNa--seems your issue is you are specifying the file path incorrectly as [this post](https://stackoverflow.com/questions/25584124/oserror-errno-22-invalid-argument-when-use-open-in-python) mentions. You don't need to specify the full file path if the files are in your current Python working directory which you can [find and change](https://note.nkmk.me/en/python-os-getcwd-chdir/). – DarrylG Mar 31 '20 at 15:40