Pasting via OpenPYXL

[PZG]

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:



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:



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

[buran]

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.

[PZG]

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.

[buran]

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).

[PZG]

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:


Thanks for your patience with me while I try to learn a new language!

[buran]

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)

[PZG]

The value of clipped is not an image in my last reply. That is copied directly from IDLE..

[buran]

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

[PZG]

Thanks so much for the help Buran! I really appreciate it.