Regex For Excel file where data is in the same sheet always

Runner
#1

I have a use case where we need to create a regex type job (similar to the csv regex job type) for Excel files. The files have a common naming convention and the data needed is on the second sheet.

#2

Hey @Tyler!

Creating a RegEx io job with an Excel input is the same process as creating a RegEx io job with a CSV input. See the RegEx help page for instructions.

Mitto currently supports loading a single sheet/tab from multiple Excel files. The name or index of the Excel sheet/tab would need to be consistent across all the Excel files.
There is not currently Mitto support for loading multiple Excel tabs from a single Excel file (or multiple Excel files), but there is a feature/improvement request for this.

1 Like
#3

I have the job configured for Regex but am messing something up on the Input

Blockquote
TypeError: init() got an unexpected keyword argument ‘sheet’

Blockquote
input: {
sheet: Calls
start_row: 0
start_column: 0
includes_header: true
noneify: true
regex: .+export.+.xlsx
use: flatfile.iov2#RegexInput
base: xsv.iov2#XsvInput2
}

I checked the excel file and the sheet name is correct.

#4

Hey @Tyler,

Here’s an example that worked for me.

The two excel files are:

  • regex_excel_1.xlsx
  • regex_excel_2.xlsx

The data in each Excel file is on the “Sheet1” tab and looks like this:

id,name
1,Justin

id,name
2,Andy

Here’s the job config:

{
  input: {
    regex: ^regex_excel_.+\.xlsx$
    sheet: Sheet1
    start_row: 0
    start_column: 0
    includes_header: true
    noneify: true
    base: flatfile.iov2#ExcelInput
    use: flatfile.iov2#RegexInput
  }
  output: {
    dbo: postgresql://localhost/analytics
    schema: test
    tablename: regex_excel
    use: call:mitto.iov2.db#todb
  }
  steps: [
    {
      column: __mtime__
      use: mitto.iov2.steps#MaxTimestamp
    }
    {
      use: mitto.iov2.steps.upsert#SetUpdatedAt
    }
    {
      transforms: [
        {
          use: mitto.iov2.transform#ExtraColumnsTransform
        }
        {
          use: mitto.iov2.transform#ColumnsTransform
        }
      ]
      use: mitto.iov2.steps#Input
    }
    {
      use: mitto.iov2.steps#CreateTable
    }
    {
      use: mitto.iov2.steps.upsert#CreateTempTable
    }
    {
      transforms: [
        {
          use: mitto.iov2.transform#FlattenTransform
        }
      ]
      use: mitto.iov2.steps#Output
    }
    {
      key: __source__
      use: mitto.iov2.steps.upsert#SyncTempTable
    }
    {
      use: mitto.iov2.steps#CollectMeta
    }
  ]
}

Here’s the resulting database table:

image
image1274×364 26.1 KB

If needed, please create a support ticket and link the Mitto job and we will help you troubleshoot.

#5

I found the issue.

I was using this base

base: xsv.iov2#XsvInput2

instead of

base: flatfile.iov2#ExcelInput

that is all that needed to get the job to work!