如何修复Python XML转CSV时属性值与元素值的列对齐问题

我尝试用Python将XML文件转换为CSV，需要提取两个值，但当前脚本输出的内容无法对应到目标列，而是各自换行。

现有代码 #

import csv
import xml.etree.ElementTree as ET

def main():

    # define input file
    input_dir = r"my\filepath\input.xml"

    # load the xml file
    tree = ET.parse(input_dir)
    root = tree.getroot()

    # open the output csv file in write mode
    csvfile = open(r'my\filepath\output.csv', 'w', newline='')
    csvwriter = csv.writer(csvfile)

    # write the header row
    csvwriter.writerow(['value1', 'value2'])

    # iterate over the CARD elements and write the data rows
    for card in root.iter('CARD'):
        value1= card.find('value1').text
        csvwriter.writerow([value1])

    # Loop through each 'BAR' element and print its 'VALUE2' attribute
    for foo in root.findall('.//BAR'):
        value2= foo.get('VALUE2')
        csvwriter.writerow([value2])

    # close the csv file
    csvfile.close()

main()

期望输出 #

实际输出 #

已尝试的方法 #

1. 预定义列数，但仅添加空值，未解决对齐问题：

# iterate over the CARD elements and write the data rows
for card in root.iter('CARD'):
    value1= card.find('value1').text
    csvwriter.writerow([value1,''])

    # Loop through each 'BAR' element and print its 'VALUE2' attribute
    for foo in root.findall('.//BAR'):
        value2= foo.get('VALUE2')
        csvwriter.writerow(['',value2])

2. 将第二个循环嵌套到第一个中，数据仍未进入目标列：

# iterate over the CARD elements and write the data rows
for card in root.iter('CARD'):
    value1= card.find('value1').text
    csvwriter.writerow([value1])

    # Loop through each 'BAR' element and print its 'VALUE2' attribute
    for foo in root.findall('.//BAR'):
        value2= foo.get('VALUE2')
        csvwriter.writerow([value2])

待转换的XML内容 #

<?xml version="1.0" encoding="ISO-8859-1"?>
<QUERY_IMPORT CREATEIFNOTFOUND="1" ARCHIVEMODE="-1">
<IDENTITY>export</IDENTITY>
<CARD VALUE2="11">
<value9>2</value9>
<r_id>11</r_id>
<value1>name_of_something</value1>
<r_begin>20040101</r_begin>
<r_end>
</r_end><BAR VALUE2="randomvaluestuff">
<VALINTA>1901</VALINTA>
</BAR>
<BAR VALUE2="randomvaluestuff">
<VALINTA>1901</VALINTA>
</BAR>
<BAR VALUE2="randomvaluestuff">
<VALINTA>1901</VALINTA>
</BAR>
<BAR VALUE2="randomvaluestuff">
<VALINTA>1901</VALINTA>
</BAR>
<BAR VALUE2="randomvaluestuff">
<VALINTA>1901</VALINTA>
</BAR>
<BAR VALUE2="randomvaluestuff">
<VALINTA>1901</VALINTA>
</BAR>
<BAR VALUE2="randomvaluestuff">
<VALINTA>1901</VALINTA>
</BAR>
<BAR VALUE2="randomvaluestuff">
<VALINTA>1901</VALINTA>
</BAR>
<BAR VALUE2="randomvaluestuff">
<VALINTA>1901</VALINTA>
</BAR>
<BAR VALUE2="randomvaluestuff">
<VALINTA>1901</VALINTA>
</BAR>
<BAR VALUE2="randomvaluestuff">
<VALINTA>1901</VALINTA>
</BAR>
<BAR VALUE2="randomvaluestuff">
<VALINTA>1901</VALINTA>
</BAR>
<BAR VALUE2="randomvaluestuff">
<VALINTA>1901</VALINTA>
</BAR>
<BAR VALUE2="randomvaluestuff">
<VALINTA>1901</VALINTA>
</BAR>
</CARD>
<CARD VALUE2="12">
<value9>2</value9>
<r_id>12</r_id>
<value1>name_of_other</value1>
<r_begin>20040101</r_begin>
<r_end>
</r_end><BAR VALUE2="randomvaluestuff">
<VALINTA>1901</VALINTA>
</BAR>
<BAR VALUE2="randomvaluestuff">
<VALINTA>1901</VALINTA>
</BAR>
<BAR VALUE2="randomvaluestuff">
<VALINTA>1901</VALINTA>
</BAR>
<BAR VALUE2="randomvaluestuff">
<VALINTA>1901</VALINTA>
</BAR>
<BAR VALUE2="randomvaluestuff">
<VALINTA>1901</VALINTA>
</BAR>
<BAR VALUE2="randomvaluestuff">
<VALINTA>1901</VALINTA>
</BAR>
<BAR VALUE2="randomvaluestuff">
<VALINTA>1901</VALINTA>
</BAR>
<BAR VALUE2="randomvaluestuff">
<VALINTA>1901</VALINTA>
</BAR>
<BAR VALUE2="randomvaluestuff">
<VALINTA>1901</VALINTA>
</BAR>
<BAR VALUE2="randomvaluestuff">
<VALINTA>1901</VALINTA>
</BAR>
<BAR VALUE2="randomvaluestuff">
<VALINTA>1901</VALINTA>
</BAR>
<BAR VALUE2="randomvaluestuff">
<VALINTA>1901</VALINTA>
</BAR>
<BAR VALUE2="randomvaluestuff">
<VALINTA>1901</VALINTA>
</BAR>
<BAR VALUE2="randomvaluestuff">
<VALINTA>1901</VALINTA>
</BAR>
<BAR VALUE2="randomvaluestuff">
<VALINTA>1901</VALINTA>
</BAR>
<BAR VALUE2="randomvaluestuff">
<VALINTA>1901</VALINTA>
</BAR>
<BAR VALUE2="randomvaluestuff">
<VALINTA>1901</VALINTA>
</BAR>
<BAR VALUE2="randomvaluestuff">
<VALINTA>1901</VALINTA>
</BAR>
<BAR VALUE2="randomvaluestuff">
<VALINTA>1901</VALINTA>
</BAR>
<BAR VALUE2="randomvaluestuff">
<VALINTA>1901</VALINTA>
</BAR>
<BAR VALUE2="randomvaluestuff">
<VALINTA>1901</VALINTA>
</BAR>
<BAR VALUE2="randomvaluestuff">
<VALINTA>1901</VALINTA>
</BAR>
<BAR VALUE2="randomvaluestuff">
<VALINTA>1901</VALINTA>
</BAR>
<BAR VALUE2="randomvaluestuff">
<VALINTA>1901</VALINTA>
</BAR>
<BAR VALUE2="randomvaluestuff">
<VALINTA>1901</VALINTA>
</BAR>
<BAR VALUE2="randomvaluestuff">
<VALINTA>1901</VALINTA>
</BAR>
</CARD>

从XML结构来看，每个<CARD>节点下包含一个<value1>和多个<BAR>节点，每个<BAR>有VALUE2属性。需根据你想要的关联关系选择对应的处理方式：

方式1：每个CARD对应一行，取第一个BAR的VALUE2 #

遍历每个CARD时，提取其下的第一个BAR的VALUE2，将两个值放在同一行写入：

import csv
import xml.etree.ElementTree as ET

def main():
    input_dir = r"my\filepath\input.xml"
    tree = ET.parse(input_dir)
    root = tree.getroot()

    with open(r'my\filepath\output.csv', 'w', newline='') as csvfile:
        csvwriter = csv.writer(csvfile)
        csvwriter.writerow(['value1', 'value2'])

        # 遍历每个CARD节点
        for card in root.iter('CARD'):
            value1 = card.find('value1').text
            # 获取当前CARD下的第一个BAR节点
            bar = card.find('BAR')
            value2 = bar.get('VALUE2') if bar else ''
            # 同一行写入两个值
            csvwriter.writerow([value1, value2])

main()

方式2：每个BAR对应一行，关联所属CARD的value1 #

如果需要每个BAR单独占一行，同时显示所属CARD的value1，遍历BAR时找到其父节点CARD提取对应值：

import csv
import xml.etree.ElementTree as ET

def main():
    input_dir = r"my\filepath\input.xml"
    tree = ET.parse(input_dir)
    root = tree.getroot()

    with open(r'my\filepath\output.csv', 'w', newline='') as csvfile:
        csvwriter = csv.writer(csvfile)
        csvwriter.writerow(['value1', 'value2'])

        # 遍历每个BAR节点
        for bar in root.findall('.//BAR'):
            # 找到BAR的父节点CARD
            card = bar.find('../')
            value1 = card.find('value1').text if card else ''
            value2 = bar.get('VALUE2')
            # 同一行写入关联值
            csvwriter.writerow([value1, value2])

main()

问题根源说明 #

原代码的核心问题是：

• 两次独立循环分别写入value1和value2，导致两者各自占一行
• 嵌套循环时，每次遍历CARD就写入一次value1，再遍历所有BAR写入value2，造成数据重复且错位
• 正确逻辑是将关联的value1和value2放在同一行写入，需先明确两者的对应关系，再按关系组织数据。

内容的提问来源于stack exchange，提问作者enor