Rational Heritage

 View Only

ClearQuest XML and CSV Import Assistant

By Divya Koppolu posted Tue June 08, 2021 12:47 PM

  
Author Credits:
Tom Milligan
Senior Area Sales Director, Software Change and Configuration Management

In my previous blog post, I presented a simple Perl application that addressed the tedious and difficult task one encounters when importing data into ClearQuest; that is, determining how to define the data types of fields within a ClearQuest record.  If the data types are defined incorrectly, the import of data will fail because of a type mismatch and you will need to modify your ClearQuest schema to correct the issue.
 

The previously posted ClearQuest Import Assistant did a very simple parse of a CSV file and produced a recommendation for how to correctly define the ClearQuest data types.

I’ve modified that previously posted ClearQuest Import Assistant to improve the robustness of the CSV parsing by using the Parse::CSV package available from CPAN and to expand the capabilities of the app by introducing the ability to operate on XML files.

 In the previous blog entry, we considered a 10,000+ line CSV file where each line represented a record to be imported into ClearQuest. Now consider the following 10,000 records of XML data to be imported into ClearQuest:

XML Data

<field name="Submit_Date">2000-06-15 00:00:00</field>
     <field name="Submitter">engineer</field>
     <field name="Symptoms"/>
     <field name="version">1</field>
   </record>
   <record>
     <field name="Description"/>
     <field name="Headline">sales tax incorrect if item deleted from purchase</field>
     <field name="history">33554474    7/25/2017 3:13:21 PM    admin   Import  Resolved    Resolved
33554514    8/7/2000 6:53:00 PM QE  Open    Submitted   Opened
33554586    6/15/2000   engineer    Submit  no_value    Submitted</field>
     <field name="id">DB100000002</field>
     <field name="Keywords"/>
     <field name="Note_Entry"/>
     <field name="Notes_Log"/>
     <field name="old_id">CLSIC00000002</field>
     <field name="Owner">lead</field>
     <field name="Priority">2-Give High Attention</field>
     <field name="ratl_mastership">&lt;local&gt;</field>
     <field name="record_type">Defect</field>
     <field name="Resolution">Fixed</field>
     <field name="Resolution_Statetype">Resolved</field>
     <field name="Severity">1-Critical</field>
     <field name="State">Resolved</field>
     <field name="Submit_Date">2000-06-15 00:00:00</field>
     <field name="Submitter">engineer</field>
     <field name="Symptoms"/>
     <field name="version">1</field>
   </record>
   10,000 more records
​

It would be difficult to visually parse this data to determine the data type for each of the many fields to be imported into ClearQuest.  However, the ClearQuest XML and CSV Import Assistant will parse the entire 10,000+ XML records and provide recommendations on how to define your ClearQuest data types.
 
Below is an example of running the ClearQuest XML and CSV Import Assistant against our sample data:

In this example, we see that the ClearQuest XML and CSV Import Assistant has parsed the XML data, performed some analysis on it and has provided recommendations for how to define the right ClearQuest data type for each field to be imported.

By running this Perl application on your CSV or XML data prior to importing into ClearQuest, you can save a great deal of time and effort in defining your ClearQuest schema and importing your data into ClearQuest.

The source code for this application is provided below so that you can use it as-is or modify it to meet your specific needs.

#!perl.exe
#
# ClearQuest XML and CSV Import Assistant
#
# One of the most difficult thing about bringing a ClearQuest eval/install
# up is defining the data types in the schema that will be used to hold
# the data from a pre-existing CR Tracking tool.  It is trial-and-error 
# with mostly error.
#
# This is a Perl script that will parse an *entire* csv or xml file
# to be used for the CQ import and will produce a recommendation for how to
# define the data types.
#
# Note that it uses the "Date::Manip", "Parse::CSV", "XML::Simple", and "Getopt::Long" Perl packages, 
# so you will need those in your Perl installation.
#
#   Author: Tom Milligan
#
use Date::Manip;
use Parse::CSV;
use XML::Simple;
use Getopt::Long;
my $dataFormat = "csv";
my $inputFile = "";
my $outputFile = "";
GetOptions("format=s" => \$dataFormat,
       "o=s" => \$outputFile,
       "i=s" => \$inputFile)
or die("Usage: $0 [-format {xml,csv}] [-o outputfile] -i inputfile\n");
​




0 comments
13 views

Permalink