Importing Data to Cosmic Frog

Cosmic Frog supports importing both CSV and Excel files directly through the application. In this documentation we will cover how users can do this, and which options are available to them.

Importing Data

There are 2 methods of importing Excel/CSV data into Cosmic Frog’s input tables available to users:

1. Upsert: when using this method, data is updated for records that already exist in the Cosmic Frog table and new records are inserted to the table.
2. Replace: all existing data in the table is first deleted and then the data from the file being imported is added to the table.

Pointers on how data to be imported needs to be formatted will be covered first, including some tips and call outs of specifics to keep in mind when using the upsert import method. Next, the steps to import a CSV/Excel file will be walked through step by step.

Data Preparation Pointers

Data is mapped from CSV/Excel files based on matching column names and table names matching to the file name (CSV) or worksheet name (Excel):

• Data (both CSV or Excel) to be imported into an input table in a Cosmic Frog model needs to have column names which match the names of the columns in the Cosmic Frog table that will be imported to exactly.
  • If you create a custom column in your import file, you must create the same name column in the input table to avoid import failures. There is no set limit as to the number of custom columns you can create.
• The name(s) of the CSV file(s) and of the Excel worksheet(s) to be imported need to exactly match the name(s) of the Cosmic Frog input table(s) the data will be imported into. The name of the Excel workbook containing the worksheets to be imported does not need to match anything in Cosmic Frog. We do, however, recommend using descriptive names for these workbooks which will often include the model’s name, and an indication of the version/changes.
  • An exception to this is when importing 1 CSV file at a time, then the name of the file does not have to match the name of the table to be imported to.

Data preparation tips:

• Export the table(s) to be updated to CSV or Excel first to generate a template. Then populate the template with the relevant data before importing the file back in.
• There is no need to have all columns that are present in the table in Cosmic Frog in the CSV/Excel data, just at least one of the table’s key columns, plus any other columns that have data in them is sufficient. Key columns can be recognized in the Cosmic Frog input tables by the key icon to the left of the column name and their column names are in bold, as shown in the screenshot below. One notable exception to this is the Notes field (present in almost all input tables): even though it does not have the key icon and the column name is not in bold, it is part of the primary key of all tables that have a Notes field.

CSV vs Excel: CSV files only have 1 “worksheet”, so it can only contain data to be imported into 1 table, whereas Excel files can have multiple worksheets with data to be imported to different tables in Cosmic Frog.

Please take note of how existing records are treated when using the upsert import method to import to a table which already has some data in it:

• If there are records in the file that is upserted that have matching values for all of the table’s primary key columns, these records’ non-primary key columns are updated with the values specified in the file that is upserted.
  • If a primary key column’s value is blank in the existing record, then having a blank value for it in the file to upsert or not having this column at all in the file to upsert are both considered to be matching with the existing record’s primary key column value.
  • If a non-primary key column in an existing record has a value set already, say for example 2 for UnitVolume on the Products table for a product with Product Name “Basketball” (Product Name and Notes are the primary key columns on the Products table), and the file that is upserted into the Products table has a record with Product Name = Basketball, but does not contain the UnitVolume column in the data, the existing value of 2 for UnitVolume persists.
• Existing records for which there are no records in the upserted file where the values of the primary key columns match are left as is.
• Records in the upserted file which do not have the values of all primary key columns match with those of an existing record are added as new records to the table (i.e. appended).

We will illustrate these behaviors through several examples too.

Importing a CSV/Excel File

Users can import 1 or multiple CSV or Excel files simultaneously, please take note of how the import will work for following situations:

• If 1 CSV file is imported – it is imported to the active table regardless of whether the name of the file matches the active table’s name.
• If multiple CSV files are imported – based on the names of the CSV files, they are imported to the Cosmic Frog input tables that have matching names, regardless of which table the active table is or which table(s) are selected in the input tables list.
• If 1 or multiple Excel files are imported – all the worksheets that have a name matching an input table are imported to those Cosmic Frog input tables. This is again regardless of which table the active table is, or which table(s) are selected in the input tables list. Please note:
  • If multiple Excel files are being imported and 2 or more of them contain worksheets with the same Cosmic Frog table name, the outcome of the import becomes unpredictable as user does not know beforehand the order in which the imports are applied (e.g. they could both be updating the same existing record with different numbers and in that case last change wins). Therefore, users should take care to prevent situations like these.
  • Known issue: if there is an entirely empty worksheet in an Excel file that is imported, the worksheet(s) after this one are not imported.

Once ready to import the prepared CSV/Excel file(s), user has 2 ways of accessing the import and export methods: from the File menu in the toolbar and from the right-click context menu of an input table. It looks like this from the File menu to import a file:

1. Make sure to be in Cosmic Frog’s Data module. If another module is active, click on the Module menu icon (the 3 horizontal bars) and choose the second option Data.
2. When importing 1 CSV file it is important to first make sure the table the data should be imported to is the active table (this is not needed for importing multiple CSV files or 1 or multiple Excel file(s)). User can do this by:
   1. Clicking on the name of the table in the Input Tables list, or
   2. Making sure the tab of this table is the active one by clicking on the tab.
3. Click on the File menu in the toolbar.
4. Select either Import File (UPSERT) or Import File (REPLACE) depending on which import method is desired.

And when using the right-click context menu the steps to import a file are as follows:

1. Again, make sure to be in Cosmic Frog’s Data module. If another module is active, click on the Module menu icon (the 3 horizontal bars) and choose the second option Data.
2. Make sure the Input Tables list is showing by clicking on the square grid icon.
3. When importing 1 CSV file it is important to right click on the table the data should be imported to (this is not needed for importing multiple CSV files or importing 1 or multiple Excel file(s)).
4. Click on File menu in the context menu.
5. Select either Import File (UPSERT) or Import File (REPLACE) depending on which import method is desired.

When using the replace import method, a confirmation message will now be shown on which user can click Import to continue the import or Cancel to abort.

Next, a file explorer window opens in which user can browse to and select the CSV/Excel file(s) to import:

1. Navigate to the folder where the file(s) to import are located.
2. Cosmic Frog currently can import Excel files with .xlsx, .xls, and .xlsm extensions, and CSV files.
3. Select the file(s) to import by clicking on them (holding the Ctrl-button down if selecting more than 1 file).
4. Click on Open when ready to import or Cancel to abort the import (buttons not shown in screenshot as they are hidden by the file extension drop-down).

Once the import starts, a status message shows at the top of the active table:

The Model Activity log will also have an entry for each import action:

1. To open the Model Activity log, click on the dark blue speech bubble located at the top right of your Cosmic Frog screen.
2. The pane indicates that this is the Model Activity list.
3. The specific activity we scrolled down to shows a successful File Upsert action.
4. More details are listed, including the number of records updated (0 here), the number of records inserted (6 here), and when the activity happened.

User can see the results of the import by opening and inspecting the affected input table(s), and by looking at the row counts for the tables in the input tables list, outlined in green in this screenshot:

Example 1 – Replace Entire Products Table

Consider user is modelling a sports equipment company and has populated the Products table of a Cosmic Frog model with 8 products as follows:

After working with the model for a while, user realizes a few things:

1. The naming convention of the products could be improved to be more structured and allow for easier filtering, model building, and output analysis.
2. The Unit Price column is not populated, but executives are interested in including this to have the model calculate revenues and profit too.
3. There are 2 products the company is considering adding to its offering, basketballs and baseballs, and these need to be added to the model to be included in certain scenario runs.

As item number 1 will change the product names, a column that is part of the primary key of the Products table, user will need to use the replace import method to make these changes as the upsert method does not change the values of columns that are part of the primary key. Following is the .xlsx file user prepares to replace the data in the Products table with:

1. All Product Names are now prefixed with first an abbreviation indicating the type of product (FG meaning finished good), followed by the product category: balls, rackets, and bats here.
2. The Unit Price field is populated for all products, while ensuring that the original data of Status and Unit Value is included too. Keeping this data in here is important since the replace will delete all data from the existing table and then add the data from this worksheet.
3. The 2 products to be included in specific scenarios are listed in the bottom 2 records, with Status = Exclude, and the Notes field populated for easy filtering when setting up the scenario(s) that change the status of these fields to Include.

After importing the file using the replace method, the Products table looks like this:

We see the records are the exact same as what was contained in the Products.xlsx file that was imported, and the row count for the Products table has correctly gone up to 10 with the 2 new products added.

Example 2 – Upsert to Products Table

Continuing from the Products table in the last screenshot above, user now wants to make a few additional changes as follows:

1. They have been made aware the Unit Value and Unit Price values of Squash Balls are incorrect in the original data, and should be changed to 6 and 12, respectively.
2. The Rackets category should by default be excluded from the model runs going forward as the company is phasing this category out.
3. A new product, Softballs, to be included in a specific scenario only, should be added to the Products table.

To make these changes to the Products table, user prepares the following Products file to be upserted to the Products table, where the green numbers in the screenshot below match the items described in the bullet point list directly above:

After using the upsert import method for this file into the Products table, it contains following records. The ones changed / added are listed at the bottom:

In the boxes outlined in green we see that all the expected changes and the insertion of the 1 new record have been made.

Example 3 – Replace Using Invalid Data

Let us also illustrate what will happen when files with invalid /missing data are imported. We will use the replace import method for the example here, but similar results will be seen when using the upsert method. Following screenshot shows a Products table that has been prepared in Excel, where we can see some issue already: a blank Product Name, a negative value for Unit Price, etc.

After this file is imported to the Products table using the replace method, the Products table will look as follows:

The cells that are outlined in red contain invalid values. Hovering over each cell will show a tooltip message describing the problem.

1. The Product Name field is left blank, it should contain a unique name for each product. Hovering over this cell following tooltip text is shown: Value is required.
2. The Status field is set to Potential which is not a valid option for this field. Hovering over this cell following tooltip text is shown: Allowed values are [Include,Exclude].
3. The Unit Value field is set to Test, it should however be a numerical value. Hovering over this cell following tooltip text is shown: Should be of type double-precision. Cannot be negative.
4. The Unit Price field is set to -5, it should however be a positive value. Hovering over this cell following tooltip text is shown: Cannot be negative.

For tables with many records, it may be hard to find the fields in red outline manually. To help with this, there is a standard filter user can apply that will show all records that have 1 or multiple input data errors:

1. Open the Filter menu in the toolbar.
2. Select the last option in the drop-down menu “Show Input Data Errors”.
3. The table is now filtered to only show records where 1 or multiple fields contain data errors.

In conclusion, Cosmic Frog will let a user import invalid data, and then helps user identify the data issues with the red outlines, hover over tooltips, and the Show Input Data Errors filter.

Example 4 – Upsert Does Not Edit Values of Primary Key Columns

Consider following Transportation Policies table:

1. The primary key of the table consists of the 4 fields outlined in green, Origin Name, Destination Name, Product Name, and Mode Name, plus the Notes field (not shown).
2. There are 4 records set up for which the Product Name field has been left blank, meaning each policy applies to all products present in the model.

There is now a change where from MFG_1 all Racket products need to be shipped by Parcel for a fixed cost of $50. User creates 2 Named Filters (see the Named Filters in Cosmic Frog help center article) in the Products table: 1 that filters out all racket products (those products that have a product name that start with FG_Racket) which is named Rackets and 1 that filters out all non-racket products (those products that do not contain racket in the product name) which is named AllExceptRackets. Next, user prepares following TransportationPolicies.csv file to upsert into the Transportation policies table with the intention to update the first 2 records in the existing table to be specific for the AllExceptRackets products and add 2 new ones for the Rackets products:

1. As these policies now need to behave differently for Rackets vs AllExceptRackets products, the Product Name field is used and set to the names of these named filters to apply to those products.
2. For the Rackets records, Mode Name is set to Parcel and Fixed Cost to 50.

The result of using this file to upsert to the Transportation Policies table is as follows:

1. As the Product Name is part of the table’s primary key, and the primary key fields do not match entirely for the MFG_1 to DC_1 / DC_2 Truck mode records (origin name, destination name, mode name, and notes match, but product name does not) new records are created and inserted:
   1. The existing records are not changed since there are no records in the upserted file that match the primary key fields exactly (the product name is different).
   2. These are the new records added for MFG_1 to DC_1 / DC_2 Truck mode records, for the products in the AllExceptRackets group. Note that no costs are associated with this record as user expected the upsert to update the existing records which have costs on them ($1 per unit per mile).
2. These records for the Rackets products were added (inserted) to the Transportation Policies table as was the intention.

This example shows that users need to be mindful of which fields are part of the table’s primary key and remember that values of primary key fields cannot be changed by the upsert import method. An example workflow that will achieve the desired changes to the Transportation Policies table is as follows:

1. Export the Transportation Policies table to Excel or CSV.
2. Update the 2 existing records for MFG_1 to DC_1 / DC_2 using the Truck mode: set the Product Name to AllExceptRecords.
3. Add 2 new records for the MFG_1 to DC_1 / DC_2 Parcel mode where Product Name = Rackets; ensure to set the Fixed Cost field to $50.
4. Save the file.
5. Use the replace method to import this file back into the Transportation Policies table.