Excel Reader

The Excel Reader component allows you to retrieve data (migration content) from an Excel sheet within an Excel document. Excel documents can consist of multiple sheets, by default it will retrieve all non-empty cells from the first sheet of the document. When configuring the sheet name, you can choose any other sheet from the document. The component will start reading when it receives a message and outputs by row or the full sheet, depending on the configuration. In order to access the local filesystem, the XILL4_WORKDIRS environment variable must be set to the path of the directory to be accessed.

The format of the saved data is:

"Sheet1": {
  "1": {
    "1": "Source category",
    "2": "Target category",
    "3": "Permissions"
  },
}

The first object represents the worksheet, the second a row and the inner-most keys are the cells.

Configuration

Path

Path to the file. The path can be overwritten by sending { path: "/path/to/file.xlsx" } to the input.

Example: ../test.xlsx

Sheet name

Excel documents can contain multiple sheets, therefore it's needed to define the sheet name that will be read. When using Excel files with multiple sheets, the sheet name must be defined. For example: Sheet1. The sheet name can be overwritten by sending { sheetName: "Sheet2" }.

Output every row

By default, the component sends the complete sheet with all rows to the output, when working with big Excel files this might lead to memory issues. For this situation output every row is used. When checked, every row from the sheet will be sent to the output instead of building the full sheet first.

Inputs

Input

Any message will make the component start reading the file from the path that is given in the configuration. However, the path can be overwritten by sending { path: "/path/to/file.xlsx" } to the input.

Outputs

Output

The full sheet will be sent to the output. When using config Output every row, all rows will be sent to the output separately. In the following example, Sheet name is set to test_sheet.

Example:

1 output will be sent:

{
    test_sheet: {
      1: { 1: "ID", 2: "NAME", 3: "AGE" },
      2: { 1: 1, 2: "Mike", 3: 25 },
      3: { 1: 2, 2: "John", 3: 30 },
    },
}

Example with configuration output every row checked:

3 outputs will be sent:

output 1:

  { 1: "ID", 2: "NAME", 3: "AGE" }

output 2:

  { 1: 1, 2: "Mike", 3: 25 }

output 3:

  { 1: 2, 2: "John", 3: 30 }