Importing and Exporting Data in Cosmic Frog

Cosmic Frog supports importing and exporting both CSV and Excel files directly through the application. This enables users to for example:

• Bulk upload data that was exported to CSV/Excel from the organization’s ERP systems to accelerate model building in Cosmic Frog.
• Easily update changed data and add new data.
• Export a subset of output tables for quick analysis in tools many users are familiar with.

In this documentation we will cover how users can import and export data into and out of Cosmic Frog, and illustrate this with multiple examples.

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 (see the “Exporting to CSV/Excel Files” section further below on how to do this). 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 – Use Replace to Bulk Import Data for First Build Model

A common way to start building a new model in Cosmic Frog is to make use of the replace import method to populate multiple tables simultaneously with data from Excel or CSV files. These files have typically been prepared from ERP extracts which have been manipulated to match the Cosmic Frog table and column names. This way, users do not need to enter data manually into the Cosmic Frog input tables, which would be very laborious. Note that it can be helpful to first export empty tables from a new, empty Cosmic Frog model to have a template to start filling out (see the “Exporting to CSV/Excel Files” section further below on how to do this).

Starting with an empty new model in Cosmic Frog:

1. No row counts are shown for any of the input tables, meaning these table do not have any data in them.
2. User is going to use the Import File (REPLACE) option from the File menu in the toolbar to import the file shown in the next screenshot.

User has prepared the following Excel .xlsx file:

1. The name of the file is Prod_Cust_Fac_CFImport_2025_02_24.xlsx.
2. It has 3 worksheets, each of which has a name that matches the name of a Cosmic Frog input table: Customers, Facilities, and Products.
3. The Customers worksheet is showing and we can see that the column names are matching those of the Customers input table in Cosmic Frog. Note that only 4 columns are included and populated, this is sufficient; columns which would have only blank values do not need to be explicitly defined and imported. The customername column is part of the table’s primary key and should be included for each record.

After importing this file into Cosmic Frog, we notice that the Customers, Facilities and Products tables now have row counts that match the number of records we had in the Excel file that was used for the import, and we can open the individual tables to see the imported records:

Example 2 – 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 3 – 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 4 – 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 several issues 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 5 – 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 entire Transportation Policies table to Excel or CSV, which is explained in the next section below.
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.

Exporting to CSV/Excel Files

It is possible to export a single table or multiple tables (input and output tables) to CSV or Excel from Cosmic Frog. Similar to importing data from CSV/Excel, user can access the export options in 2 ways: from the File menu in the toolbar and from the context menus that come up when right-clicking on tables in the input/output/custom tables lists.

Please note:

• One important difference between the 2 ways of accessing the export options is that it is only possible to export multiple tables simultaneously by using the File menu in the toolbar; right-clicking on a table and using the context menu to export to CSV/Excel is limited to one table at a time.
• If an output table is filtered when exporting it, only the filtered rows are exported.

Export Multiple Tables to Excel

The steps to export multiple tables to an Excel file are as follows:

1. Select the tables to export from the Data module in Cosmic Frog, holding down the Ctrl-button to select multiple tables. This can be from the Input Tables section (as shown here), from the Output Tables section, or the Custom Tables section. In this example, user has selected three input tables: Customers, Facilities, and Products.
2. Since user wants to export these 3 selected tables to Excel in one go, they need to use the Export Excel option from the File menu in the toolbar.

Once the export starts, following message appears at the top of the active table:

Once the export is complete, the exported file can be found in the folder where user’s downloaded files are saved:

When exporting multiple tables to Excel or CSV, the downloaded file will be a .zip file with an automatically generated name based on the model’s Cosmic Frog ID. Extracting the zip-file will show an .xlsx file of the same name, which can be opened in Excel:

1. The name of the file which was autogenerated by Cosmic Frog. Here it is also indicated that the file is opened in “Protected View” mode.
2. An explanation of the Protected View mode is given here; user can choose to click on Enable Editing to be able to work with the contents of the file.
3. There are 3 worksheets in the file, which match the names of the exported Cosmic Frog tables.
4. The Customers worksheet is showing and we see that the column names are those used in Cosmic Frog (just converted to be all lowercase). We also notice that all columns have been exported, not just the ones that are populated.

Export Multiple Tables to CSV

These are the steps to export multiple tables to CSV:

1. In this case, user wants to export multiple output tables to CSV, so the Output Tables section of the Data module is selected.
2. User has selected 2 tables, the Simulation Order Report and the Simulation Shipment Report.
3. To export to CSV, user chooses the Export CSV option from the File menu.

When the export starts, the same “File is exporting…” message as shown in the previous section will be showing at the top of the active table. Once the export process is finished, the exported file can again be found in the folder where user’s downloaded files are saved:

The file is again a zip-file, and it has the same name based on the model’s Cosmic Frog ID, just appended with (1), as there is already a zip-file of the same name in the Downloads folder from the previous export to Excel. Unzipping the file creates a new sub-folder of the same name in the Downloads folder:

1. The sub-folder created in the Downloads folder which has the same auto-generated name as the zip-file.
2. The 2 output tables exported as CSV. Their names match the names of the output tables that were exported.

Export Single Table to Excel

Exporting a single table to Excel can also be done from the File menu, in the same way as multiple tables are exported to Excel, which was shown above in the “Export Multiple Tables to Excel” section. Now, we will show the second way of doing this by using the context menu that comes up when right-clicking on a table:

1. User wants to export 1 output table to Excel, so the Output Tables section of the Data module is selected by clicking on the round grid icon.
2. User wants to export the Simulation Shipment Report and right-clicks on this table.
3. Export Excel is selected from the File menu.

When the export starts, the same “File is exporting…” message as shown above will be showing at the top of the active table. Once the export process is finished, the exported file can again be found in the folder where user’s downloaded files are saved:

The name of the exported CSV file matches that of the table that was exported.

Export Single Table to CSV

Exporting a single table to CSV can also be done from the File menu, in the same way as multiple tables are exported to CSV, which was shown above in the “Export Multiple Tables to CSV” section. Now, we will show the second way of doing this by using the context menu that comes up when right-clicking on a table:

1. User wants to export the Transportation Policies input table to Excel. When looking at this table, we notice it is filtered:
   1. At the top of the table, it is indicated that only 27 of the 8,189 rows in the table are being shown.
   2. We see the 2 filter tags for Origin Name and Destination Name at the top right of the table – these are the 2 tables that have filter criteria on them.
   3. The filter icons to the right of the Origin Name and Destination Name column names are blue, indicating these columns are filtered. This icon is black for non-filtered columns (like Product Name in this screenshot).
2. User right-clicks on the Transportation Policies input table to bring up the context menu.
3. Export CSV is chosen from the File menu.

When the export starts, the same “File is exporting…” message as shown above will be showing at the top of the active table. Once the export process is finished, the exported file can again be found in the folder where user’s downloaded files are saved:

For single tables exported to CSV, the name of the file is the same as the name of the exported table. If the Cosmic Frog table was filtered, the file name is appended with “_filtered” like it is here to remind user that only the filtered rows are contained in this exported file.