Python Forum
To Copy text values to Excel using Python
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
To Copy text values to Excel using Python
#1
I am using Python to copy values from Text file to excel, able to succeed in that but the problem is that when pasting values to excel the existing formulas are removed.

I am using "import openpyxl" and also have a code as "data_only= False" when loading workbook. If I use this then values are pasted but formulas are removed, so changed "data_only= True" but unable to paste values corresponding to a column with formulas. Any help would be greatly appreciated. Thank you.
Reply
#2
(May-31-2019, 04:44 PM)ksasi2k3 Wrote: I am using "import openpyxl" and also have a code as "data_only= False" when loading workbook. If I use this then values are pasted but formulas are removed
as per the docs this will keep the formulas
If you can't explain it to a six year old, you don't understand it yourself, Albert Einstein
How to Ask Questions The Smart Way: link and another link
Create MCV example
Debug small programs

Reply
#3
Without a sample from you, it is difficult to help.

Maybe you have meant this:
#!/usr/bin/python3
from openpyxl import Workbook
wb = Workbook()
ws = wb.active
ws["A3"] = "=SUM(A1, A2)"

ws["A1"] = 10
ws["A2"] = 100
wb.save("formula.xlsx")
Reply
#4
Thank you heiner55 for your reply.
I have attached example files.

I have a text file 12.txt as attached, I want to insert the values in this file to attached excel and in column "C" named as Finaltotal. This column has formulas and need to paste values from text file to this column.


Secondly: In excel I have column name "Textfile" this has value 12 and need to find text file with name 12, for simplicity purpose I have named the text file as "12". Need to find the value from this "Textfile" column and paste the values from 12 text to corresponding "Pastevalues" column.

Attached Files

.txt   12.txt (Size: 140 bytes / Downloads: 384)
.xlsx   sample.xlsx (Size: 9.42 KB / Downloads: 304)
Reply
#5
Could you also attach the excel-file
which results from "12.txt" and "sample.xls" ?
Reply
#6
Hi heiner55, Sample.xlsx is the resulting file.
Reply
#7
Then I need the "input.xls".

12.txt + input.xls ==> sample.xls
Reply
#8
It is the same excel file which acts as Input and output. For your reference I am attaching it with slightly modified and included formulas. "Sample.xlsx" is the input and output file.
.xlsx   sample.xlsx (Size: 9.44 KB / Downloads: 244)
Reply
#9
Sorry I can not help, because I do not understand your issue.
Maybe you could use my very first version:

#!/usr/bin/python3
import openpyxl as xls

#todo: read file 12.txt
tm    = [0]*6
tm[0] = 0.99999982e-01
tm[1] = 9.84491405e-08
tm[2] = 2.08444947e-16
tm[3] = 6.91223128e-24
tm[4] = 8.09480403e-31
tm[5] = 0.00000000e+00
print(tm)

#xls
wb = xls.load_workbook(filename="sample.xlsx")
ws = wb.active

ws["G2"] = tm[0]
ws["H2"] = tm[1]
ws["I2"] = tm[2]

wb.save("sample2.xlsx")
Reply
#10
Let me explain in step by step process.

1.I have an excel file with formulas included in columns.
2.I want to paste the values from txt file to excel file.
3.I have a column name "Text file" in the excel sheet, this columns is derived by using formulas.
4. Need to check the number in that column, example here it is "12", so check for the text file with name "12".
5. Copy the last value from text file "12" and paste it in column "Pastevalues" in excel sheet.
6. So input is from text file,reference and output is the same excel.
.xlsx   sample.xlsx (Size: 9.44 KB / Downloads: 228)
.xlsx   sample.xlsx (Size: 9.44 KB / Downloads: 228)

Attached Files

.txt   12.txt (Size: 140 bytes / Downloads: 251)
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Copy Paste excel files based on the first letters of the file name Viento 2 348 Feb-07-2024, 12:24 PM
Last Post: Viento
  Search Excel File with a list of values huzzug 4 1,147 Nov-03-2023, 05:35 PM
Last Post: huzzug
  save values permanently in python (perhaps not in a text file)? flash77 8 1,121 Jul-07-2023, 05:44 PM
Last Post: flash77
  Copy data from Excel and paste into Discord (Midjourney) Joe_Wright 4 1,926 Jun-06-2023, 05:49 PM
Last Post: rajeshgk
Thumbs Up Need to compare the Excel file name with a directory text file. veeran1991 1 1,071 Dec-15-2022, 04:32 PM
Last Post: Larz60+
  is it possible to copy image from email and place into excel file? cubangt 3 1,212 Nov-30-2022, 05:11 PM
Last Post: snippsat
  Trying to use 2 values from excel in my script but getting error.. cubangt 3 1,625 May-11-2022, 07:12 AM
Last Post: normanwolf
  Want to remove the text from a particular column in excel shantanu97 2 2,094 Jul-05-2021, 05:42 PM
Last Post: eddywinch82
  Copy column from one existing excel file to another file mkujawsk 0 5,488 Apr-14-2021, 06:33 PM
Last Post: mkujawsk
  Importing text file into excel spreadsheet with formatting david_dsmn 1 3,550 Apr-05-2021, 10:21 PM
Last Post: david_dsmn

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020