xlextract 0.2.4

Extract data from Excel files

XLExtract

An abstraction layer for quickly pulling data out of Microsoft Excel Spreadsheets.

The project is currently powered by the openpyxl and pandas libraries but you don't need to know anything about those underlying libraries and they could change in the future.

Installation

xlextract can be installed via poetry with: poetry add xlextract
or via pip with: pip install xlextract

What does it do?

xlextract searches a spreadsheet for a keyword you provide and extracts nearby data.
It provides three operations:

1. Right Lookup: Extract cell value to the immediate right of the keyword
2. Left Lookup: Extract cell value to the immediate left of the keyword
3. Bottom Lookup: Extract cell value immediateley below the keyword
4. Table Lookup: Extract an entire Excel table of data adjacent to the keyword. The table is modeled as a list of dictionaries where each list item represents a row in the table and the list item is a dictionary (key/value pairs) where each item in the dictionary represents a column of the row and where the key is the column header. An example will be provided below.

How do I use it in my project?

You need four bits of information to use xlextract:

1. The name of the Excel file
2. The name of the sheet in the Excel file
3. The keyword you want to search for
4. The type of lookup you want to do (Right, Left, Bottom, or Table)

The project provides a class named XLExtract that requires the first 3 inputs above as strings.
The lookup is done via one of four class methods:

1. RLookup() (Right Lookup)
2. LLookup() (Left Lookup)
3. BLookup() (Bottom Lookup)
4. TLookup() (Table Lookup)

Here is an example of how to import the library and create a sheet object ready for lookup:

import json # This is just to print our example table with formatting

from xlextract import XLExtract

vrf_table = XLExtract("design_document.xlsx", "Tenants", "VRF NAME")

Table Lookup Example

Assuming we have an Excel spreadsheet with a "Tenants" tab that contains a table that looks like this:
If we then do this in our Python code:

vrf_table.TLookup()

print(json.dumps(vrf_table.value, indent = 4))

We would get the following output:

[
    {
        "TENANT": "Prod-tn",
        "VRF NAME": "Prod-VRF",
        "ENABLE PREFERRED GROUP": "enabled"
    },
    {
        "TENANT": "Prod-tn",
        "VRF NAME": "Dev-VRF",
        "ENABLE PREFERRED GROUP": "enabled"
    }
]

CAVEATS

The source Excel spreadsheet content should be planned ahead of time to account for these operational caveats:

• Keywords on a given sheet should be unique (the first instance found is used)
• Keywords for a table must be one of the column headers (keyword defines the header row)
• Keyword columns are assumed to be required values. Any rows that do not have a value for the keyword column will be filtered out.