Время прочтения: 3 мин.

Часто случается так, что данные с интернет ресурсов/внутренних корпоративных систем попадают к нам в виде xml-файлов. И хорошо, когда эти файлы читаются/помещаются в excel и скорость работы Вас удовлетворяет. Но что делать, если данных очень много, excel вываливается с ошибкой, ну или даже простой =ВПР() при дальнейшей отработке предлагает Вам выпить кофе, пока он обрабатывает данные, попутно фактически блокируя работу компьютера, ну а Pandas попросту не воспринимает документ как таблицу и тоже отказывается работать с ним? Данная статья позволит Вам достаточно оперативно обработать такого рода xml-документы.

Пример такого xml-документа — ниже:

<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:v="urn:schemas-microsoft-com:vml"
 xmlns:o="urn:schemas-microsoft-com:office:office"
 xmlns:x="urn:schemas-microsoft-com:office:excel"
….
<Worksheet ss:Name="Sheet1">
  <Table ss:ExpandedColumnCount="29" ss:ExpandedRowCount="72319" x:FullColumns="1"
   x:FullRows="1">
   <Column ss:StyleID="s62" ss:AutoFitWidth="0" ss:Span="4"/>
   <Column ss:Index="6" ss:StyleID="s62" ss:Width="53.25"/>
   <Column ss:StyleID="s62" ss:AutoFitWidth="0" ss:Span="19"/>
   <Column ss:Index="27" ss:StyleID="s62" ss:Width="53.25" ss:Span="1"/>
   <Column ss:Index="29" ss:StyleID="s62" ss:AutoFitWidth="0"/>
   <Row>
<Row>
    <Cell><Data ss:Type="String">123945</Data></Cell>
    <Cell><Data ss:Type="String"Иванов Иван Иванович </Data></Cell>
...
    <Cell ss:Index="26"><Data ss:Type="String">пример текста</Data></Cell>
    <Cell ss:StyleID="s64"><Data ss:Type="DateTime">2008-10-06T00:00:00.000</Data></Cell>
    <Cell ss:StyleID="s64"><Data ss:Type="DateTime">1998-07-23T00:00:00.000</Data></Cell>
    <Cell><Data ss:Type="String">111222</Data></Cell>
   </Row>
…

В коде видно, что в шапке файла явно указана схема, на основе которой спроектирован xml-файл — xmlns:x=»urn:schemas-microsoft-com:office:excel». Стандартной загрузки такого рода xml-документов в Pandas на текущий момент не существует.

Я покажу, как с помощью стандартной библиотеки xml.etree.ElementTree загрузить данные или в Pandas, или в SQL-таблицу (в нашем случае — в БД Oracle) для дальнейшего анализа.

Импортируем необходимые модули:

import pandas as pd
import time
import datetime as dtm
import xml.etree.ElementTree as etree
import cx_Oracle

Загружаем xml-файл:

tree = etree.parse("D:\IN\File.xml")
root = tree.getroot()

Проверяем, что все загружено как надо:

for child in root:
    print(child.tag, child.keys(), child.items())

Данный код возвращает результат в формате ключ-значение:

{urn:schemas-microsoft-com:office:office}DocumentProperties [] []
{urn:schemas-microsoft-com:office:office}OfficeDocumentSettings [] []
{urn:schemas-microsoft-com:office:excel}ExcelWorkbook [] []
{urn:schemas-microsoft-com:office:spreadsheet}Styles [] []
{urn:schemas-microsoft-com:office:spreadsheet}Worksheet ['{urn:schemas-microsoft-com:office:spreadsheet}Name'] [('{urn:schemas-microsoft-com:office:spreadsheet}Name', 'Sheet1')]
{urn:schemas-microsoft-com:office:spreadsheet}Worksheet ['{urn:schemas-microsoft-com:office:spreadsheet}Name'] [('{urn:schemas-microsoft-com:office:spreadsheet}Name', 'Sheet2')]
{urn:schemas-microsoft-com:office:spreadsheet}Worksheet ['{urn:schemas-microsoft-com:office:spreadsheet}Name'] [('{urn:schemas-microsoft-com:office:spreadsheet}Name', 'Sheet3')]

Далее приступаем к этапу обработки и загрузки данных. С помощью XPath выбираем все элементы из тега Row:

for child in root.iterfind('.//{urn:schemas-microsoft-com:office:spreadsheet}Row')

В самом xml-файле есть пустые поля и в следующем непустом поле с тегом Cell будет присутствовать атрибут Index с номером текущего поля <Cell ss:Index=»26″>:

if(str(cell.attrib.keys()).find('Index') > 0):
            indx = int((cell.attrib.get('{urn:schemas-microsoft-com:office:spreadsheet}Index')))

Записываем данные в БД:

if(kol_zp > 1):
v_cursor.execute(query_inz, [cell_r[0], cell_r[1], cell_r[2], cell_r[3], cell_r[4], dtm.datetime.strptime(cell_r[5], "%Y-%m-%dT%H:%M:%S.%f"), cell_r[6], cell_r[7], cell_r[8], cell_r[9], cell_r[10], cell_r[11], cell_r[12], cell_r[13], cell_r[14], cell_r[15], cell_r[16], cell_r[17], cell_r[18], cell_r[19], cell_r[20], cell_r[21], cell_r[22], cell_r[23], cell_r[24], cell_r[25], dtm.datetime.strptime(cell_r[26], "%Y-%m-%dT%H:%M:%S.%f"), dtm.datetime.strptime(cell_r[27], "%Y-%m-%dT%H:%M:%S.%f"), cell_r[28]])

Если дата нужна в формате даты/времени – используем стандартный python-модуль datetime. В противном случае можно оставить атрибут в текстовом формате, а преобразование выполнять уже в конечной системе. Например, в базе данных. Я корректирую форматы атрибутов сразу:

dtm.datetime.strptime(cell_r[26], "%Y-%m-%dT%H:%M:%S.%f")

Если есть необходимость проводить дальнейший анализ в Pandas — импортируем полученный список списков в DataFrame:

df = pd.DataFrame(cell_cell)
df.head()

Готово! Данные готовы для дальнейшей отработки. Если данные импортируются в БД, то не забываем закрывать соединение.

v_cursor.close()
v_connect.close()
print(f'Выполнение за: {time.time() - t0:.1f} sec')

Полный блок обработки xml-документа приведен ниже:

#Главная
run_time = time.time()
kol_zp=0
#список списков
cell_cell = []
for child in root.iterfind('.//{urn:schemas-microsoft-com:office:spreadsheet}Row'):
    kol_zp += 1
    cell_r = []
    row_children = child.getchildren()
    #
    con_cl=0
    for cell in row_children:
        con_cl += 1
        if(str(cell.attrib.keys()).find('Index') > 0):
            indx = int((cell.attrib.get('{urn:schemas-microsoft-com:office:spreadsheet}Index')))
            if(con_cl < indx):
                rz_c = indx-con_cl
                for i in range(rz_c):
                    cell_r.append('0')
                    con_cl += 1
        cell_children = cell.getchildren()
        for cell_sh in cell_children:
            cell_r.append(cell_sh.text)
            #cell_r.append(con_cl)
    #список списков
    cell_cell.append(cell_r)
    if(kol_zp > 1):
        v_cursor.execute(query_inz, [cell_r[0], cell_r[1], cell_r[2], cell_r[3], cell_r[4], 
                                 dtm.datetime.strptime(cell_r[5], "%Y-%m-%dT%H:%M:%S.%f"), cell_r[6], cell_r[7], cell_r[8], cell_r[9], 
                                 cell_r[10], cell_r[11], cell_r[12], cell_r[13], cell_r[14], 
                                 cell_r[15], cell_r[16], cell_r[17], cell_r[18], cell_r[19], 
                                 cell_r[20], cell_r[21], cell_r[22], cell_r[23], cell_r[24], 
                                 cell_r[25], dtm.datetime.strptime(cell_r[26], "%Y-%m-%dT%H:%M:%S.%f"), 
                                 dtm.datetime.strptime(cell_r[27], "%Y-%m-%dT%H:%M:%S.%f"), cell_r[28]
                                ])
#Ограниечение на количество записей
    if(kol_zp == 20):
        break
#
v_connect.commit()