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.
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:
Stocks.xlsx
15KB
Binary
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.
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.
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.
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 Cornerend condition. In this case, that value isSubTotal.
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.
Last modified 6mo ago