Skip to content

Connecting a Workbook to data in the Data Lake

When using XLConnect, the most typical usecase is connecting to data that someone else will have prepared in the Data Lake. This could be interest rates that were downloaded daily, reference data like mortality tables, pricing parameters etc. Connecting your workbooks to such data is one of the core features of XLConnect.

1.

Importing the original format

  • Open a new workbook and make sure the launch pad is visible on the left.
  • Navigate to the workbook 'Tutorial \> 2. Basics \> Loan Calculator.xlsx' but do not open the workbook. Instead just select it to display the Messages:

You should see something like this, where the 'Loan Calculator' workbook is selected, and the messages pane shows the three loans.

  • Double click any of the messages, this should bring up this dialog:

  • Leave the settings as they are and click 'Import' (or press ENTER) to complete the import.

Your screen should look something like this:

The values of the fields in the message have been put in the worksheet on the same place as they were on the 'Loan Calculator' workbook.

  • 'Import Original Format' imports a message by putting the cells in the same layout as they have in the workbook that produced them.

  • Double click any of the other loans in the messages pane to see that not just the values have been imported, but connections have been made to load that message type into workbooks.

  • Importing massages not only imports the data of a particular message, but creates connections to load that type of message.

  • If you would like you can commit this workbook to be able to se it again. Else just close it to start for the next exercise.

Importing the Message format

  • Create a fresh workbook by starting Excel or pressing CTRL-N to create a new workbook.
  • Navigate to the workbook 'Tutorial \> 2. Basics \> Loan Calculator.xlsx' again and do not open the workbook. Instead just select it to display the Messages.
  • Double click any of the messages (loans) to bring up the 'Import Message' dialog again
  • This time select 'Message' format and press 'Import'. You should now see this:

These values form the message were not imported in the layout of the original workbook, but they are just imported in the order they appear in the message. Notice how the arrays (Period – RepaymentActual) are now rows instead of columns.

  • Import Message Format imports a MessageType and lays out the cells in the order they appear in the message.
  • XLConnect imports arrays as rows by default, these can be made columns. See paragraphs 7.4 and 7.5 to see how.

Importing Map Only

  • Open up a new workbook and navigate to the loans messages again like in the previous two examples
  • Double click to bring up the Import dialog, this time choose 'Map only' and click 'Import'

You should now notice no values have been added into the worksheet, only that the map has appeared on the right.

  • Double click any of the other messages to see their values are not put into the sheet
  • Keep the workbook open for the next exercise

Drag & Drop

  • In map1, open the object 'Input' and drag the connection 'Loan' to cell B2:

Dragging a conection onto the workbook connects that cell.

  • Now drag the ArrayConnection 'Calculations/Principal' onto the workbook:

This will connect the array.

  • Now hold down the CTRL key and drag ArrayConnection 'Calculation/Month' onto the workbook.

Now the array is inserted not as a row but as a column

  • Holding down the CTRL key when dragging ArrayConnections pivots them into row columns. This only works for ArrayConnections, RangeConnection and TableConnection remain in their original orientation.

  • Drag 'Calculations/InterestPayment' next to column period (as a columns by holding down CTRL)

  • Drag 'Calcualitons/PrepaymentActual' in between these two columns
  • Hold down CTRL at the beginning of the drag
  • Hold down SHIFT to insert the cells between them

  • Holding down the SHIFT key on any connection inserts the cells between other cells. This can be combined with the CTRL key for ArrayConnections to insert them as columns.

Updating connections

So far we've discussed creating connecting cells from the workbook, or dragging them from the Map. There are two more ways to changes connections: updating connections and disconnecting

\<\< todo \>\>

Importing a message format multiple times

Advanced topics

Writing Data to the Data Lake with a Workbook

In the previous chapter we connected workbooks to data others had prepared for us in the Data Lake. In this chapter we will show how to create such data from workbooks.

Connecting cells

  • Open the workbook 'Tutorials \> 4. Creating Connections \> Loan Calculator No Connections.xlsx'
  • Select cell C2 (with value 10.000)
  • Click the 'Connect' button on the ribbon (Click the top half of the button with the icon, not the bottom half that opens up a dropdown that will be explained later)

Now the 'Maps& Messages' task pane should appear showing this:

This shows that new Map has been created called 'Map1' with one connection called 'Loan'.

This name was taken from the cell to the left of C2 because that's usually where the label for a cell will be.

Changing connection names

The name Map1 isn't very helpful, so we want to

  • Select the map called 'Map1' and hit F2, this will put the Map in edit mode. Change the name to 'Loan' and hit enter. Hitting enter will cycle through the connections to keep changing the names.

  • As we are content with the name right now hit ESC to exit edit mode.

  • F2 puts the Maps&Messages control into Edit Mode, ESC goes back out.

Your Maps&Messages pane should now look like this:

  • Now select range C3:C4 (so the two cells that contain 36, and 2.40%) and click on the bottom half of the connect button to open the dropdown, and select Property.

You should now see two connections added to the Map called 'Loan'

  • When you select multiple cells and choose to add as Property Connection, each individual cell will be added as separate connection saving quite some of clicking around.

You may wonder where it got the names from. XLConnect looks to the left of a newly connected cell to see if there is a word there, as that is the most likely place for a meaningful label to be. If it is not as desired you can change the connection name with F2. As the names are fine we will leave these as is.

  • XLConnect looks to the left of newly connected cells for an initial name for a new connection. You can always edit it with F2.

Now we have our first connections on this workbook, we want to save it somewhere. As you cannot save to the tutorials database we will save the workbook in your personal folder.

  • On the ribbon, hit the Commit Workbook button, and save the workbook to your personal folder (See 5.3 Committing the workbook to recall how).
  • Close Excel

Types of connections

XLConnect has different types of connections:

  1. PropertyConnections which contain a single value
  2. ArrayConnections that contain an array of values
  3. RangeConnections that contain a range of multiple rows and columns. It will be stored as an array of arrays.
  4. TableConnections that contain a datatable

  5. ObjectConnection which is a collection of other connections. These allow you to structure larger messages into 'chapters' or create specific layouts required to communicate with other systems that expect a specific layout.

  6. Open Excel again, then open the loan calculator from your Personal folder by any to two ways:

  7. In the Launch Pad, open your personal database and double-click the file

  8. From the ribbon, select quick open \> your personal db \> the workbook.

  9. Select range B10:B70 (those are the period numbers 0-60) and click on the top half of the Connect button.

You will now see that a Connection called 'Month' is added. If you look at the icon, it is not a single blue square but a blue row. This is the icon for an ArrayConnection.

Also note that in the case of a column, the label was taken from the cell above, instead of to the left.

We will now connect the remaining cells

  • Select range C10:E70, those are the values for the three columns Principal, Interest and Repayment
  • Click
  • bottom half of the connect button
  • then the \> indicator to the right of Array
  • Split area into columns

This will have added the three columns as array connections. This splitting can also be done to connect larger areas of cells in which case this will save quite a bit of repetitive selecting and clicking.

What you will notice is that there are two connection named Interest that are colored red.

  • hover above one of them to read the validation message: 'The name Interest is used more than once on this level. Change one, or move to another level by inserting an object'

That message is pretty self explanatory. If you try to save the workbook now you will get the message

  • Press F2 and change the second connection into 'InterestPerPeriod', press ENTER and then ESC.

The map should now pass validation:

  • Hit CTRL-S, this should bring up the Commit Workbook dialog

  • Press ENTER to commit changes

  • CTRL-S, ENTER is way to commit your workbook changes in under a second with just two keystrokes, although it will help a third party and your future self if you add comments on the changes you made.

Guessing connection type

In 6.1, when we selected a single cell and pressed the connect button (top half, without specifying the type) it created a PropertyConnection. When we selected a Single column of cells it created an ArrayConnection.

By default, when you press the top half of the Connect button (or hit CTRL-SHIFT-C which is the shortcut for the Connect button), XLConnect will 'guess' the connection type best suited to your selection by using there rules:

  • Is it a single cell: PropertyConnection
  • Is it a single column or row: ArrayConnection
  • Is a range with multiple rows and columns: RangeConnection
  • Is it a Table: TableConnection

  • When a multiple selection is made (by holding down CTRL and selecting other cells, the rules are applied to every section).

  • When you have selected multiple cells and select property, every cell is added a separate property.

There are quite a few ways in which XLConnect can save effort to connect new worksheets, especially when they contain a lot of cells knowing the behavior can save a lot of work.

Different ways of creating connections

There are multiple places in the GUI where you can

  • CTRL-C
  • Ribbon
  • Task pane
  • Context Menu (right click)