Microsoft Access¶

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

.accdb/.mdb file¶

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

Meta Table¶

Once the file exists in Zuar Runner’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 or .mdb file. Create a Generic Job from the Add Job menu.

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 or .mdb 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://mitto:{password}@db/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://mitto:{password}@db/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
    }
  ]
}