Script to compare 2tables and get the results

[prakah]

I have written script to compare 2 tables(staging & target) and get the results matching different conditions for 2 different tables. i'm getting the output if i use setLimit(5000), But i'm not getting results if i run without setLimit functionality as the staging table contains more than 1 million records. Please help with any other functionality to process this 1M records

Attaching the script.

var fileName = 'Customerinfo.csv';
var csvData = ''; //The variable csvData will contain a string which is used to build the CSV file contents
for (var i = 0; i < Headers.length; i++) { //Build the Headers
csvData = csvData + '"' + Headers[i] + '"' + ',';
}
csvData = csvData+"\r\n";

var ad = "accountISEMPTY";
var ad1 = "u_cust_nbrISEMPTY";
var adhoc = new GlideRecord('u_release');
adhoc.addEncodedQuery(ad1);
adhoc.setLimit(1000);
adhoc.query();
while(adhoc.next()){
var accGr = new GlideRecord('account');
accGr.addQuery('u_number', adhoc.u_nbr);
accGr.addEncodedQuery(ad);
accGr.query();
while(accGr.next()) {
csvData = csvData + '"' + accGr.u_number + '",' + '"' + accGr.parent.getDisplayValue() + '",' + '"' + accGr.u_type+'",' + '"' + accGr.u_number.getDisplayValue() + '",' + '"' + accGr.u_number.getDisplayValue() + '"';
csvData = csvData+"\r\n";
}
}

//attach the file to a record.
var grRec = new GlideRecord("account");
grRec.addQuery("sys_id","***********");
grRec.query();
if(grRec.next()){
var grAttachment = new GlideSysAttachment();
grAttachment.write(grRec, fileName, 'application/csv',csvData);
}

[Aniket Chavan]

Hello @prakah ,

Can you please try the below code onces and let me know your views on this.

var batchSize = 1000; // Set your desired batch size
var fileName = 'Customerinfo.csv';
var csvData = ''; // CSV data string

for (var i = 0; i < Headers.length; i++) {
    csvData = csvData + '"' + Headers[i] + '"' + ',';
}
csvData = csvData + '\r\n';

var ad = "accountISEMPTY";
var ad1 = "u_cust_nbrISEMPTY";

var adhoc = new GlideRecord('u_release');
adhoc.addEncodedQuery(ad1);

var count = 0; // Counter for limiting batches
adhoc.query();
while (adhoc.next()) {
    var accGr = new GlideRecord('account');
    accGr.addQuery('u_number', adhoc.u_nbr);
    accGr.addEncodedQuery(ad);
    accGr.query();

    while (accGr.next()) {
        csvData = csvData + '"' + accGr.u_number + '",' + '"' + accGr.parent.getDisplayValue() + '",' + '"' + accGr.u_type + '",' + '"' + accGr.u_number.getDisplayValue() + '",' + '"' + accGr.u_number.getDisplayValue() + '"';
        csvData = csvData + '\r\n';
    }

    count++;

    if (count >= batchSize) {
        // Process the batch
        processBatch(csvData);

        // Reset variables for the next batch
        csvData = '';
        count = 0;
    }
}

// Process the remaining records (if any)
if (csvData.length > 0) {
    processBatch(csvData);
}

function processBatch(data) {
    // Attach the file to a record or perform other actions
    var grRec = new GlideRecord("account");
    grRec.addQuery("sys_id", "***********");
    grRec.query();
    if (grRec.next()) {
        var grAttachment = new GlideSysAttachment();
        grAttachment.write(grRec, fileName, 'application/csv', data);
    }
}

Let me know your views on this and Mark ✅Correct if this solves your query and also mark 👍Helpful if you find my response worthy based on the impact.

Regards,

Aniket

[Siddhesh Gawade]

Hello @prakah , 

It may be beacause of property com.glide.attachment.max_size.  This property restricts the overall size of the the attachment and applicable on all formats including XML,json etc. By default it's 1024 i.e.1 GB. So please check what is file size of your attachement.  Or try with incresing the property value to 2048, 3072 etc.

Kindly mark my answer as Correct and helpful based on the Impact.

Regards,

Siddhesh