Importing Data from a Microsoft Excel file

In the Factory, you can import MS Excel files, specifying the data sheet you want to import.

• Dragging the Excel file directly onto the Stage, automatically creating an Import from Excel task;

• Dragging an Import from Excel task onto the Stage. This operation allows you to make a more precise import operation, as you can:

  • Import a single file, choosing the specific datasheets to import;

  • Import multiple files together, concatenating them into a single dataset. For this operation, it is important that the Excel files have the same structure.

Prerequisites

• You must have created a flow;

• If you are importing multiple files, they need to have the same structure.

Procedure

1. Drag an Import from Excel File task onto the stage.

2. Double click the task and open it.

3. Select whether you want to use a Saved source or a Custom source.

4. Choose from the drop down list if you want to import the file from your computer (Local) or from a Remote Filesystem.

   1. If you are importing from a Remote Filesystem, choose it from the list and then click on the pencil button to set the connection information required (only if you are using a Custom source). The tables are loaded in the Files tab.

   2. If you are using a Local Filesystem, drag the Excel files onto the file area or click on the Select File button and choose the path.

5. In the Sheets tab, select the sheets you want to import and the Sheet Import mode (Import selected sheets, Import all but the selected sheets, Import all sheets)

6. In the Concatenation type box, select either:

   1. Detach to keep the imported files, or sheets from the same file, separate, or

   2. Concatenate if you want to merge them. You must then specify the concatenation type:
      - Inner concatenation includes only attributes that exist in both tables.
      - Outer concatenation final table includes all the attributes, filling in any missing values if necessary.

7. Select if you want the columns to be matched by their Name or Position in the the Match Column by box.

8. Click on the Excel Configuration tab and set the Parsing options and the Import options, as displayed in the table below.

9. Save and compute the task.

Parsing and import options