Overview

Cosmos offers several ways to connect to non-Business Central data. One method that is commonly used to bring in ad hoc data that may not reside in Business Central or to import historical data from another system is by using an Excel Data Source. This type of data source gets data from Static Files and brings it into the Data Model in Cosmos. This article will cover how to set up and work with an Excel Data Source.

Creating an Excel Data Source

Note: The Azure SQL data source in Cosmos requires an additional data source connector being added to your license. Contact your partner or Cosmos representative to have this added to your license.

Excel Data Sources in Cosmos leverage Static Files, so you will first want to create a Static File and get your data set up in it. Details on that are covered in the KB article here:

Working with Static Files

Once you have that set up you will navigate to the Data Sources section under Data Management -> Manage and click the New Data Source button. 

You will then give your data source a name, select Excel as the Type, and select the Static File to connect to. Once this is done you will click the Create button.

Once the data source has been created, you can click the Validate button to have Cosmos check the data source setup and the contents of the Static File. If there is anything set up incorrectly then Cosmos will show you in a window, otherwise if clicking the Validate button turns into a checkbox it means that everything is set up properly. 

If you need to change the name or the Static File that the data source is connected to you can click the Edit button to adjust your settings.

Working with Data from an Excel Data Source

Once you have set up your Excel Data Source you will see it in the Staging section as a new data source. At this point you can treat it as any other data source and check the box to bring in the table, check the boxes for which fields you want to bring in, and then add transformations, lookups, and/or bring the data into its own table in the Data Model section just as you would with standard Business Central data.