How To: Read from an Excel file

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.

Step-by-step guide

1. 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.

2. Save the changes and then click on the advanced tab

3. 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

4. Tick the Ignore Undefined Values box. This will ensure blank rows are ignored
5. Save changes and Click on the File Columns tab
6. Click Create the file attributes automatically button. This will populate the attributes as shown below.
7. Check the data types and lengths and make any adjustments if needed to match the expected data
8. Save and close the file collector
9. Hover over the file collector and click Create a new stream using the attributes from the file collector
10. After creating the stream, view the attributes to confirm they match the file collector
11. Run analysis on the stream ad view the data from the specified range and workbook, excluding any blank rows

Sample Model for Reading from an Excel File

 File Collector Configuration

Excel  Worksheet and Range Configuration

File Column Configuration

Sample Excel Workbook with Sales Worksheet

Results of CenterView Stream after running analysis

Model Ref: 005

Related articles