Using SpotlightXL-Model Import and Export Data Subtask

Overview

Import and Export model data in text and CSV formats. Access the Import/Export Data page (shown below) by selecting the Model task and the Import/Export Data subtask. You can import zipped files in text and CSV format that were exported from Dynamic Planning.

Export Data

With the Export Data subtask you can export the leaf level data from a model into a file which can be used for loading reporting tools. You also can filter the data and export the data to either a text or csv format. After you export, the data is available to you in a zipped format. For example, you specify filter settings for a specific scenario or a specific time period and choose a file format (.txt/.csv) for export.

Actions Available

The table below provides a description of the actions available for the Export Data subtask.

Model - The model you want to export.

Name - Enter the name for exporting.

File Type - Specify the output file format: text (.txt) or CSV (comma delimited value).

Row Delimiter - Separates each row with the selection your make ({CR}{LF}/{CR}/{LF}).

Column Delimiter - Separates each column with the selection you make (Tab, Comma, Semicolon).

Text Qualifier - Differentiates your field data from your delimiter (None/Double Quotes/Single Quote).

Dimension Member Display - Allows you to dimension members either with Code or Display Label.

Data - Select Leaf to export the leaf data. Always export leaf data.

Suppress Zero Values - Select Yes to suppress zero values from the data you are exporting as shown below.

Filters Described

For all the dimensions that are available within the model you can pick filters. The values for filters are discussed below.

Dimension - Specify the name of dimension that is available in model for exporting.

Filter -

• AllMembers – Export all members from the source model to the target model.

• Leaf Members – Export leaf members only from the source to target model.

• MemberAndBelow - Members at a specified level where a member name exists as well as all members below that level in the hierarchy will be exported.

• FixedMember – The dimension displays data from a set member based on member name. For example, the scenario dimension only displays a 2020 Budget scenario and all other scenarios (Actual, What If, etc.) are not exported.

Value - Specify the value required for dimensions for exporting. For example, if you select Fixed Member for the Account dimension, select/enter the account name.

How to Create an Export?

1. Select the Model task and the Import/Export Data subtask.
   
   

2. Select the Model you want to export data from.

3. Enter the name of the export.

4. For File Type , select the format of file for exporting.

5. In the Row Delimiter cell, the default is {CR}{LF}, which means carriage return line feed.

6. In the Column Delimiter cell, select the column delimiter to separates each column with a tab, semicolon, or comma. 

7. In the Text Qualifier cell, specify the character you want the system to use to enclose values in the file. This function is important because it separates the field data from the delimiter. You may select to separate the two using single quotes or double quotes. If you choose none, there will be no separator to distinguish between the delimiter and field data. 

8. For Dimension Member Display , select how you want the dimension member to be displayed using either Code/Display Label.

9. In the Data cell, always choose leaf data to export all leaf-level data.

10. Choose the required filter options for the dimensions from the table.

11. Click Save.
    

12. Click Export To File . This exports your data as a zipped file with the name and file format that were provided in Export Data settings page.
    
    

13. 
    Enter a file name and click Save as shown below.
    
    
    An example of an exported text file is shown below.
    
    
    

Available Actions Described

The table below shows the actions that are available with the Export Data subtask.

Refresh - After a selection is made, click Refresh to view updated data.
- Exports data to the specified file format. Or, import data. Since Export Data is an administrative function, it will export the entire data based on the filters defined, and hence dimension security is not regarded. By default, Export Data subtasks are not enabled for Contributor users. For example, if a Contributor user has access only to the Sales department in a Model and if the same Contributor user has access to the Export Data subtask, then that user can create the export format with all departments included and export the data to a file which exports all departments' data. Thereby the Contributor user has access to the data. To prevent the Contributor user from accessing, do not enable access on Export Data for Contributor users.

Save - Save a predefined export data settings.

Delete - Deletes the export data settings.

How to Import Data?

1. Click the Import to File action.
   
2. Select the exported zip file from the browser window and click Open. User will receive an email based on the success of the import. Any exceptions occurring during the import will be emailed.