Compass XML and CSV Import Assistant

In my previous blog post, I presented a simple Perl application that addressed the tedious and difficult task one encounters when importing data into HCL Compass; that is, determining how to define the data types of fields within a Compass 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 Compass schema to correct the issue.

The previously posted HCL Compass Import Assistant did a very simple parse of a CSV file and produced a recommendation for how to correctly define the Compass data types. 
 
I’ve modified that previously posted Compass 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 Compass. Now consider the following 10,000 records of XML data to be imported into Compass: 

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 Compass. However, the Compass XML and CSV Import Assistant will parse the entire 10,000+ XML records and provide recommendations on how to define your Compass data types. 
 
Below is an example of running the Compass XML and CSV Import Assistant against our sample data:  

In this example, we see that the Compass 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 Compass data type for each field to be imported. 
 
By running this Perl application on your CSV or XML data prior to importing into Compass, you can save a great deal of time and effort in defining your Compass schema and importing your data into Compass. 
 
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 

# 

# Compass XML and CSV Import Assistant 

# 

# One of the most difficult thing about bringing a Compass 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“);