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
#11
Second version:

Things todo:
- take lastline instead of firstline
- get value from lastline

Maybe you could code the points above yourself:

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

# open xls
wb = xls.load_workbook(filename="sample.xlsx", data_only=True)
ws = wb.active

# read data in E2 for filename
filename = str(ws["E2"].value) + ".txt"
print(filename)

with open(filename) as file:
    lines = file.readlines()

lastline = lines[0].strip() #todo: lastline
print(lastline)

# replace data
ws["G2"] = lastline #todo extract value
wb.save("sample2.xlsx")
Reply
#12
Hi heiner55, I have done this already and I am able to copy the value from Text to excel when mentioning "data_only= True".
But I need to populate without this option because, when I use this "data_only= True" it removes formulas from other columns.

Hence I tried without this option and also by giving "data_only= False" but nothing happens. No values are copied, this because the cell contains formula, hence the value in the cell is not compared with text.

I have given a screenshot, and as highlighted in cell "E2" we have formula but I need only the value "12" to be taken and go to the text file with name "12", copy value and paste it in cell "G".
.txt   12.txt (Size: 140 bytes / Downloads: 213)
.xlsx   sample.xlsx (Size: 9.44 KB / Downloads: 216)
.docx   Screenshot.docx (Size: 98.73 KB / Downloads: 186)
Reply
#13
one workaround is
1. open file once with data_only=True and read column E values into memory
2. open the file second time and populate column G using values you read into memory in step 1

another option is to calculate values from column e in your script (in your example I see column e is result of multiplication of values in column B)

yet another option is to use different library like xlwings instead of openpyxl
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
#14
Hi,

I am able to write without removing formulas using XLwings

here is the code:
import xlwings as xw
wb = xw.Book('C:/Users/Template v5.xlsx')
sht = wb.sheets['lity']

Now I need to loop this sheet to find text called "Talk" and paste 1,2,3,4,5,6 after six rows in the same column where "Talk" word is found.
Please let me know looping in xlwings
Reply
#15
You can open the file twice,
with data_only and without data_only.

#!/usr/bin/python3
import openpyxl as xls
 
# open xls (readonly)
wbro = xls.load_workbook(filename="sample.xlsx", data_only=True)
wsro = wbro.active
 
# open xls
wb = xls.load_workbook(filename="sample.xlsx")
ws = wb.active

# read data in E2 for filename
filename = str(wsro["E2"].value) + ".txt"
print(filename)
 
with open(filename) as file:
    lines = file.readlines()
 
lastline = lines[0].strip() #todo: lastline
print(lastline)
 
# replace data
value = lastline.split()[1]
print(value)

ws["G2"] = value
wb.save("sample2.xlsx")
Reply
#16
Thank you heiner55 , XLWINGS library solved the problem
Reply
#17
Hi,
I am able to successfully preserve the formulas and also updating the required cells with desired values. Here is what I did.

exl = openpyxl.load_workbook(exlFile)
sheet = exl["Sheet1"]

now again define the same excel this time using data_only=true

exl1 = openpyxl.load_workbook(exlFile, data_only=True)
sheet1 = exl1["Sheet1"]

Now while reading the data from excel, use sheet1 while writing back to excel, use sheet in the same loop.
Also while saving the workbook at the end, use exl.save(exlFile) instead of exl1.save(exlFile)

With this I was able to retain all the formulas and also could update the required cells.
Let me know if this is sufficient or need more info
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 349 Feb-07-2024, 12:24 PM
Last Post: Viento
  Search Excel File with a list of values huzzug 4 1,148 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