Page Comparison

It is often useful to read data directly from an Excel file rather than having to create additional data files. Using this technique you can read a specific worksheet and/or range to locate just the data you need.

The model for this example is 005 Read from an Excel file. The Excel file (Sales.xlsx) is also provided with sample data

Step-by-step guide

Add a file collector to your model and configure it as follows:
1. Source Type = Specified Directory
2. File Type = Excel Spreadsheet
3. File Location Strategy = All files in folder
4. Input Directory Expr. = The folder name containing your Excel file , within the Import Directory defined in System Directories
5. File Pattern Expression = ".*.xlsx" (This will ensure only Excel files are processed)
6. Number of Header Lines = 1. This will ensure the header row doesn't get processed as data and that the attributes are named correctly to match the header row.
Save the changes and then click on the advanced tab
Enter the name of the Worksheet and the range of the data into the Excel data Expression box in the format show below. Include the header row in the range and a number of rows that is sufficient to capture all data
Tick the Ignore Undefined Values box. This will ensure blank rows are ignored
Save changes and Click on the File Columns tab
Click Create the file attributes automatically button. This will populate the attributes as shown below.
Check the data types and lengths and make any adjustments if needed to match the expected data
Save and close the file collector
Hover over the file collector and click Create a new stream using the attributes from the file collector
After creating the stream, view the attributes to confirm they match the file collector
Run analysis on the stream ad view the data from the specified range and workbook, excluding any blank rows