Extract excel ranges into a dataframe in python

user_18108067 0
Viewed 71

I have a working excel sheet that does not contain any tables. Instead it has multiple sections of data. I want to extract certain ranges of cells from this sheet and create a new data source that can be used to create Power BI reports.

Examples of the ranges are:

    range1 = ws['A5':'N7']
    range2 = ws['A12':'N13']
    range3 = ws['A17':'N20']
    range4 = ws['A33':'N35']
    range5 = ws['A41':'N42']

When I print the values of these ranges using Python and openpyxl I get a long list of values which I would like to transform into a new dataframe with custom column headers.

How do I transform that list into a table that I can then either export to an excel or into a sql database?

Thank you

dataframe excel sql python
1 Answers

I'd use pandas.read_excel as documented here. Note the usecols, skiprows, and nrows to select the ranges. Simply call it multiple times to access different ranges.

Subsequently, I'd use pandas.to_excel (and other to_... functions) to export the dataframe to the appropriate format.

Personally, I only use openpyxl directly when I need to optimize performance, or when I can't install pandas.

user_22186300
Related