Как читать данные в таблице xlsx? построчно форматировать данные вставляя в столбцы?

Question

[dimorexa]

Имеется массив данных построчно (каждый в одной ячейке в начале строки) в таблице name.xlsx вида:

15:32:50> 19-10-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>

требуется разбить данные по 3-ем столбцам и сохранить в этот же файл(в новом листе) чтобы получилось:

1столбец 2столбец 3столбец
19.10.08 14:48:58 0 4
19.10.08 14:49:58 0 4
19.10.08 14:50:58 0 6

Answer 1

[o5a]

Я бы наверное сгруппировал все данные по дате лога, а потом распарсил значения через regexp (если они фиксированы, можно и жестко по индексу). Примерно так

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

Comments

[dimorexa]

Что я делаю не так ? Добавил адрес файла и ....

import openpyxl
import re

wb = openpyxl.load_workbook('C:\\Users\\OD\\Desktop\\python_scripts\\Book1.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('C:\\Users\\OD\\Desktop\\python_scripts\\Book1.xlsx')

# и в итоге получил это...

new data: []
---------------------------------------------------------------------------
KeyError Traceback (most recent call last)
in
23
24 # и записываем результат на новый лист (для простоты через добавление новых строк)
---> 25 sheet = wb['Sheet2']
26 for row in new_data:
27 sheet.append(row)

~\Anaconda3\lib\site-packages\openpyxl\workbook\workbook.py in __getitem__(self, key)
275 if sheet.title == key:
276 return sheet
--> 277 raise KeyError("Worksheet {0} does not exist.".format(key))
278
279 def __delitem__(self, key):

KeyError: 'Worksheet Sheet2 does not exist.'

[o5a]

dimorexa, wb['Sheet2'] здесь должно быть точное название листа, в который помещаем данные. В русской версии Excel это "Лист2" и подобные названия.

[dimorexa]

o5a, прописал "лист2"

и снова...

new data: []
---------------------------------------------------------------------------
KeyError Traceback (most recent call last)
in
23
24 # и записываем результат на новый лист (для простоты через добавление новых строк)
---> 25 sheet = wb['Лист2']
26 for row in new_data:
27 sheet.append(row)

~\Anaconda3\lib\site-packages\openpyxl\workbook\workbook.py in __getitem__(self, key)
275 if sheet.title == key:
276 return sheet
--> 277 raise KeyError("Worksheet {0} does not exist.".format(key))
278
279 def __delitem__(self, key):

KeyError: 'Worksheet Лист2 does not exist.'

[o5a]

Видимо у вас в том файле только 1 лист. Создайте новый с нужным именем, вместо sheet = wb['Лист2']:

sheet_name = 'Лист2'
if sheet_name not in wb.sheetnames:
    wb.create_sheet(sheet_name)
sheet = wb[sheet_name]

[dimorexa]

o5a, открывает файл, создает в нем Лист2, но результатов нет.


import openpyxl
import re

wb = openpyxl.load_workbook('C:\\Users\\OD\\Desktop\\python_scripts\\book1.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_name = 'Лист2'
if sheet_name not in wb.sheetnames:
wb.create_sheet(sheet_name)
sheet = wb[sheet_name]

sheet = wb['Лист2']
for row in new_data:
sheet.append(row)
wb.save('C:\\Users\\OD\\Desktop\\python_scripts\\book1.xlsx')

[o5a]

dimorexa, Так на скриншоте Лист1 с изначальными данными, а на Лист2 данные смотрели? Или Лист2 пустой совсем? А что печатает new_data: (там должны быть распарсенные данные)? Второй раз использовать sheet = wb['Лист2'] не нужно, до этого мы уже выбираем sheet = wb[sheet_name].

[dimorexa]

o5a, на скриншоте лист1 с первоначальными данными.
Строку "sheet = wb['Лист2']" убрал
Лист2 создается, но на нем пусто.
печатает:
new data = [ ]

[o5a]

dimorexa, Значит он вообще не парсит нужный лист.
Варианты:

1. Лист с данными не основной (тот, что сразу виден при открытии файла).
Вместо sheet = wb.active прописать sheet = wb['Лист1'] с тем названием листа, который с этими данными.

2. Страница выбирается правильно, но по какой-то причине regexp не находит данные (хотя по приведенному скриншоту не вижу, чтобы в этом была проблема). Попробовать добавить вывод print(log) перед rc = re.compile.. и выложить результат сюда.

[dimorexa]

o5a,
1. Лист прописал.

2. print (log) выдал:

{'15:32:50': '19-10-08 14:48:58Flow 0 m3/hPOS: +4x m', '15:33:03': '_x001A_19-10-08 14:48:58Flow 0 m3/hPOS: +4x m', '15:33:16': '_x001A_19-10-08 14:48:58Flow 0 m3/hPOS: +6x m', '15:33:45': '_x001A_19-10-08 14:48:58Flow 0 m3/hPOS: +9x m'}

[o5a]

dimorexa, И после всего этого все равно пишет new_data: [] и не заполняет Лист2? По приведенному вижу, что словарь данных заполняется адекватно. Для примера скопировал приведенный словарь, запустил у себя, new_data распарсилось адекватно, выводит:

new data: [('19-10-08', '14:48:58', '0', '4'), ('19-10-08', '14:48:58', '0', '4'), ('19-10-08', '14:48:58', '0', '6'), ('19-10-08', '14:48:58', '0', '9')]

Т.е. все как и ожидалось, готово к вставке в лист.

[dimorexa]

o5a,
Создает в файле новый Лист2 и в нем пусто все равно. Не могу понять почему не сохраняет ?


import openpyxl
import re

wb = openpyxl.load_workbook('C:\\Users\\OD\\Desktop\\python_scripts\\book1.xlsx')
sheet = wb['Лист1']

log = dict()
# перебираем ячейки 1-го столбца и группируем по времени лога
for c in sheet['A']:
key = c.value[:8]
val = c.value[10:]
log[key] = log.get(key,'') + val
print(log)
rc = re.compile(r"(\d+-\d+-\d+) (\d+:\d+:\d+).*?Flow (\d+).*?POS:.*?(\d+)")
print(rc)
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_name = 'Лист2'
if sheet_name not in wb.sheetnames:
wb.create_sheet(sheet_name)
sheet = wb[sheet_name]

for row in new_data:
sheet.append(row)
wb.save('C:\\Users\\OD\\Desktop\\python_scripts\\book1.xlsx')

Получаем:

{'15:32:50': '19-10-08 14:48:58Flow 0 m3/hPOS: +4x m', '15:33:03': '_x001A_19-10-08 14:48:58Flow 0 m3/hPOS: +4x m', '15:33:16': '_x001A_19-10-08 14:48:58Flow 0 m3/hPOS: +6x m', '15:33:45': '_x001A_19-10-08 14:48:58Flow 0 m3/hPOS: +9x m'}
re.compile('(\\d+-\\d+-\\d+) (\\d+:\\d+:\\d+).*?Flow (\\d+).*?POS:.*?(\\d+)')
new data: []

[o5a]

dimorexa, пока будет возвращать new_data [] соответственно и писать в Лист2 ничего не будет. А вот не заполняет его потому, что регексп почему-то у тебя не считается (цикл по log.items()). Попробуй подобавляй вывод значений key, val, m, может понятнее будет, почему регексп не определяет. Может какая-то особенность версии питона, или модулей, или из-за символа в поле перед датой, хотя я не вижу, как он может мешать.

Answer 2

[Джон]

Как вариант - Разбить текст по столбцам. Сначала разделитель >
потом разделитель ПРОБЕЛ

Далее просто удалить столбцы ненужные со сдвигом влево. Это, чтобы подтянуть 0 4