PhixFlow Help
6 Reading from specific Excel worksheets
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 from 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 stream 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.
Add a file collector and set Basic Settings → Name:
LoadAccountHistory
.In the property tab toolbar, click Save.
Upload the data.
- PhixFlow adds a new stream to the model, called
LoadAccountHistory
.
Change the file collector to load the second worksheet
- In the model, right-click the new stream and click permenantly delete
- Double-click LoadAccountHistory to open its property tab. Set Advanced → Excel Data Range Expression:
"accountHistory!A1:F100"
- In the File Coluns section, delete the column information that PhixFlow has already loaded.
- Click - Create file attributes
- PhixFlow already knows about the data in the spreadsheet. It now uses the expression you added to read the information about the columns in the second worksheet. PhixFlow adds the attributes to the list.
- In the model view, from the hover menu of the file collector press - Create a new Stream
- Save your changes
- Have a look the attributes in the new stream – you can see that these have been derived from the columns in the file collector
- Run analysis on the stream and confirm that the data that has been read into PhixFlow is from the second tab in the spreadsheet
Please let us know if we could improve this page feedback@phixflow.com