By the end of this chapter you will be able to:
- Collect data from additional tabs in Excel Spreadsheets
A file containing a mapping of Counties to Regions can be found in the input files, at:
…\inputData\AccountTransactions\AccountTransactions.xlsx
On the second tab of this workbook – accountHistory – details for the accounts are provided. You will create a file collector to read in this information:
- Create a new model Read Account History
- Drag
- Add a new File Collector onto the modelling pane menu - Call the new file collector Read Account History
- Hover over your new File Collector, and press
- In the file explorer, go to: …\inputData\AccountTransactions
- Select the file AccountTransactions.xlsx and press Open
- In the Upload Managed File form, press
By default the file collector is set up to read from the first sheet in the workbook – and the Stream is configured to hold this data.
You will need to update this configuration to read from the second sheet:
- Permanently delete the generated Stream ReadAccountHistory
- Open the configuration form for the file collector by double-clicking the icon on your modelling pane
- Go to the Advanced tab
- In the field Excel Data Expression enter: "accountHistory!A1:F100"
- Press
- Apply - Go to the File Columns tab
- Delete the attributes that have been automatically created (these reflect the data in the first sheet in the workbook)
- Move to the File Columns tab
- Press
- Create the file attributes automatically from the header row in the file
- Press
PhixFlow will read the file and work out the attributes that make up the file. You will see these attributes appear in the Attributes list
- In the banner of the File Collector details form, press
- Create a new Stream using the File Collector attributes - Have a look the attributes in the new stream – you can see that these have been derived from the columns in the file collector
- Save your changes
- Run analysis on the Stream and confirm that the data that has been read into PhixFlow is from the second tab in the spreadsheet