Python Forum
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Pasting via OpenPYXL
#1
Hello Python Forum -

I work in motorsports and would like to automate the process of coping data from race software into excel for manipulation. The part I am stuck on is the actual pasting into excel from OpenPyXL so that all the data from the clipboard does not end up in one cell. If I manually copy a chart from the race software into the clipboard, click on any cell in excel, and <ctrl-V>, the chart gets pasted with appropriate rows/columns as shown below:

[Image: excel.jpg]

However, using OpenPyXL, if I copy the contents of the clipboard into a string (clipped)and then paste that string into an excel cell using OpenPyXl, everything ends up in one cell. See the actual string data below:

[Image: OpenPyXL.jpg]

My question - is there a simple command, another module or maybe another data type instead of string I should be using so a paste function from the clipboard in python breaks out the data into appropriate rows and columns. I'm guessing there is a way to do this by reading the string data, searching for the \t and \r characters representing tabs and breaks, and filling in the excel cells through some type of a loop, but I'm hoping there is an easier way for a python beginner...

Any help would be appreciated!

-Stephan
Reply
#2
where does the data/string come from?
You can parse the string - e.g. split in meaningful parts that you want in separate cells and write these into respective cells

also, don't paste images of code, copy/paste in python tags
Please, use proper tags when post code, traceback, output, etc.
See BBcode help for more info.
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
Thanks Buran - the data is copied to the clipboard manually from a generated chart within the race software. This is something I will continue to do in the process. The copying of the clipboard contents into excel and then further manipulation of the data is what I would like to achieve via python. The part I do not understand is why a copy/paste from my keyboard into an excel cell does not function the same way as pasting the clipboard into a cell via OpenPyXl.
Reply
#4
you haven't show any code, so we don't know what you are doing with openpyxl, i.e. there is no "paste" concept in openpyxl. You write something, in this case a string into a cell(s).
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
#5
Tried also using the xlxswriter module -

clipped = pyperclip.paste()
filename1 = 'GEMSclipboarddataFORxlsxwriter.xlsx'
workbook = xlsxwriter.Workbook(filename1)
worksheet = workbook.add_worksheet()
worksheet.write(0, 0, str(clipped))
workbook.close()
This what the value of the string CLIPPED returns after running the program:

>>> clipped
'"\t1: Lap 9\t\t\n\tSelection\t\t\n\tMax\tAvg\t\nAct Throttle [%]\t100\t59.69\t\nRoad Speed [km/h]\t102\t88.10\t\nOil Pressure Filtered [Bar]\t6.24\t5.8112\t\nSpark adv (map) [°]\t29.7\t24.304\t\nLambda1 [AFR]\t1.98\t1.0752\t\nFuel Total PW 1 [ms]\t2.14\t1.5420\t\nLambda1 FB [%]\t13.7\t11.046\t\nFuel Pressure [Bar]\t60.9\t56.819\t\nEngine Load [kPa]\t99\t84.87\t\nAir Temp [°C]\t20\t19.15\t\nCoolant [°C]\t76\t75.94\t\nOil Temperature [°C]\t53\t52.77\t\n"\r\n'
>>>

...and the entire string is written into one cell in the excel sheet (which I know is exactly what I am asking it to do). This again is what I would like it to look like in excel:
[Image: excel.jpg]

Thanks for your patience with me while I try to learn a new language!
Reply
#6
can you post the value of clipped, not as image, so that I can work with it and help you - that's why we ask not to post code as image (https://idownvotedbecau.se/imageofcode)
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
#7
The value of clipped is not an image in my last reply. That is copied directly from IDLE..
Reply
#8
from openpyxl import Workbook

# in reality you will read this from clipboard, e.g. using pyperclip
clipped = '\t1: Lap 9\t\t\n\tSelection\t\t\n\tMax\tAvg\t\nAct Throttle [%]\t100\t59.69\t\nRoad Speed [km/h]\t102\t88.10\t\nOil Pressure Filtered [Bar]\t6.24\t5.8112\t\nSpark adv (map) [°]\t29.7\t24.304\t\nLambda1 [AFR]\t1.98\t1.0752\t\nFuel Total PW 1 [ms]\t2.14\t1.5420\t\nLambda1 FB [%]\t13.7\t11.046\t\nFuel Pressure [Bar]\t60.9\t56.819\t\nEngine Load [kPa]\t99\t84.87\t\nAir Temp [°C]\t20\t19.15\t\nCoolant [°C]\t76\t75.94\t\nOil Temperature [°C]\t53\t52.77\t\n\r\n'

clipped = clipped.split('\t\n')
clipped = [item.split('\t') for item in clipped]
wb = Workbook()
ws = wb.active
for row, row_data in enumerate(clipped, start=1):
    for col, cell_data in enumerate(row_data, start=1):
        ws.cell(row=row, column=col, value=cell_data)
wb.save('spam.xlsx')
note that there is extra \r\n at the end. you may do extra steps to remove these
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
#9
Thanks so much for the help Buran! I really appreciate it.
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Why is copying and pasting a block now broken? WagmoreBarkless 2 1,333 May-05-2022, 05:01 AM
Last Post: WagmoreBarkless
  Why is copying and pasting a block now broken? WagmoreBarkless 1 1,197 May-04-2022, 11:40 PM
Last Post: Larz60+
  copy/pasting in excel WHILE keep file format zarize 0 1,918 Jun-23-2020, 03:51 PM
Last Post: zarize
  openpyxl Pasting data to different cells Kristenl2784 3 7,560 Jun-15-2020, 08:50 PM
Last Post: Yoriz
  Pasting multiple images on to one image with a for loop. DreamingInsanity 2 6,415 Feb-01-2019, 12:39 PM
Last Post: DreamingInsanity
  Looping .xlsx files in folder/subfolders copy pasting currentregion HarrisQ 4 5,501 Apr-17-2017, 06:35 AM
Last Post: HarrisQ

Forum Jump:

User Panel Messages

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