0

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()
Mr.Slowbro
  • 7
  • 2

1 Answers1

1

Pandas is actually really well suited for this type of thing, I suggest to look at this question.

tl;dr Use one of these methods

  • to_numeric()
  • astype()
  • infer_objects()
  • convert_dtypes()
joshmeranda
  • 3,001
  • 2
  • 10
  • 24