i18n - bulk loading translation data

kevinanderson · ‎06-05-2017

So,

We just got done implementing Spanish in our instance, and I want to share with you something that took us many hours to get working correctly - transform maps for sys_translated_text, sys_choice, sys_documentation, sys_translated, and sys_ui_message.

According to SN, the out of box transform maps that come with the language pack plugins are not for use by customers, and are only intended for use by the plug-in process.

So, we had to copy and modify these transform maps to work correctly. We had thousands of records related to our translation data, and using XML export / import was not practical as the files were very large and took many hours to import into the target instance ( we had to break them into 2k row 'chunks' to get them to process at all without timing out ).

I spent several hours on the phone with HI support building these transform maps, and I would like to present them to the community, so that others shall not suffer the same pain we did migrating language data manually.

After building these transform maps, we were able to move ~65k records in only a few minutes.

The key to this is:

1. copy the oob transform maps, and disable the scripts - instead do a direct field mapping

2. copy the coalesce field settings from the oob transform maps fields to the custom transform maps

3. when exporting the data, ensure all the fields in the transform map are in the export

4. add the document key sys_id to the sys_translated_text table to accommodate the transform map (done in attached update set)

5. convert the XLSX to XLS before importing to target system for the transform ( I hear this will be fixed in Jakarta )

6. use the following onbefore script for the document key field on sys_translated text (Thanks to Chris Beltran @snc / Rod Godfrey @ snc for providing this basis for this script)

(function runTransformScript(source, map, log, target /*undefined onStart*/ ) {
   // validate the document key can be built - table exists and target record can be found in the table
   gs.include('j2js');
   var isValidTable = function(table_name) {
                       result = false;
                     
                       if (JSUtil.notNil(table_name)) {
                               var gr = new GlideRecord('sys_db_object');
                               if (gr.get('name', table_name)) {
                                       result = true;
                               }
                       }
                       //gs.log('isValidTable for '+table_name+': '+result, 'transform map - sys_tanslated');
                       return result;
               };
               
   // string trim polyfill
               String.prototype.trim || (String.prototype.trim = function() {
                       return this.replace(/^[\s\uFEFF\xA0]+|[\s\uFEFF\xA0]+$/g, "");
               });
               var _doc = source.u_document.toString();
               var _tbl = source.u_table_name.toString();
                             
               var table_name = (_tbl).trim();
   var process_record = false;
   if (isValidTable(table_name)){
   if (JSUtil.notNil(source.u_document_key_sysid)){
   // search for the document key sys_id in the target table
   var _gr = new GlideRecord(table_name);
   if (_gr.get(source.u_document_key_sysid)) {
   process_record = true;
   }
   else{
   gs.log('Warning: unable to find matching record with sys_id: ' + source.u_document_key_sysid+' in table '+table_name, 'transform map - sys_tanslated');
   }
   }
   else{
   gs.log('Warning: invalid document key sys_id for record: ' + _doc, 'transform map - sys_tanslated');
   }
   }
         else{
   gs.log('Warning: invalid table: '+table_name,'transform map - sys_tanslated');
   }
         // if the document key sysid or the target table not found, skip this record
         if (!process_record){ 
   ignore = true;
   }
})(source, map, log, target);