The Data Extract tool allows you to easily create CSV files from an existing database. The Extract Data window can be accessed via the "Tools > Extract Data..." menu item in Cycle. It can also be accessed from the Inspector panel with the funnel-shaped button:

Using A Data Extract Model

After opening the Extract Data window, clicking "Add model..." will open a file browser to select a model. A Data Extract Model has the file extension .cycextract. Once a model has been added, it will automatically be copied to a folder named "DataExtractModels" in the currently open Cycle tab's project directory. It can then be found in the "Select extract model" dropdown on the Extract Data window.

The JDA Feature Bundle includes extract models for Non-Picked Inventory, Master Receipts, Orders, Shipments and Terminals.

Note: To create a custom extract model you will need schema knowledge of the database as well as working knowledge of JSON.

This is an example of a .cycextract that extracts terminal data from a JDA WMS:

{
 "name": "JDA_SCE_2017_RDT_EXTRACT",
 "type": "moca",
 "format": "csv",
 "params": [
 {
 "name": "wh_id",
 "description": "WH_ID",
 "type": "String"
 },
 {
 "name": "rf_ven_nam",
 "description": "RF_VEN_NAM",
 "type": "String"
 },
 {
 "name": "filter",
 "description": "FILTER",
 "type": "String"
 }
 ],
 "steps": [
 {
 "fileName": "rf_term_mst",
 "script": [
 "if ($filter = '' or $filter is null) { publish data where filter = '%' } else {publish data where filter = $filter } | [select * from rf_term_mst where wh_id = $wh_id and rf_ven_nam = $rf_ven_nam and devcod like @filter]"
 ]
 },
 {
 "fileName": "rftmst",
 "script": [
 "if ($filter = '' or $filter is null) { publish data where filter = '%' } else {publish data where filter = $filter } | [select * from rf_term_mst where wh_id = $wh_id and rf_ven_nam = $rf_ven_nam and devcod like @filter] | [select * from rftmst where devcod = @devcod and wh_id = @wh_id] catch(@?)"
 ]
 },
 {
 "fileName": "devmst",
 "script": [
 "if ($filter = '' or $filter is null) { publish data where filter = '%' } else {publish data where filter = $filter } | [select * from rf_term_mst where wh_id = $wh_id and rf_ven_nam = $rf_ven_nam and devcod like @filter] | [select * from devmst where devcod = @devcod and wh_id = @wh_id] catch(@?)"
 ]
 },
 {
 "fileName": "locmst",
 "script": [
 "if ($filter = '' or $filter is null) { publish data where filter = '%' } else {publish data where filter = $filter } | [select * from rf_term_mst where wh_id = $wh_id and rf_ven_nam = $rf_ven_nam and devcod like @filter] | [select * from locmst where stoloc = @devcod and wh_id = @wh_id] catch(@?)"
 ]
 }
 ]
}

This is an example of a .cycextract that extracts Test Parameter Data from the Cycle Data Store:

{
"name" : "Cycle Test Plan Extract",
"type" : "sql",
"format" : "csv",
"params" : [ {
"name" : "id",
"description" : "TEST PLAN ID",
"type" : "String"
} ],
"steps" : [ {
"fileName" : "test_plan",
"script" : [ "select * from test_plan where id = $id;" ]
}, {
"fileName" : "test_data_set",
"script" : [ "select * from test_data_set where test_plan_id = $id;" ]
}, {
"fileName" : "test_data_record",
"script" : [ "select * from test_data_record where test_data_set_id in (select tds1.id from test_data_set tds1 where tds1.test_plan_id = $id);" ]
}, {
"fileName" : "test_data_column",
"script" : [ "select * from test_data_column where test_data_set_id in (select tds1.id from test_data_set tds1 where tds1.test_plan_id = $id);" ]
}, {
"fileName" : "test_data_value",
"script" : [ "select * from test_data_value where test_data_column_id in ( select tdc1.id from test_data_set tds1,test_data_column tdc1 where tds1.test_plan_id = $id and tds1.id = tdc1.test_data_set_id) order by id;" ]
}, {
"fileName" : "test_data_record_tag",
"script" : [ "select * from test_data_record_tag where test_data_record_id in( select tdr1.id from test_data_record tdr1, test_data_set tds1 where tdr1.test_data_set_id = tds1.id and tds1.test_plan_id = $id) order by id;" ]
}, {
"fileName" : "test_data_tag",
"script" : [ "select * from test_data_tag where id in( select tdrt1.test_data_tag_id from test_data_record tdr1, test_data_set tds1, test_data_record_tag tdrt1 where tdr1.test_data_set_id = tds1.id and tdr1.id = tdrt1.test_data_record_id and tds1.test_plan_id = $id) order by id;" ]
} ]
}

Extracting data from a database

Enter a new Dataset Name or select an existing dataset from the dropdown. Select the Data Extract Model from the dropdown for this extraction. JDBC or MOCA will be selected automatically based on your Data Extract Model. For JDBC databases, select the database type from the dropdown and enter the database name. Both MOCA and JDBC databases require entering the database host, username, and password containing the data to be extracted. The Input Parameters table at the bottom of the window allows you to enter in values to extract from the database. The Keys and Types are auto-created based on the Data Extract Model.

Once this information has been completed, click the "Extract data" button. Once the extract completes a dialog window will be presented detailing any errors with the extract or that the extract completed successfully. If successful, the resulting CSV file can be found in your currently open Cycle tab's resource directory in a folder with Dataset Name given in the Extract Data window. A .cycextractin file will also be created from the information entered into the Input Parameters table.

Using the dataset

Now that the dataset has been created, use either of the following Steps to put it to use:

I execute MOCA dataset “<PATH_TO_DATASET>“
I execute SQL dataset “<PATH_TO_DATASET>“

When using these Steps, it will look for any filename beginning with “cleanup” and ending with “sql or msql”. If you want to validate something, a validation script will run if you have a filename that begins with “validate” and ends with “sql or msql”; this also applies to "load...".

Files are run in this order (none are required):

• Cleanup
• Load
• Then CSVs are imported into Cycle (inserted into database)
• Validate