- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
01-23-2023 08:35 PM
while importing the data from the database view table through RITM, we are only able to export only 10k records, We didn't applied any limitage in the script, But I need full data which is in the table eg:-u_sam_user_sysid.
This the script I'm using
(function execute(inputs, outputs) {
// ... code ...
var StringLimit=16000000;
var Headers = ["Name","User ID","Email","Sys ID","Active","Cost center","Country code","Department","Domain","Domain Path","Country","HR Other Company","Resource Type","Business Unit","City"];
var fileName = 'User_Data'; // index &'.csv' is added;
var csvData = ''; //The variable csvData will contain a string which is used to build the CSV file contents
var csvHeader = '';
var csvRow = '';
var fileid=0;
for (var i = 0; i < Headers.length; i++) { //Build the Headers
csvHeader = csvHeader + '"' + Headers[i] + '"' + ',';
}
csvHeader = csvHeader+"\r\n";
csvData=csvHeader;
//attach the file to a record.
var grRec = new GlideRecord("sc_req_item");
grRec.addQuery("cat_item","b06f07ed1bdb91909c791f88b04bcb7a");
grRec.orderByDesc('sys_created_on');
grRec.query();
if(grRec.next()){
gs.print('Enter');
gs.info('RITM:'+grRec.cat_item);
var gr = new GlideRecord("u_sam_user_sysid"); // User
//gr.addEncodedQuery('active=true^u_ad_domainSTARTSWITHsb');
//gr.addEncodedQuery('active=true');
//gr.setLimit(30);
gr.query();
while(gr.next()) {
csvRow = '"' + gr.usr_name + '",' + '"' + gr.usr_user_name + '",' + '"' + gr.usr_email +'",' + '"' + gr.usr_sys_id + '",' + '"' + gr.usr_active + '",' + '"' + gr.usr_cost_center + '",' + '"' + gr.getDisplayValue('usr_u_country') + '",' + '"' + gr.getDisplayValue('usr_department') + '",' + '"' + gr.usr_sys_domain + '",' + '"' + gr.usr_sys_domain_path + '",' + '"' + gr.usr_country + '",' + '"' + gr.usr_u_amsextensionattribute23 + '",' + '"' + gr.usr_u_resource_type + '",' + '"' + gr.getDisplayValue('usr_u_business_unit') + '",' + '"' + gr.usr_city + '",' + "\r\n";
if (csvData.length+csvRow.length>StringLimit) {
//save to attachment
var grAttachment = new GlideSysAttachment();
grAttachment.write(grRec, fileName+fileid+'.csv', 'application/csv',csvData);
//increase filedid
fileid++;
csvData=csvHeader+csvRow;
} else {
csvData = csvData+csvRow;
}
}
if (csvData!="") {
//save attachment
var grAttachment1 = new GlideSysAttachment();
grAttachment1.write(grRec, fileName+fileid+'.csv', 'application/csv',csvData);
}
}
})(inputs, outputs);
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
01-23-2023 09:15 PM
Hi @mastan babu ko1 ,
Exporting to Excel is actually limited by 2 system parameters: glide.excel.export.limit and glide.excel.max_cells. So, if you increase the default 10000 to 80000 of row limit, you might still not get all data as by default the cell limit is 500000. You might need to increase that value as well. However, keep in mind that Excel itself has a limit of how much data can it display when you open the file.
1. Go to System Properties => Import Export
2. Change the Export Row Limit value to 80000
3. Change the Export Cell Limit (as needed)
FYI, when dealing with such large files, I will recommend using CSV instead of Excel. Excel is good for small result sets but limited when it comes to moving large amount of data between systems.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
01-23-2023 09:48 PM - edited 01-23-2023 09:49 PM
Change the property value glide.db.max_view_records and try to export. If the property doesn't exist, create it.
Regards, Shekhar
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
01-23-2023 09:15 PM
Hi @mastan babu ko1 ,
Exporting to Excel is actually limited by 2 system parameters: glide.excel.export.limit and glide.excel.max_cells. So, if you increase the default 10000 to 80000 of row limit, you might still not get all data as by default the cell limit is 500000. You might need to increase that value as well. However, keep in mind that Excel itself has a limit of how much data can it display when you open the file.
1. Go to System Properties => Import Export
2. Change the Export Row Limit value to 80000
3. Change the Export Cell Limit (as needed)
FYI, when dealing with such large files, I will recommend using CSV instead of Excel. Excel is good for small result sets but limited when it comes to moving large amount of data between systems.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
01-23-2023 09:48 PM - edited 01-23-2023 09:49 PM
Change the property value glide.db.max_view_records and try to export. If the property doesn't exist, create it.
Regards, Shekhar