Populate a Lookup Table from a CSV File import java.util.Properties; import java.io.*; import Thor.API.Operations.tcLookupOperationsIntf; import Thor.API.tcResultSet; import Thor.API.tcUtilityFactory; import com.thortech.xl.util.config.ConfigurationClient; /* * Tool to import a Lookup Definition table from a file * * The import file is in CSV format, format "key,value" * The Lookup Definition must already exist * * Algorithm: * * Open CSV File * Get XL API * Get Lookup Table * Delete each entry of the lookup table * For each line of the CSV File, * Parse key-value * Add the new key-value to the lookup table * * @author Vinh-An Trinh (vinhant@zerointech.com) */ public class ImportLookupValues { public static void help() { System.out.println("Imports a Lookup Definition Table from a CSV file./n"); System.out.println("The Lookup Definition Table must already exists./n"); System.out.println("Its content will be deleted prior to import/n"); System.out.println("Usage:/n" + "username password lookup_definition_code csv_file"); } /* * arg[0] : username * arg[1] : password (eg xelsysadm) * arg[2] : Lookup Definition Code. Must exist. * arg[3] : CSV file to import. Format : key, value */ public static void main(String args[]) { if (args.length < 3) { help(); return; } try { BufferedReader in = new BufferedReader(new FileReader(args[3])); // Get the Lookup operation API Properties jndi = ConfigurationClient.getComplexSettingByPath( "Discovery.CoreServer").getAllSettings(); tcUtilityFactory tcutilityfactory = new tcUtilityFactory(jndi, args[0], args[1]); tcLookupOperationsIntf lookupIntf = (tcLookupOperationsIntf) tcutilityfactory.getUtility( "Thor.API.Operations.tcLookupOperationsIntf"); tcResultSet result = lookupIntf.getLookupValues(args[2]); // Delete all the current values of the lookup int rowCount = result.getRowCount(); String value = ""; for (int i=0; i<rowCount; i++) { result.goToRow(i); value = result.getStringValue( "Lookup Definition.Lookup Code Information.Code Key"); System.out.println("deleting " + value); lookupIntf.removeLookupValue(args[2], value); } // Insert new values String line; while (null != (line = in.readLine())) { if (line.startsWith("#")) continue; int idx = line.indexOf(','); if (idx < 0) { System.out.println("Cannot parse key-value from line : " + line); continue; } String key = line.substring(0, idx); String val = line.substring(idx+1); if (val.charAt(0) == '"') val = val.substring(1); if (val.charAt(val.length()-1) == '"') val = val.substring(0, val.length()-1); String lang = "en"; String country = "US"; System.out.println(key + "," + val); lookupIntf.addLookupValue(args[2], key, val, lang, country); } } catch (Exception ex) { ex.printStackTrace(); } System.exit(0); } }