Overview

Mitto has the ability to ingest .accdb files and output the data into a desired database location. You must have the Microsoft Access plugin installed on your Mitto instance. Create a Zuar support ticket if you need assistance.

Mitto + Microsoft Access

.accdb file

Mitto requires that the .accdb file be accessible within the Mitto file store . There are many ways to get the file into Mitto's file store like using Rclone from a storage location to Mitto, uploading/dropping the file into Mitto's file page, etc.

Default Job Setup

Meta Table

Once the file exists in Mitto's file store, you first need to create a meta table which will include a single column and 1 row per table name from the .accdb file. Create a Generic Job from the Add Job menu.

Mitto Generic Job Icon
  • Give the job a Title like [Microsoft Access Meta] Table Names
  • Set the Type to io
  • Paste the following configuration into the job, change the path to the location and name of your .accdb file, adjust the schema and tablename output to your desired info:
{
  input: {
    path: /var/mitto/data/{yourfilename.accdb}
    use: access.iov2#AccessTableNameInput
  }
  output: {
    dbo: postgresql://localhost/analytics
    schema: microsoft_access_meta
    tablename: table_names
    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#CollectMeta
    }
  ]
}

Database Tables

Next, create individual io jobs to input individual tables from the .accdb file into your desired database.

  • Follow the steps above to create a new io job
  • Name the job something like [Microsoft Access] {Name of Table}
  • Use the following configuration (adjust the input table_name to match the table name in the meta table and .accdb file, edit the output details to your desired location, schema, and table name):
{
  input: {
    path: /var/mitto/data/{yourfilename.accdb}
    table_name: {table name}
    use: access.iov2#AccessInput
  }
  output: {
    dbo: postgresql://localhost/analytics
    schema: microsoft_access
    tablename: example_table
    use: call:mitto.iov2.db#todb
  }
  steps: [
    {
      transforms: [
        {
          rename_columns: false
          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#CollectMeta
    }
  ]
}