When you need to iterate over files in folder with similar names one of the ways could be make a pattern and use glob. To make sure that returned path is file you can use isfile().

Regarding XML, I see that basically you need to write values of every terminal tag in column with name of this tag. As you have various files you can create tag-value dictionaries from each file and store them into ChainMap. After all files processed you can use DictWriter to write all data into final csv file.

This method is much more safe and flexible then use static column names. Firstly program will collect all possible tag(column) names from all files, so in case if XML doesn't have such a tag or have some extra tags it won't throw an exception and all data will be saved.

Code:

import xml.etree.ElementTree as ET
from glob import iglob
from os.path import isfile, join
from csv import DictWriter
from collections import ChainMap

xml_root = r"C:\data\Desktop\Blue\XML-files"
pattern = "xmlfile_*"
data = ChainMap()
for filename in iglob(join(xml_root, pattern)):
    if isfile(filename):
        tree = ET.parse(filename)
        root = tree.getroot()
        temp = {node.tag: node.text for node in root.iter() if not node}
        data = data.new_child(temp)

with open(join(xml_root, "data.csv"), "w", newline="") as f:
    writer = DictWriter(f, data)
    writer.writeheader()
    writer.writerows(data.maps[:-1])  # last is empty dict

Upd. If you want to use xlsx format instead of csv you have to use third-party library (e.g. openpyxl). Example of usage:

from openpyxl import Workbook

...

wb = Workbook(write_only=True)
ws = wb.create_sheet()
ws.append(list(data))  # write header
for row in data.maps[:-1]:
    ws.append([row.get(key, "") for key in data])
wb.save(join(xml_root, "data.xlsx"))

MattDMo has given a sufficient answer to the problem, but I just want to remind you that python has a csv module which makes it easier to write comma separated data, which is typically then read into applications such as databases or spreadsheets.

From the docs:

import csv
with open('eggs.csv', 'wb') as csvfile:
    spamwriter = csv.writer(csvfile, delimiter=' ',
                        quotechar='|', quoting=csv.QUOTE_MINIMAL)
    spamwriter.writerow(['Spam'] * 5 + ['Baked Beans'])
    spamwriter.writerow(['Spam', 'Lovely Spam', 'Wonderful Spam'])

I recommend just using libraries because they're usually very optimised. I'll talk about that later. For now, here's a way that utilises the xml.dom.minidom module, which is a part of the Python standard library, so no additional libraries are required.

Edit: rewrote the last part using the standard CSV library instead of manually writing the file, as suggested by a comment. That makes for 2 Python built-in modules, not 1. The original code for the CSV writing will be at the end of the reply, if you're interested.

from xml.dom import minidom
from csv import DictWriter

# Step 1: Read and parse the XML file
# Write it as a string, or open the file and read it
xml_file = open('employees.xml', 'r')
xml_data = xml_file.read()

dom = minidom.parseString(xml_data)
employees = dom.getElementsByTagName('employee')

xml_file.close()

# Step 2: Extract the required information
data = []
for employee in employees:
    emp_data = {}
    for child in employee.childNodes:
        if child.nodeType == minidom.Node.ELEMENT_NODE:
            emp_data[child.tagName] = child.firstChild.data
    data.append(emp_data)

# Step 3: Write the extracted information to a CSV file
with open('output.csv', 'w', newline = '') as csv_file:
    fieldnames = ['id', 'name', 'age', 'salary', 'division']
    writer = DictWriter(csv_file, fieldnames = fieldnames)

    writer.writeheader()
    for emp_data in data:
        writer.writerow(emp_data)

Don't reinvent the wheel, just realign it.

— Anthony J. D'Angelo, I think

I recommend NOT using this code. You should really just use lxml. It's extremely simple and easy to use and can handle complex XML structures with nested elements and attributes. Let me know how everything goes!

Original CSV write code without CSV library

# Step 3: Write the extracted information to a CSV file
with open('output.csv', 'w') as f:
    f.write('id,name,age,salary,division\n')
    for emp_data in data:
        f.write(f"{emp_data['id']},{emp_data['name']},{emp_data['age']},{emp_data['salary']},{emp_data['division']}\n")