6 Reading additional tabs from Excel spreadsheets

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 Upload

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:
  • Right click on the stream and select Permanently Delete
• Open the configuration form for the file collector
• Go to the Advanced section
• In the field Excel Data Expression enter: "accountHistory!A1:F100"
• Press  - Apply
• Go to the File Columns section
• Delete the attributes that have been automatically created (these reflect the data in the first sheet in the workbook)
• Press - Create the file attributes automatically from the header row in the file

PhixFlow will read the file and work out the attributes that make up the file. You will see these attributes appear in the columns list

• In the model view, from the hover menu of the file collector 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