csvデータからインシデントレコードを作成/削除する方法

Go to solution
d-aizawa
Kilo Sage

‎10-13-2022 09:38 PM

コミュニティの皆様
いつもお世話になっております。

 

私は、Business Ruleを作成して、

CSVデータからインシデントの作成および削除を行いたいです。

それぞれの条件としては、以下になります。

・作成対象：CSVにあり、インシデントレコードには存在しないデータ(short_descriptionで判別)

・削除：CSVになく、インシデントレコードに存在するデータ

 

しかし、スクリプトを実行すると、以下のエラーが表示されました。

Parsed value count is not equal to header count for CSV record

 

こちらについて、エラーの意味がよくわからずどう改善すれば良いかわかりません。

よろしくお願いいたします。

(function executeRule(current, previous /*null when async*/ ) {
    var delimiter = ',';
    var quoteCharacter = '"';
    var columnNames = ["short_description", "description"];

    var attachment = new GlideSysAttachment();
    var gr_attachment = new GlideRecord("sys_attachment");
    gr_attachment.orderByDesc('sys_created_on');
    gr_attachment.setLimit(1);
    gr_attachment.query();
	gs.info("【CSV Check】csv：" + gr_attachment.hasNext());

    while (gr_attachment.next()) {
        var file_name = gr_attachment.file_name;
		gs.info('file_name:' + gr_attachment.file_name);
			var attachment_sys_id = gr_attachment.sys_id;
			var attachmentStream = attachment.getContentStream(attachment_sys_id);
            var reader = new GlideTextReader(attachmentStream);
            var csvLine = ' ';
            var isHeader = true;
			while ((csvLine = reader.readLine()) != null) {
                if (isHeader) { // first line is header so skip
                    isHeader = false;
                    continue;
				}
				gs.info("【CSV Check】csvLine", + csvLine);
				var x = new sn_impex.CSVParser().parseLineToObject(csvLine, columnNames, delimiter, quoteCharacter);
				gs.info("【CSV Check】x:" + JSON.stringify(x));
				gs.info("【CSV Check】short_description:" + x.short_description);
				var inc = new GlideRecord("incident");
				//Delete incident
				if(inc.short_description != x.description){
					gs.info("【CSV Check】delete:" + inc.number);
					inc.deleteRecord();
				}
				//Create incident
				inc.addQuery("short_description", x.short_description);
				inc.query();
				if(inc.getRowCount() == 0){
					inc.initialize();
					inc.setValue("short_description", x.short_description);
					inc.setValue("description", x.description);
					inc.insert();
				}
                }
		
    }


})(current, previous);

エラーログ

om.glide.csv.parser.CSVParserException: CSVParser: Parsed value count is not equal to header count for CSV record: |Q��c�: org.mozilla.javascript.JavaScriptException: com.glide.csv.parser.CSVParserException: CSVParser: Parsed value count is not equal to header count for CSV record: |Q��c�: org.mozilla.javascript.Context.makeJavaScriptException(Context.java:1952)
org.mozilla.javascript.Context.throwAsScriptRuntimeEx(Context.java:1938)
org.mozilla.javascript.MemberBox.invoke(MemberBox.java:143)
org.mozilla.javascript.FunctionObject.doInvoke(FunctionObject.java:670)
org.mozilla.javascript.FunctionObject.call(FunctionObject.java:614)
org.mozilla.javascript.ScriptRuntime.doCall(ScriptRuntime.java:2609)
org.mozilla.javascript.Interpreter.interpretLoop(Interpreter.java:1518)
org.mozilla.javascript.Interpreter.interpret(Interpreter.java:830)
org.mozilla.javascript.InterpretedFunction.lambda$call$0(InterpretedFunction.java:152)
com.glide.caller.gen.sys_script_b10a7e142f6ed1109c8bd8ddf699b692_script.call(Unknown Source)
com.glide.script.ScriptCaller.call(ScriptCaller.java:18)
org.mozilla.javascript.InterpretedFunction.call(InterpretedFunction.java:151)
org.mozilla.javascript.ScriptRuntime.doCall2(ScriptRuntime.java:2678)
org.mozilla.javascript.ScriptRuntime.doCall(ScriptRuntime.java:2617)
org.mozilla.javascript.Interpreter.interpretLoop(Interpreter.java:1518)
org.mozilla.javascript.Interpreter.interpret(Interpreter.java:830)
org.mozilla.javascript.InterpretedFunction.lambda$call$0(InterpretedFunction.java:152)
com.glide.caller.gen.sys_script_b10a7e142f6ed1109c8bd8ddf699b692_script.call(Unknown Source)
com.glide.script.ScriptCaller.call(ScriptCaller.java:18)
org.mozilla.javascript.InterpretedFunction.call(InterpretedFunction.java:151)
org.mozilla.javascript.ContextFactory.doTopCall(ContextFactory.java:563)
org.mozilla.javascript.ScriptRuntime.doTopCall(ScriptRuntime.java:3459)
org.mozilla.javascript.InterpretedFunction.exec(InterpretedFunction.java:164)
com.glide.script.ScriptEvaluator.execute(ScriptEvaluator.java:354)
com.glide.script.ScriptEvaluator.evaluateString(ScriptEvaluator.java:177)
com.glide.script.ScriptEvaluator.evaluateString(ScriptEvaluator.java:111)
com.glide.script.fencing.GlideScopedEvaluator.evaluateScript(GlideScopedEvaluator.java:322)
com.glide.script.fencing.GlideScopedEvaluator.evaluateScript(GlideScopedEvaluator.java:226)
com.glide.script.fencing.GlideScopedEvaluator.evaluateScript(GlideScopedEvaluator.java:213)
com.glide.script.BusinessRule.runScript(BusinessRule.java:443)
com.glide.script.BusinessRule.runSyncBusinessRule(BusinessRule.java:325)
com.glide.script.BusinessRule.runBusinessRule(BusinessRule.java:218)
com.glide.script.BusinessRule.run(BusinessRule.java:194)
com.glide.script.GlideRecordBusinessRules.run(GlideRecordBusinessRules.java:255)
com.glide.script.GlideRecord.runScripts(GlideRecord.java:1422)
com.glide.script.GlideRecord.runScriptsAndEngines(GlideRecord.java:1356)
com.glide.script.GlideRecord.insert(GlideRecord.java:5032)
com.glide.script.GlideRecord.insert(GlideRecord.java:4962)
com.glide.ui.SysAttachmentOutputStream.close(SysAttachmentOutputStream.java:268)
com.glide.ui.SysAttachment.streamData(SysAttachment.java:1920)
com.glide.ui.SysAttachment.streamData(SysAttachment.java:1834)
com.glide.ui.SysAttachment.write(SysAttachment.java:1691)
com.glide.ui.SysAttachment.write(SysAttachment.java:1658)
com.glide.ui.SysAttachment.write(SysAttachment.java:1654)
com.glide.ui.SysAttachment.write(SysAttachment.java:1649)
com.glide.ui.SysAttachment.write(SysAttachment.java:1423)
com.glide.ui.SysAttachment.writeParts(SysAttachment.java:457)
com.glide.ui.SysAttachment.processRequest(SysAttachment.java:387)
com.glide.processors.AttachmentProcessor.process(AttachmentProcessor.java:93)
com.glide.processors.AProcessor.runProcessor(AProcessor.java:612)
com.glide.processors.AProcessor.processTransaction(AProcessor.java:274)
com.glide.processors.ProcessorRegistry.process0(ProcessorRegistry.java:184)
com.glide.processors.ProcessorRegistry.process(ProcessorRegistry.java:172)
com.glide.ui.GlideServletTransaction.process(GlideServletTransaction.java:46)
com.glide.sys.Transaction.run(Transaction.java:2468)
com.glide.ui.HTTPTransaction.run(HTTPTransaction.java:27)
java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
java.base/java.lang.Thread.run(Thread.java:829)

CSVデータ(サンプル)

daizawa_0-1665722343300.png

 

0 Helpfuls
  • 1,714 Views
2 ACCEPTED SOLUTIONS

Go to solution
d-aizawa
Kilo Sage
In response to iwai

‎10-16-2022 05:22 PM

iwaiさん
ご教示ありがとうございます。

再度スクリプトを修正(更新する場合のケースを追加)して、実行しましたが、

現在、コメントアウトしている削除のスクリプトを入れると

既存のレコードすべてが削除されてしまう現象が発生しました。

(削除をコメントアウトすると、追加と更新はうまくいくことを確認済です)

CSVに存在しないインシデントレコード(short descriptionで判別)を削除するには

どうすれば良いでしょうか。

(function executeRule(current, previous /*null when async*/ ) {
    var delimiter = ',';
    var quoteCharacter = '"';
    var columnNames = ["short_description", "description"];

    var attachment = new GlideSysAttachment();
    var gr_attachment = new GlideRecord("sys_attachment");
    gr_attachment.orderByDesc('sys_created_on');
    gr_attachment.setLimit(1);
    gr_attachment.query();
    gs.info("【CSV Check】csv：" + gr_attachment.hasNext());

    while (gr_attachment.next()) {
        var file_name = gr_attachment.file_name;
        gs.info('file_name:' + gr_attachment.file_name);
        var attachment_sys_id = gr_attachment.sys_id;
        var attachmentStream = attachment.getContentStream(attachment_sys_id);
        var reader = new GlideTextReader(attachmentStream);
        var csvLine = ' ';
        var isHeader = true;
        while ((csvLine = reader.readLine()) != null) {
            if (isHeader) { // first line is header so skip
                isHeader = false;
                continue;
            }
            gs.info("【CSV Check】csvLine", +csvLine);
            var x = new sn_impex.CSVParser().parseLineToObject(csvLine, columnNames, delimiter, quoteCharacter);
            gs.info("【CSV Check】x:" + JSON.stringify(x));
            gs.info("【CSV Check】short_description:" + x.short_description);
            //Delete incident
            //var incDelete = new GlideRecord("incident");
            //incDelete.addQuery("short_description", "!=", x.short_description);
            //incDelete.query();
            // while (incDelete.next()) {
            //incDelete.deleteRecord();
            //gs.info("【CSV Check】削除されたインシデント" + incDelete.number);
            //}
            //Create incident
            var incCreate = new GlideRecord("incident");
            incCreate.addQuery("short_description", x.short_description);
            incCreate.query();
            if (incCreate.getRowCount() == 0) {
                incCreate.initialize();
                incCreate.setValue("short_description", x.short_description);
                incCreate.setValue("description", x.description);
                incCreate.insert();
            } else if (incCreate.getRowCount() == 1) {
                while (incCreate.next()) {
                    incCreate.setValue("short_description", x.short_description);
                    incCreate.setValue("description", x.description);
                    incCreate.update();
                }

            }

        }

    }


})(current, previous);

View solution in original post

0 Helpfuls

Go to solution
iwai
Giga Sage
In response to d-aizawa

‎10-16-2022 07:11 PM

そのScriptには、CSV読み込みがないので1行しか作られないように思います。Scriptの全量としては以下のようになるのではないかと考えています。

(function executeRule(current, previous /*null when async*/ ) {
  var sysidList = [];
  var delimiter = ',';
  var quoteCharacter = '"';
  var columnNames = ["short_description", "description"];

  var attachment = new GlideSysAttachment();
  var gr_attachment = new GlideRecord("sys_attachment");
  gr_attachment.orderByDesc('sys_created_on');
  gr_attachment.setLimit(1);
  gr_attachment.query();
  gs.info("【CSV Check】csv：" + gr_attachment.hasNext());

  while (gr_attachment.next()) {
    var file_name = gr_attachment.file_name;
    gs.info('file_name:' + gr_attachment.file_name);
    var attachment_sys_id = gr_attachment.sys_id;
    var attachmentStream = attachment.getContentStream(attachment_sys_id);
    var reader = new GlideTextReader(attachmentStream);
    var csvLine = ' ';
    var isHeader = true;
    while ((csvLine = reader.readLine()) != null) {
      if (isHeader) { // first line is header so skip
        isHeader = false;
        continue;
      }
      gs.info("【CSV Check】csvLine", +csvLine);
      var x = new sn_impex.CSVParser().parseLineToObject(csvLine, columnNames, delimiter, quoteCharacter);
      gs.info("【CSV Check】x:" + JSON.stringify(x));
      gs.info("【CSV Check】short_description:" + x.short_description);

      //Create incident
      var incCreate = new GlideRecord("incident");
      incCreate.addQuery("short_description", x.short_description);
      incCreate.query();
      if (incCreate.getRowCount() == 0) {
        incCreate.initialize();
        incCreate.setValue("short_description", x.short_description);
        incCreate.setValue("description", x.description);
        sysidList.push(incCreate.insert() + '');
      } else if (incCreate.getRowCount() == 1) {
        while (incCreate.next()) {
          incCreate.setValue("short_description", x.short_description);
          incCreate.setValue("description", x.description);
          sysidList.push(incCreate.update() + '');
        }
      }
    }
  }

  if (sysidList) {
    var incGr = new GlideRecord("incident");
    incGr.addQuery('sys_id', 'NOT IN', sysidList.join(','))
    incGr.deleteMultiple();
  }

})(current, previous);

View solution in original post

0 Helpfuls
8 REPLIES 8

Go to solution
iwai
Giga Sage
In response to d-aizawa

‎10-16-2022 07:11 PM

そのScriptには、CSV読み込みがないので1行しか作られないように思います。Scriptの全量としては以下のようになるのではないかと考えています。

(function executeRule(current, previous /*null when async*/ ) {
  var sysidList = [];
  var delimiter = ',';
  var quoteCharacter = '"';
  var columnNames = ["short_description", "description"];

  var attachment = new GlideSysAttachment();
  var gr_attachment = new GlideRecord("sys_attachment");
  gr_attachment.orderByDesc('sys_created_on');
  gr_attachment.setLimit(1);
  gr_attachment.query();
  gs.info("【CSV Check】csv：" + gr_attachment.hasNext());

  while (gr_attachment.next()) {
    var file_name = gr_attachment.file_name;
    gs.info('file_name:' + gr_attachment.file_name);
    var attachment_sys_id = gr_attachment.sys_id;
    var attachmentStream = attachment.getContentStream(attachment_sys_id);
    var reader = new GlideTextReader(attachmentStream);
    var csvLine = ' ';
    var isHeader = true;
    while ((csvLine = reader.readLine()) != null) {
      if (isHeader) { // first line is header so skip
        isHeader = false;
        continue;
      }
      gs.info("【CSV Check】csvLine", +csvLine);
      var x = new sn_impex.CSVParser().parseLineToObject(csvLine, columnNames, delimiter, quoteCharacter);
      gs.info("【CSV Check】x:" + JSON.stringify(x));
      gs.info("【CSV Check】short_description:" + x.short_description);

      //Create incident
      var incCreate = new GlideRecord("incident");
      incCreate.addQuery("short_description", x.short_description);
      incCreate.query();
      if (incCreate.getRowCount() == 0) {
        incCreate.initialize();
        incCreate.setValue("short_description", x.short_description);
        incCreate.setValue("description", x.description);
        sysidList.push(incCreate.insert() + '');
      } else if (incCreate.getRowCount() == 1) {
        while (incCreate.next()) {
          incCreate.setValue("short_description", x.short_description);
          incCreate.setValue("description", x.description);
          sysidList.push(incCreate.update() + '');
        }
      }
    }
  }

  if (sysidList) {
    var incGr = new GlideRecord("incident");
    incGr.addQuery('sys_id', 'NOT IN', sysidList.join(','))
    incGr.deleteMultiple();
  }

})(current, previous);
0 Helpfuls

Go to solution
d-aizawa
Kilo Sage
In response to iwai

‎10-16-2022 07:41 PM

ご教示ありがとうございます。

上記スクリプトに参考させて頂き、無事解決しました。

1点ご確認ですが、更新したレコードが数万件もあるようならば、配列のリストではないほうが良いとおっしゃっておりますが、具体的にどういった理由になるのでしょうか。

0 Helpfuls

Go to solution
iwai
Giga Sage
In response to d-aizawa

‎10-17-2022 12:06 AM - edited ‎10-17-2022 12:08 AM

  • 『1点ご確認ですが、更新したレコードが数万件もあるようならば、配列のリストではないほうが良いとおっしゃっておりますが、具体的にどういった理由になるのでしょうか。』

この『addQuery('sys_id', 'NOT IN', sysidList.join(','))』 文字列が 膨大な長さになってしまうので、ある程度の長さ以上は無視されるか、Queryとしてエラーになると思います。

数万行になる想定があるなら配列のリストを使わない方法で作る必要がありそうです。その具体的な方法は、更新日付などを使ったQueryを工夫するか、TAGを使う方法です。

0 Helpfuls

Go to solution
d-aizawa
Kilo Sage

‎10-16-2022 05:03 PM

iwaiさん
ご教示ありがとうございます。

再度スクリプトを修正(更新する場合のケースを追加)して、実行しましたが、

現在、コメントアウトしている削除のスクリプトを入れると

既存のレコードすべてが削除されてしまう現象が発生しました。

(削除をコメントアウトすると、追加と更新はうまくいくことを確認済です)

CSVに存在しないインシデントレコード(short descriptionで判別)を削除するには

どうすれば良いでしょうか。

(function executeRule(current, previous /*null when async*/ ) {
    var delimiter = ',';
    var quoteCharacter = '"';
    var columnNames = ["short_description", "description"];

    var attachment = new GlideSysAttachment();
    var gr_attachment = new GlideRecord("sys_attachment");
    gr_attachment.orderByDesc('sys_created_on');
    gr_attachment.setLimit(1);
    gr_attachment.query();
    gs.info("【CSV Check】csv：" + gr_attachment.hasNext());

    while (gr_attachment.next()) {
        var file_name = gr_attachment.file_name;
        gs.info('file_name:' + gr_attachment.file_name);
        var attachment_sys_id = gr_attachment.sys_id;
        var attachmentStream = attachment.getContentStream(attachment_sys_id);
        var reader = new GlideTextReader(attachmentStream);
        var csvLine = ' ';
        var isHeader = true;
        while ((csvLine = reader.readLine()) != null) {
            if (isHeader) { // first line is header so skip
                isHeader = false;
                continue;
            }
            gs.info("【CSV Check】csvLine", +csvLine);
            var x = new sn_impex.CSVParser().parseLineToObject(csvLine, columnNames, delimiter, quoteCharacter);
            gs.info("【CSV Check】x:" + JSON.stringify(x));
            gs.info("【CSV Check】short_description:" + x.short_description);
            //Delete incident
            //var incDelete = new GlideRecord("incident");
            //incDelete.addQuery("short_description", "!=", x.short_description);
            //incDelete.query();
           // while (incDelete.next()) {
				//incDelete.deleteRecord();
                //gs.info("【CSV Check】削除されたインシデント" + incDelete.number);
            //}
			//Create incident
            var incCreate = new GlideRecord("incident");
            incCreate.addQuery("short_description", x.short_description);
            incCreate.query();
            if (incCreate.getRowCount() == 0) {
                incCreate.initialize();
                incCreate.setValue("short_description", x.short_description);
                incCreate.setValue("description", x.description);
                incCreate.insert();
            } else if (incCreate.getRowCount() == 1) {
                while (incCreate.next()) {
                    incCreate.setValue("short_description", x.short_description);
                    incCreate.setValue("description", x.description);
                    incCreate.update();
                }
            }
        }
    }
})(current, previous);
0 Helpfuls