Transform map onStart script to check if trailer exists in csv file, if not abort the transaction.

Lavanya6 · ‎05-25-2020

I am running Transform map to insert and update user records. Data source is a csv zipped file. My logic is not working when i am checking for trailer in the source.

Requirement is:

1.If the source field does not have trailer information (as in the attachment) the transaction should get aborted that is the transform map should not happen.

2.If the record count from source does not match the record count in the transform map table the transaction should be aborted.

Ankur Bawiskar · ‎05-29-2020

Hi Lavanya,

here is the script; I tried in onStart transform script but somehow it didn't work out.

It worked well when added in onBefore transform script

script below

Note: Ensure you give proper

1) import set table name; I have used u_testing_data_load_csv

2) correct import set field name; -> u_recordcount and u_h

(function runTransformScript(source, map, log, target /*undefined onStart*/ ) {

	// Add your code here
	var trailerCharacter = '';

	var recordCountField;
	var gr1 = new GlideRecord('u_testing_data_load_csv');
	gr1.orderByDesc('sys_import_row');
	gr1.addQuery('sys_import_set', source.sys_import_set);
	gr1.query();
	if(gr1.next()){
		recordCountField = gr1.u_recordcount;
		trailerCharacter = gr1.u_h;
	}

	if(trailerCharacter != 'T'){
		log.error('Missing trailer row as the character T cannot be found in column H');
		error = true;
	}
	else{
		// found the tralier row now check whether the count of rows is same as record count column

		var gr = new GlideRecord('sys_import_set_row');
		gr.addQuery('sys_import_set', source.sys_import_set);
		gr.query();
		var totalRowsIncludingTrailer = gr.getRowCount();

		var actualDataRows = totalRowsIncludingTrailer - 1;

		if(actualDataRows != recordCountField){
			log.info('Total rows are not matching hence setting error as true and it would halt entire transformation');
			ignore = true;
			error = true;
		}
		else{
			// allow transform to run
		}
	}

})(source, map, log, target);

Mark ✅ Correct if this solves your issue and also mark 👍 Helpful if you find my response worthy based on the impact.
Thanks
Ankur

Regards,
Ankur
✨ Certified Technical Architect || ✨ 9x ServiceNow MVP || ✨ ServiceNow Community Leader

View solution in original post

Ankur Bawiskar · ‎05-28-2020

Is there any update on this? Is this question resolved or you need some more assistance.

Regards
Ankur

Regards,
Ankur
✨ Certified Technical Architect || ✨ 9x ServiceNow MVP || ✨ ServiceNow Community Leader

Lavanya6 · ‎05-28-2020

Hi Ankur,
I am still looking for resolution.

Thanks