6 Reading from specific Excel worksheets
- Chris Welford
- Zoe Baldwin
- Anthony George
- Fiona Sargeant (Unlicensed)
By the end of this chapter you will be able to:
In 2 Using Excel Templates, you downloaded train.zip and saved the files to <path>
. Find this file: <path>\inputData\AccountTransactions\AccountTransactions.xlsx
. The second tab of this workbook – accountHistory contains details of account transactions.
Upload data using a file collector
You have already learned how to upload data using a file collector in 3 Combining Data Sets. By default the file collector reads from the first sheet in the workbook. The table that PhixFlow creates when you upload the data is configured to hold this data. For this exercise, you need the data from the second worksheet. You will need to set additional options to configure PhixFlow to use this sheet.
The easiest way to do this is to load the data from the first sheet, then edit the collector properties.
Create a new model called
Read Account History.
How?- Use the button on the Analysis home screen.
- Enter the name
Read Account History.
- PhixFlow displays the empty model window in the workspace on the left and opens the model's property tab on the right.
Add a file collector and set Basic Settings → Name:
LoadAccountHistory
.How?- From the model toolbar, drag a File Collector into the model.
- PhixFlow displays the property tab for the new file collector.
In the property tab toolbar, click Apply.
Upload the data.
How?- In the model, hover your mouse pointer over the
AccountHistory
file collector to display the context toolbar and click Upload File. - Navigate to the directory
<path>\train\inputData
.\AccountTransactions\AccountTransactions.xlsx
- Select the file
AccountTransactions.xlsx
and click Open, then click Upload.
- In the model, hover your mouse pointer over the
- On the pop-up box, under Select a WorkSheet, choose
accountHistory
. - PhixFlow adds a new table to the model, called
LoadAccountHistory
.
Change the file collector
- Double-click the file collector LoadAccountHistory to open its property tab. Set Advanced → Excel Data Range Expression:
"accountHistory!A1:F100"
- Run analysis on the table and confirm that the data that has been read into PhixFlow is from the second tab in the spreadsheet.
- In the model window, click Save.