I have notepad.txt generated files that I paste to Excel. Reason I'm using Excel is because it has been neatly formatted, stylized in the Master Sheet, and also Column A in the "DATA TAB" contains formulas that I wish to keep. Anyway, this is what I want to achieve:
After running my code, the numbers paste nicely into the Excel sheet. Only problem is that after running the code, the data is pasted as text, and I have to manually convert the text to numbers in order for my vlookup formulas to recognize these characters. The data I paste contains 3 columns(First 2 are strings, 1 is number).
Any advice is greatly appreciated!
Code as below:
import pandas as pd
from openpyxl import load_workbook
from openpyxl import workbook
#Origin Files
df1 = pd.read_table('Source Data.txt',encoding = 'UTF-16 LE', sep='\t')
#Reading the Master File for data to be pasted here
book = load_workbook("Master.xlsx")
writer = pd.ExcelWriter('Master.xlsx', engine='openpyxl')
writer.book = book
writer.sheets = dict((ws.title, ws) for ws in book.worksheets)
#Paste Data into Column B, Row 3:
df1.to_excel(writer, "DATA", startcol=1,startrow=2)
writer.save()
