Excel jobs in Mitto can be used to create database tables from individual sheets in an Excel (xls, xlsx, etc) file.

Upload the Excel file:

Click on the Files menu item in Mitto.

Click Add File or drag and drop your file.

Now that the file exists on the Mitto box, you can use it in a Mitto job.

Create an Excel job

Currently the Excel job does not have a wizard interface, so a custom job needs to be created and the job configured with JSON.

Click on any existing job and Duplicate it.

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.