ExcelInOut

Overview

Load data from an Excel workbook and output to a templated workbook according to templates column definitions

Using CreateTableFromWorkbook, CreateExportTemplate and ExportIntoWorkbook

[
  {
    "method": "CreateTableFromWorkbook",
    "action": "LOAD",
    "table": "superstore",
    "filename": "%DATAPATH%ExcelLoad/Global_Superstore2.xlsx",
    "worksheet": "Transactions",
    "headerRow": 1,
    "project": "ExcelFileLoad"
  },
  {
    "method": "CreateExportTemplate",
    "name": "MyExportTemplate",
    "fromWorkbook": {
      "filename": "%DATAPATH%ExcelLoad/template.xlsx",
      "worksheet": "owid",
      "headerRow": 2
    },
    "project": "ExcelFileLoad"
  },
  {
    "method": "ExportIntoWorkbook",
    "filename": "%DATAPATH%ExcelLoad/workbook.xlsx",
    "xlTemplate": "%DATAPATH%ExcelLoad/template.xlsx",
    "startRow": 3,
    "sheet": "owid",
    "includeHeaders": false,
    "exportTemplate": "MyExportTemplate",
    "dataSet": {
      "logic": "or",
      "name": "SubSet",
      "strict": true,
      "set": [
        {
          "logic": "or",
          "stype": "TABLE",
          "entity": "superstore"
        }
      ]
    },
    "project": "ExcelFileLoad"
  }
]

The loaded superstore contains 24 fields :-

CreateExportTemplate uses the Excel template "%DATAPATH%ExcelLoad/template.xlsx" , row 2 to identify the fields to be exported

Executing the script will produce a workbook at "%DATAPATH%ExcelLoad/workbook.xlsx"

Enhancement

This version of the script has verification add.

It will stop if the source data does not contain the fields specified in the template.

[
  {
    "method": "StartErrorLog",
    "filename": "ErrorLog",
    "mailTo": [],
    "project": "ExcelFileLoad"
  },
  {
    "method": "DropProject",
    "deleteFiles": true,
    "name": "ExcelFileLoad",
    "project": "ExcelFileLoad"
  },
  {
    "method": "CreateProject",
    "name": "ExcelFileLoad",
    "project": "ExcelFileLoad"
  },
  {
    "method": "OpenProject",
    "name": "ExcelFileLoad",
    "project": "ExcelFileLoad"
  },
  {
    "method": "CreateTableFromWorkbook",
    "action": "LOAD",
    "table": "superstore",
    "filename": "%DATAPATH%ExcelLoad/Global_Superstore2.xlsx",
    "worksheet": "Transactions",
    "headerRow": 1,
    "project": "ExcelFileLoad"
  },
  {
    "method": "CreateExportTemplate",
    "name": "MyExportTemplate",
    "fromWorkbook": {
      "filename": "%DATAPATH%ExcelLoad/template.xlsx",
      "worksheet": "owid",
      "headerRow": 2
    },
    "project": "ExcelFileLoad"
  },
  {
    "method": "RequiredFields",
    "exportTemplate": "MyExportTemplate",
    "table": "superstore",
    "project": "ExcelFileLoad"
  },
  {
    "method": "StopIfErrors",
    "project": "ExcelFileLoad"
  },
  {
    "method": "ExportIntoWorkbook",
    "filename": "%DATAPATH%ExcelLoad/workbook.xlsx",
    "xlTemplate": "%DATAPATH%ExcelLoad/template.xlsx",
    "startRow": 3,
    "sheet": "owid",
    "includeHeaders": false,
    "exportTemplate": "MyExportTemplate",
    "dataSet": {
      "logic": "or",
      "name": "SubSet",
      "strict": true,
      "set": [
        {
          "logic": "or",
          "stype": "TABLE",
          "entity": "superstore"
        }
      ]
    },
    "project": "ExcelFileLoad"
  }
]