Import multiple tables from one Excel sheet

This tutorial shows how to import an Excel file that contains multiple tables on one sheet as multiple dataframes in Mito.

Step 1: Open the file in Excel

Notice that the Excel file has multiple tables in one sheet. When working with dataframes in Python, its best practice to represent each of these tables as one dataframe. In this analysis we'll have one dataframe for the Trending Stocks table and another for the Top Movers table.

To follow along, download the data here:

15KB

Stocks.xlsx

Open

Step 2: Select the Excel file in the Mito file browser

Use the Mito file browser to navigate to select the Excel file. Select it just like you would to import any other file in Mito.

Step 3: Import multiple dataframes from one sheet

In the Excel import configuration screen, select that you'd like to import multiple ranges from the sheet. It will open a new taskpane.

This configuration option is only available when importing a single sheet from the Excel file. If you need to import multiple tables from multiple sheets in the Excel file, you'll need to import one sheet at a time.

Step 4: Select the first table from the sheet

Use the taskpane to select which ranges from the sheet you'd like to import as separate tables.

• The first table in the Excel sheet has the title Trending Stocks, so we'll use that as the dataframe name.

• Since we're not sure if the table is always located in the same exact position within the Excel file, we'll choose to locate the dataframe by the value in the upper left hand corner of the table. For this first table, that value is Trending Stock Company Name. This value should always be the first column header in your table.

• Since we see in our Excel file that there are no empty cells in our table, we'll find the end of the table by looking for the first empty cell.

Checkout the Importing Specific Ranges from a single Excel Sheet documentation for a full explanation of all configuration options.

Step 5: Select the second table from the sheet

To import a second range from the Excel file, click the Add button in the taskpane.

Configure the table import like we did previously.

• The second table in the Excel sheet has the title Top Movers, so we'll use that as the dataframe name.

• Since we're still not sure if the table is always located in the same position, we'll again use the upper left hand corner value to identify the start of the table. For this second table, that value is Top Mover Company Name.

• The second table has blank cells in it, so we're going to identify the end of the table using the Bottom Left Corner end condition. In this case, that value is SubTotal.

Step 6: Import the data

Click the import button and scroll through the dataframes to verify they are correct. Notice that the generated code is dynamic -- the next time you run the code, if the original Excel file has more data, it will reidentify the bounds of the table using your configuration and the most up to date data.