Overview

The Excel plugin enables Mitto to pipe data from Excel and store the data in a database.

Create an Excel job

Click + Add Job.

Choose Excel.

Fill out the forms.

  • Source - Choose the Excel file you want to pipe data from. The Excel file must exist in the Mitto file manager.

Click Next.

  • Sheet - This is the tab/sheet in the Excel file. You will only create 1 table in the database from 1 sheet in the Excel file. In the Excel plugin wizard, the list of sheets will be available in the dropbox. In the resulting Excel job, sheet can either use the name of the sheet as a string or the index of the sheet as an integer (0 is the first sheet).
  • Start Row - This is optional, but allows you to skip rows in the Excel file. 0 is the first row. 0 by default.
  • Start Column - This is optional, but allows you to skip columns in the Excel file. 0 is the first column. 0 by default.
  • Include Header - Check this if the data has a header. Checked by Default.
  • Noneify - Check this if you want to convert empty values to NULL. Checked by default.

Click Next.

  • Title - This is the resulting job's title.

Give the new job a title. Best practice is to have the below naming convention:

So if the name of the Excel file is Orders.xlsx and the name of the tab in the file is Sheet1 then the name of the job should be: [Excel] Orders.xlsx - Sheet 1.

Tag the job with excel.  This is for organization of your jobs.

Make sure the type is io.

Configure the Excel Job

Edit the job.

Delete the existing configuration.

Copy and paste this template job configuration as a starting point:

{
    "input": {
        "sheet": "Sheet1",
        "source": "Orders.xlsx",
        "start_column": 0,
        "start_row": 0,
        "use": "flatfile.iov2#ExcelInput"
    },
    "output": {
        "dbo": "postgresql://localhost/analytics",
        "schema": "excel",
        "tablename": "orders_sheet1",
        "use": "call:mitto.iov2.db#todb"
    },
    "steps": [
        {
            "transforms": [
                {
                    "use": "mitto.iov2.transform#ExtraColumnsTransform"
                },
                {
                    "use": "mitto.iov2.transform#ColumnsTransform"
                }
            ],
            "use": "mitto.iov2.steps#Input"
        },
        {
            "use": "mitto.iov2.steps#CreateTable"
        },
        {
            "transforms": [
                {
                    "use": "mitto.iov2.transform#FlattenTransform"
                }
            ],
            "use": "mitto.iov2.steps#Output"
        },
        {
            "use": "mitto.iov2.steps#CountTable"
        }
    ]
}

The main sections to edit are input and output.

Input:

source: This is the Excel file on the Mitto box.

sheet: This is the tab/sheet in the Excel file. You will only create 1 table in the database from 1 sheet in the Excel file. sheet can either use the name of the sheet as a string or the index of the sheet as an integer (0 is the first sheet).

start_column: This is optional, but allows you to skip columns in the Excel file. 0 is the first column.

start_row: This is optional, but allows you to skip rows in the Excel file. 0 is the first row.

Output:

dbo: This is the connection string of the output database. If you are outputting data into Mitto's internal PostgreSQL database, leave this as is.

schema: This is the output schema in the database.

table: This is the output table in the database.

Save and run the Excel job

Click Save.

Click Start.

The data from the sheet in the Excel file is now a table in the database.