The Excel formatter enables reading and writing data in the Excel format from a local file, S3, SFTP, and other connectors.

Reading Data

-
    name: myExcelFormat
    type: excel
    properties:
      worksheets: "data"  #required
      useHeader: "true"   #required
      treatEmptyValuesAsNulls: "true"
      inferSchema: "true"
      addColorColumns: "true"
      startColumn: "true"
      endColumn: "true"
      timestampFormat: "true"
      excerptSize: "true"

Properties

  • worksheets (required) - Specify one or many worksheets. If you specify multiple worksheets, the worksheets will be joined together as a single dataset when read. The worksheets must have the same columns.
  • userHeader (required) - use the first row as header. If false, the column names will return as "_c0", "_c1", etc.
  • treatEmptyValuesAsNulls (optional) - default: true
  • inferSchema (optional) - default: false
  • addColorColumns (optional) - default: false
  • startColumn (optional) - default: 0
  • endColumn (optional) - default: Int.MaxValue
  • timestampFormat (optional) - default: yyyy-mm-dd hh:mm:ss[.fffffffff]
  • excerptSize (optional) -  default: 10. If set and if schema inferred, number of rows to infer schema from

Writing Data

-
    name: myExcelFormat
    type: excel
    properties:
      worksheets: "data" 
      useHeader: "true"

Properties

  • worksheets - Specify a single worksheet name to write the data to.
  • userHeader - use the first row as header. If false, the column names will return as "_c0", "_c1", etc.