{"id":14936,"date":"2021-09-20T15:44:21","date_gmt":"2021-09-20T19:44:21","guid":{"rendered":"http:\/\/www.iri.com\/blog\/?p=14936"},"modified":"2024-01-05T11:29:03","modified_gmt":"2024-01-05T16:29:03","slug":"multi-format-test-data","status":"publish","type":"post","link":"https:\/\/beta.iri.com\/blog\/test-data\/multi-format-test-data\/","title":{"rendered":"Creating CSV, JSON, XML and RDB Test Data"},"content":{"rendered":"

This article demonstrates how to synthesize and write data to CSV, JSON, and XML files, plus an Oracle table all at once via the IRI RowGen<\/a> New Test Data Job<\/i> wizard in IRI Workbench<\/a>. If you are interested in synthesizing and populating an entire RDB schema with referential integrity however, see this article<\/a>.<\/p>\n

The IRI Workbench screenshot below displays the RowGen script produced by the wizard along with its transform mapping diagram, plus the target test data written by the RowGen job into an Oracle table.<\/p>\n

\"\"<\/a><\/p>\n

Data Preparation<\/b><\/h6>\n

RowGen users can synthesize test data in multiple ways:<\/p>\n

    \n
  1. Random generation of characters and digits by data type and length (with allowances for frequency distribution)<\/li>\n
  2. Random selection of real or generated values from external set files<\/a>, or from values in database (lookup<\/a>) tables<\/li>\n
  3. Custom string\/format definition and random generation of the component values<\/li>\n
  4. Random generation of computationally valid or invalid values of known string types like credit cards (CCN), dates, national ID (e.g., social security) numbers, etc.<\/li>\n
  5. Subsetting and masking of data in existing production files or related database tables<\/li>\n<\/ol>\n

    Set files are text files composed of one or more tab-separated columns or ranges.\u00a0 They can be created from a number of different sources, using a number of techniques.<\/p>\n

    For example, they can be created with a text editor, with an IRI job or wizard that extracts column values from files or wizards, SQL commands, etc. In addition, many sample set files are also included in the base RowGen (CoSort) package found under the $COSORT_HOME folder.<\/p>\n

    In this demonstration, I will be using several set files from the IRI-provided folder, including names_last.set<\/i>, street_addresses.set<\/i>, and state_city.se<\/i>t. I will also use random value generation, and the Data Generation Rule<\/i><\/a> wizard to synthesize \u201cvalid\u201d CCN and NID values.<\/p>\n

    Start the Job<\/b><\/h6>\n

    From the top toolbar menu, select the New Test Data Job<\/i> wizard from the RowGen menu. Enter a filename (RowGen jobs are created with .rcl extension).<\/p>\n

    Select the Create Script<\/b> radio button and click Next<\/b>:<\/p>\n

    \"\"<\/a><\/p>\n

    Define the Test Fields to Generate<\/b><\/h6>\n

    By default, RowGen will generate random values for the data type specified for each field (the default is ALPHA_DIGIT). However, the data types and lengths can be changed. Here I have used ASCII and ISO_TIMESTAMP data types as well.<\/p>\n

    Next on the Data Sources page, you will see for Infile only \u201crandom_place_holder\u201d to indicate dummy data is being produced (vs. reading from a production source). The Incollection option refers to the number of records\/rows you want to generate in this phase (the default is 100).<\/p>\n

    Then click on Source Field Layout <\/b>where a data layout screen is provided for each named test field you will generate.<\/p>\n

      \n
    1. Click on the F<\/b> icon to add a field<\/li>\n
    2. Double-click on the field to open the Field Layout Editor to handle attributes like the field name, data type, position and, if desired, a rule like a set file value lookup.<\/li>\n<\/ol>\n

      The screenshots below display the process of creating a field that will get values from a set file.<\/p>\n

      \"\"<\/a><\/p>\n

      \"\"<\/a><\/p>\n

      \"\"<\/a><\/p>\n

      Adding Rules to Create Credit Card and Social Security Numbers<\/b><\/h6>\n

      To add a rule, right click on the field in the Target Field Layout editor to bring up the context menu. Then, select the menu item Add Rule<\/b>. Select the Create Rule<\/b> sub item to define a new rule, or select Browse Rule<\/b> to re-use a previously-defined rule.<\/p>\n

      Choosing to create a new rule will open the New Rule wizard. To generate credit card numbers, select Credit Card Number Generator<\/b> under Generation Rules. There are two options to generate credit cards. In this instance, the second option was selected that allows the credit card type and separator to be specified.<\/p>\n

      \"\"<\/a><\/p>\n

      A Social Security (or similar) number rule can be created from the list of National ID Functions<\/i>.<\/p>\n

      The image below displays the final field layout.<\/p>\n

      \"\"<\/a><\/p>\n

      Next, we will use the Data Targets dialog to define the output files and their layout(s). Note that because RowGen uses the same SortCL metadata and executable as CoSort, NextForm, FieldShield, and Voracity, it is also possible to reformat and transform the generated data in the target phase.<\/p>\n

      Define the Targets<\/b><\/h6>\n

      Click Next <\/b>at the bottom of the Test Data Definition dialog to begin specifying the data targets and their field layouts.<\/p>\n

      The first output file will be in CSV format. In this case, I named the target report.csv<\/i> and assigned the process type (file format) to be CSV:<\/p>\n

      \"\"<\/a><\/p>\n

      Clicking OK <\/b>displays the generated fields mapped by default into a target tab with my file name. In the Target Field Layout Editor dialog shown below, you can add rules to each field, and a new locator (field name) assignment specific to the format (in this case CDEF for CSV):<\/p>\n

      Format specific location definitions can be important when the location of that field in the target needs to have another name, case, or even formatting syntax, like an XML path structure. Localized fields may also be incompatible with IRI \/FIELD naming conventions, too.<\/p>\n

      When you click on the DEF icon in the target, you will see an Add Location Definition page. In its drop-down menu shown below, you can add DEFs for CSV files (CDEF), JSON (JDEF), XML (XDEF), and ODBC (ODEF) for SQL-compatible RDB column names. There is also support for LDIF (LDEF) file, MongoDB (MDEF) document, and Excel (SDEF) spreadsheet locators, too.<\/p>\n

      \"\"<\/a><\/p>\n

      Below is a screenshot of the CSV output file produced by this part of the RowGen job:<\/p>\n

      \"\"<\/a><\/p>\n

      Creating JSON output<\/b><\/h6>\n

      The next target from the same generation job will be a JSON file containing my test data. In this screen I specify the format as JSON and name the file:<\/p>\n

      \"\"<\/a><\/p>\n

      I was also able to automate the generation of optional JDEF locators in the specs. The screenshot below show the script, mapping diagram and both the CSV and JSON test files produced by the job:<\/p>\n

      \"\"<\/a><\/p>\n

      Follow along with our YouTube video creating a JSON file!<\/strong><\/p>\n