One possibility is a streaming XSLT 3.0 processor, which given your constraints means in practice Saxon/C Enterprise Edition (this has a Python language binding).
There is actually a CSV-to-XML stylesheet published as a worked example in the XSLT 3.0 specification, but sadly no counterpart to do the reverse. However, you can see the principle in some of the ...
Although not free, there's a cheap (~5$) option - Data Transformer (disclaimer - I'm its developer). It converts between CSV, JSON, XML, and YML locally.
It offers a number of conversion settings (with sensible defaults) so you can match the data for your purposes.
You can check it out on the Mac App Store or the Microsoft Store.
The XML Utilities library is worth a try, assuming valid & flat XML structure - it even comes with a command line xml2csv utility.
It specifically states:
xmlutils.py is a set of Python utilities for processing xml files
serially for converting them to various formats (SQL, CSV, JSON). The
scripts use ElementTree.iterparse() to iterate through ...
I'd suggest you look at Data Transformer (disclaimer - I'm its developer). It can convert CSV/JSON/XML to SQL. The generated SQL contains "insert" statements for each line and a "create table" statement.
The app works offline, and your data never leaves your computer.
You can get it from the Mac App Store or the Microsoft Store.
I would suggest keeping an eye out for pandas official documentation on IO. One's option keeps changing based on the development cycle and new formats get added all time. They also publish the benchmark.
I managed to get it working just using power query, though now I need to figure out how to automate the process of creating a CSV from this new data.
I might end up using your solution if this turns out to be able to help me in this respect.
You can normalize crosstabbed data in the way that you want using a Python program named un-xtab.py. It can be installed from Python Package Index (PyPI) at https://pypi.org/project/un-xtab/. The documenation is in the file un-xtab.html in the doc directory of the Bitbucket repository at https://bitbucket.org/rdnielsen/un-xtab/src/default/.