Description:

The Excel Input File step is an ETL source component that imports data from Microsoft Excel workbooks into a FlowWright ETL definition.

Microsoft Excel is widely used across organizations for maintaining business reports, financial records, inventory lists, customer information, operational metrics, and manually maintained datasets. The Excel Input File step enables FlowWright ETL pipelines to directly consume spreadsheet data without requiring intermediate conversion to CSV or other formats.

The step is configured by specifying:

• The Excel workbook location
• The column schema that maps spreadsheet columns into the ETL dataset
• An optional schema viewer for validating the imported dataset structure

During execution, the ETL engine opens the specified Excel workbook, reads the worksheet data, applies the configured column schema, and creates an in-memory dataset that becomes the source for downstream ETL components.

The Show Schema property assists ETL designers by displaying the imported dataset structure before execution, helping verify that spreadsheet columns match the expected ETL schema.

The Excel Input File step is typically the first component in an ETL pipeline.

The step supports:

• Microsoft Excel workbook import
• Column schema mapping
• Dataset generation
• Schema validation
• Enterprise ETL workflows
• Integration with FlowWright ETL pipelines

Typical business uses include:

• Customer master imports
• Financial spreadsheet processing
• Sales report analysis
• Inventory imports
• Budget data migration
• Operational reporting
• Business intelligence staging

Usage:

The Excel Input File step is typically the first component in an ETL definition.

During execution:

1. The configured Excel workbook is opened.
2. Worksheet data is read into memory.
3. The selected column schema is applied.
4. The ETL dataset is created.
5. Processing continues to downstream ETL components.

A typical ETL pipeline might look like this:

Typical usage scenarios include:

• Importing customer spreadsheets
• Processing monthly sales workbooks
• Loading inventory reports
• Importing financial planning data
• Preparing operational reports
• Migrating spreadsheet-based business information
• Creating reporting datasets for enterprise analytics

Once imported, the dataset can immediately be validated, transformed, filtered, grouped, aggregated, sorted, or exported to databases and various file formats.

Prerequisite:

The ETL process engine service should be running to execute the ETL definitions.

Example:

Let’s build and execute the “clsInputCSVDef” example.          

• Create a new ETL definition named “clsInputCSVDef” and open it in designer mode. 
• Drag “clsInputExcel, clsTransformFormatNumberToString, and clsOutputExcel” steps to the canvas.
• Connect the dots between the “clsInputExcel” step and other steps, as shown above.
• Define a variable or global to hold the virtual path. 
• Click the "clsInputExcel" step to configure its "Required" properties. Provide a name for the step. Specify the path to the input Excel file on the application server. Select the ETL data schema from the drop-down list. Note: The schema's column names should match those in the input file. Click the Save button. Click here to learn about the ETL Data Schema Designer.

• Click the "clsInputExcel" step to view its "Optional" properties. Select the Show Schema button. The pop-up window displays the schema columns as shown below. This function helps to understand the schema at a glance.  

• Click the "clsTransformFormatNumberToString" step to configure its "Required" properties. Provide a name for the step. Click the button to configure the transform list, then click the Save button. 

• Click the "clsTransformFormatNumberToString" step to configure the transform list. The configuration pop-up window appears. Click the Add Row (+) button to insert an empty row. Select “integer or value” as the input source column and string as the output target column. Click the Save button. You may add multiple columns for transformation by using the Add Row button. 

• Click the "clsOutputExcel" step to configure its "Required" properties. Provide a name for the step and specify the output Excel file path on the application server. Click the Save button. 

• Click the "clsOutputExcel" step to configure its "Optional" properties. Provide a name for the step. Provide the variable or global reference for the virtual output path. Click the Save button. 

• The “Logging” configuration is necessary for documentation and also measures workflow progress and percent complete. This is achieved by configuring the step state and percent fields individually, as shown below. Configure the “Logging” using the following properties.

• Save the ETL definition, create a new ETL instance, and execute. Render the ETL instance. Click the ETL step to view its properties. The “clsInputExcel” step facilitates transforming the input file. The variable or global holds the output file’s virtual path, as shown below. 

Tips:

• Ensure the Excel workbook is accessible to the FlowWright ETL server.
• Verify that the selected column schema accurately matches the worksheet structure.
• Use the Show Schema option to confirm imported columns before execution.
• Validate imported datasets immediately after reading the workbook to detect missing or invalid values.
• Remove unnecessary worksheet formatting before importing large spreadsheets to improve processing efficiency.
• Maintain consistent column ordering across recurring workbook imports.
• Combine this step with Validate Data, Transform Text Case, Transform Date by Format, Sorting Transform, Group By Transform, Filter with Conditions, Excel Output File, SQL Output, or CSV Output File to build complete enterprise ETL workflows.

Notes:

• The Excel workbook must be accessible by the FlowWright ETL server.
• The selected column schema determines the structure of the generated ETL dataset.
• The Show Schema property helps validate imported spreadsheet columns during ETL design.
• Workbook access issues, invalid file formats, or schema mismatches may prevent successful execution.
• Because this is an ETL source component, it has no incoming connections and serves as the starting point of the ETL pipeline.
• ETL execution statistics, workbook import results, and any processing errors are recorded in the ETL execution log.
• This step is commonly used for spreadsheet-based reporting, financial imports, customer master migration, inventory management, operational reporting, ERP integration, and business intelligence solutions.

Excel Input File vs. CSV Input File:

Both components import external data into FlowWright ETL, but they are intended for different source formats.

As a general guideline:

• Use Excel Input File when importing spreadsheet-based business information maintained in Microsoft Excel.
• Use CSV Input File when importing lightweight text-based datasets generated by enterprise systems or integration platforms.

Definition Sample:

You may download the sample ETL definition(s) from the link provided and later import them into your FlowWright ETL Designer.

Note: Verify and complete any missing configuration after importing the sample, including:

• Input workbook location
• Column schema selection
• Schema validation
• Worksheet structure verification
• Downstream transformation mappings
• Destination component configuration
• Environment-specific file paths

After verifying the configuration, save the ETL Definition before execution.

Click here to download the sample file.