How to read data in xlsx table? line by line to format the data inserting into columns?

Question

Python

There is an array of data line by line (each in one cell at the beginning of the line) in the name.xlsx table of the form:
15:32:50> 10-19-08 14:48:58
15:32:50> Flow 0 m3/h
15:32 :50> POS: +4x m
15:32:50>
15:33:03> 19-10-08 14:49:58
15:33:03> Flow 0 m3/h
15:33:03> POS: + 4x m
15:33:03>
15:33:16> 19-10-08 14:50:58
15:33:16> Flow 0 m3/h
15:33:16> POS: +6x m
15:33: 16>
you need to split the data into 3 columns and save it to the same file (in a new sheet) to get:
1 column 2 column 3 column
10/19/08 2:48:58 0 4
10/19/08 2:49:58 0
4 14:50:58 0 6

Answer 1

I would probably group all the data by the date of the log, and then parse the values ​​​​through regexp (if they are fixed, you can also hard by index). More or less like this

import openpyxl
import re

wb = openpyxl.load_workbook('name.xlsx')
sheet = wb.active

log = dict()
# перебираем ячейки 1-го столбца и группируем по времени лога
for c in sheet['A']:
    key = c.value[:8]
    val = c.value[10:]
    log[key] = log.get(key,'') + val

rc = re.compile(r"(\d+-\d+-\d+) (\d+:\d+:\d+).*?Flow (\d+).*?POS:.*?(\d+)")
new_data = []
for key, val in log.items():
    m = rc.search(val)
    if m:
        new_data.append(m.groups())

# наши разбитые данные
print('new data:', new_data)

# и записываем результат на новый лист (для простоты через добавление новых строк)
sheet = wb['Sheet2']
for row in new_data:
    sheet.append(row)
wb.save('name.xlsx')

Answer 2

As an option - Break the text into columns. First, the separator >
then the separator SPACE
Then simply delete the columns you don't need with a shift to the left. This is to pull up 0 4