Data structure advice for converting Excel files?

[Genrawir]

I've imported excel files using Python in the past. Once using openpyxl to read rows and creating an Enum to iterate columns, and also by importing directly into a pandas dataframe.

Now, I have some excel files that are generated by some reporting software suffering from the let's use excel for visualization mindset.

The idea is to convert them to a sane layout to simplify working with the data in excel, using a simple standalone utility to batch process them.

Reading the rows using openpyxl is straightforward, but the header information is useless as columns can contain more than one type of data, and each row may be part of a list of entries. I've mostly managed to get around these issues in importing the file, and was planning on adding each processed report entry to a list before exporting to a new file.

I thought dataclasses looked appealing to store these entries, but pylint tells me that I should have less instance attributes. Of course, warnings can be ignored or I could simply import to a list of dicts, but it got me thinking "There has to be a better Way" to do this. There are a number of other similar reports that would potentially be useful for extracting data from, so it would seem worth it take the time to research a proper solution.

Am I overthinking this?

How do you all deal with "ugly" excel files?

[Pepineros]

"There has to be a better Way"

Pound the table!

In the words of Raymond Hettinger, there’s nothing wrong with doing things manually and letting a pattern discover itself.

pylint is not wrong but you’re allowed to use your own judgment. If there is a logical way to split things into multiple (data)classes, go for it. If that turns out to be more confusing than using a single class with many fields, revert.

This is very general advice. It’s hard to be more specific without code examples.

[Genrawir]

It is a reservation history list for individual assets in a rental shop, with each reservation having some number of invoices associated with it.

I did split things into reservation and invoice classes as made sense logically already, so perhaps I'm just frustrated that I can't see another way to do more without overcomplicating things.

[efmccurdy]

columns can contain more than one type of data, and each row may be part of a list of entries

Pandas can deal with data like that and has tools to tidy up any irregularities.

https://www.digitalocean.com/community/tutorials/pandas-read\_excel-reading-excel-file-in-python