Compass Import Assistant

One of the difficult tasks in performing an import of external data into a HCL Compass database is determining how to define the data type of a field within a Compass record.  If you define it 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 Compass Import Assistant is a simple Perl application that will parse an entire CSV file to be imported into Compass and will produce a recommendation for how to correctly define the Compass data types. 
 
For example, consider the following 10,000+ lines of data, each representing a record to be imported into Compass: 
 
“dbid”,”Headline”,”id”,”old_id”,”Owner”,”Priority”,”ratl_mastership”,”record_type”,”Resolution”,”Severity”,”State”,”Submit_Date”,”Submitter”,”version” 
“33554446”,”clerk allowed to charge too much on credit card”,”DB100000014″,”CLSIC00000014″,”engineer”,”3-Normal Queue”,”<local>”,”Defect”,”Fixed”,”4-Minor”,”Resolved”,”7/8/2000 0:00″,”lead”,”1″ 
“33554447”,”end-of-shift report fails if after midnight”,”DB100000015″,”CLSIC00000015″,”engineer”,”3-Normal Queue”,”<local>”,”Defect”,”Fixed”,”3-Average”,”Resolved”,”7/8/2000 0:00″,”lead”,”1″ 
… 
10,000 more records 
… 
“33554449”,”delete operation leaves blank line in form”,”DB100000017″,”CLSIC00000017″,”engineer”,”3-Normal Queue”,”<local>”,”Defect”,”Fixed”,”3-Average”,”Resolved”,”7/8/2000 0:00″,”lead”,”1″ 
“33554450”,”add item button not active after adding 3 other items”,”DB100000018″,”CLSIC00000018″,”lead”,”1-Resolve Immediately”,”<local>”,”Defect”,”Fixed”,”1-Critical”,”Resolved”,”7/8/2000 0:00″,”lead”,”1″ 
“33554451”,”sales tax incorrect for NH”,”DB100000019″,”CLSIC00000019″,””,””,”<local>”,”Defect”,””,”1-Critical”,”Submitted”,”7/8/2000 0:00″,”lead”,”1″ 

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

In this example, we see that the Compass Import Assistant has parsed the 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 simple Perl application on your 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. 

Source Code 

#!perl.exe 

# 

# Compass Import Assistant 

# 

# One of the most difficult things 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 file 

# to be used for the CQ import and will produce a recommendation for how to 

# define the data types. 

# 

# Eventually, I may get around to putting a TK interface on it… 

# 

# Note that it uses the “Date::Manip” Perl package, so you will need that in 

# your Perl installation. 

# 

#   Author: Tom Milligan 

# 

use Date::Manip; 

sub usage 

{ 

   die “Usage: $0 [-o outputfile] input.csv\n”; 

} 

# 

# Rudimentary check for correct number of args 

# 

if ($#ARGV > 2) {