python - 使用Python将子节点输出到CSV

Question

编辑:我已将示例 XML 替换为真实数据，并在底部提供了我的代码。

我有几个 xml 文件，其中包含 1 到 10 多行以下数据:

<?xml version="1.0" encoding="UTF-8"?>
<Invoice xmlns="urn:oasis:names:specification:ubl:schema:xsd:Invoice-2" xmlns:cac="urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2" xmlns:cbc="urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2" xmlns:cec="urn:oasis:names:specification:ubl:schema:xsd:CommonExtensionComponents-2" xmlns:soapenv="http://www.w3.org/2003/05/soap-envelope" xmlns:wsu="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd" xmlns:xenc="http://www.w3.org/2001/04/xmlenc#" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="urn:oasis:names:specification:ubl:schema:xsd:Invoice-2 UBL-Invoice-2.0.xsd">
   <cac:LegalMonetaryTotal>
      <cbc:PayableAmount currencyID="DKK">2586.61</cbc:PayableAmount>
   </cac:LegalMonetaryTotal>
   <cac:InvoiceLine>
      <cbc:ID>1</cbc:ID>
      <cbc:InvoicedQuantity unitCode="HUR">1.50</cbc:InvoicedQuantity>
      <cbc:LineExtensionAmount currencyID="DKK">1633.65</cbc:LineExtensionAmount>
   </cac:InvoiceLine>
   <cac:InvoiceLine>
      <cbc:ID>2</cbc:ID>
      <cbc:InvoicedQuantity unitCode="HUR">1.00</cbc:InvoicedQuantity>
      <cbc:LineExtensionAmount currencyID="DKK">952.96</cbc:LineExtensionAmount>
   </cac:InvoiceLine>
</Invoice>

我想将数据输出到以下结构的 CSV 文件中:

filename,lineId,lineQuantity,lineAmount,payableAmount
file1,1,1.50,1633.65,2586.61
file1,2,1.00,952.96,2586.61
file2,.,.,.

...每个文件的每一行都有一行以及文件名和总量。

这是我的代码:

from os import listdir, path, walk
import xml.etree.ElementTree as ET
import csv

def invoicelines(self):
    filename = path.splitext(path.split(file)[1])[0]
    lineId = root.find('./InvoiceLine/ID').text
    lineQuantity = root.find('./InvoiceLine/InvoicedQuantity').text
    lineAmount = root.find('./InvoiceLine/LineExtensionAmount').text
    payableAmount = root.find('./LegalMonetaryTotal/PayableAmount').text

    row = [
        filename,
        lineId,
        lineQuantity,
        lineAmount,
        payableAmount
        ]

    return row

csvfile = 'output.csv'

def csv_write_header(csvfile):
     with open(csvfile, 'w', newline='') as outfile:
          writer = csv.writer(outfile)
          writer.writerow([
               'filename',
               'lineId',
               'lineQuantity',
               'lineAmount',
               'payableAmount'
               ])

xml_files = []
for root, dirs, files in walk('mypath'):
    for file in files:
        if file.endswith('.xml'):
            xml_files.append(path.join(root, file))
            csv_write_header(csvfile)

for file in xml_files:
    tree = ET.iterparse(file)
    for _, el in tree:
        el.tag = el.tag.split('}', 1)[1] # ignores namespaces
    root = tree.root
    if 'Invoice' in root.tag: # only invoice files
        for e in root.iter('InvoiceLine'):
            with open(csvfile, 'a', newline='') as outfile:
                writer = csv.writer(outfile)
                writer.writerow(invoicelines(e))

如果我只是解析上面的文件，我得到的输出是:

filename,lineId,lineQuantity,lineAmount,payableAmount
file1,1,1.50,1633.65,2586.61
file1,1,1.50,1633.65,2586.61

...所以我猜这与我的迭代有关。

Answer 1

下面的代码可以达到您想要的结果。

import os
import xml.etree.ElementTree as ET


def extract_line_id_data(line_element):
    line_id = line_element[0].text
    quantity = line_element[1].text
    line_amount = line_element[2].text

    return line_id, quantity, line_amount


# Iterate over all files in a directory
for _, dirs, files in os.walk('/path/to_folder/with/xml_files/'):
    with open('output.csv', 'a') as output:
        output.write('Filename,LineID,Quantity,LineAmount,TotalAmount\n')  # Headers
        for xml_file in files:
            # If not all files in the folder files are XML you'll need to catch an exception here
            tree = ET.parse(xml_file)  # might need to use os.path.abspath
            root = tree.getroot()
            total_amount = root[0][0].text  # Get total amount value
            # Iterate over all "Line" elements
            for e in root[1:]:
                output.write('{},{},{},{},{}\n'.format(xml_file, * extract_line_id_data(e), total_amount))

使用您的文件和 TotalAmount 为 350 的“file2.xml”进行测试，输出如下所示:

Filename,LineID,Quantity,LineAmount,TotalAmount
file.xml,1,4,132,407
file.xml,2,1,72,407
file.xml,3,7,203,407
file2.xml,1,4,132,350
file2.xml,2,1,72,350
file2.xml,3,7,203,350

我希望这对你有用。尽管我自己会使用 lxml，但我还是首选使用 ElementTree。