question Re: Export Report in CSV format using a script in Platform Analytics forum

Export Report in CSV format using a script

knowledgeenthus — Thu, 11 Jan 2018 18:52:29 GMT

Hi,

I have figured out a script to export report using csv format which is great but i cant export more than 50,000 records

I have tried the logic of looping through and exporting 50,000 records at a time and combine them at the end but specifying a number in sysparm_first_row gets ignored and it always results the first 50,000. Is there any other way around this? I am using powershell if anyone has expertise in that area

Re: Export Report in CSV format using a script

SanjivMeher — Thu, 11 Jan 2018 19:20:03 GMT

You can set the export limit to more than 50k.

Navigate to System Properties->Import Export and set export row limit.

Reference

Export limits

Re: Export Report in CSV format using a script

knowledgeenthus — Thu, 11 Jan 2018 19:22:12 GMT

Unfortunately that is not an options for us for performance and/or other reasons. Is there a variable that can be used to loop through set of records in the report?

Re: Export Report in CSV format using a script

SanjivMeher — Thu, 11 Jan 2018 19:33:56 GMT

I know that if you do it using javascript gliderecord, you can query in format gr.chooseWindow(initRow, lastRow);

Re: Export Report in CSV format using a script

knowledgeenthus — Thu, 11 Jan 2018 19:46:12 GMT

This is the code for what i am using, if that helps. What you are describing, is that available only in javascript?

$postparams= @{sysparm_first_row='125000'}

Invoke-webrequest -Credential $credobject -outfile "Path\report.csv" -uri https://instance.com/sys_report_template.do?CSV&jar_report_id=2382383094932840284 -Method POST -Body $postParams

Re: Export Report in CSV format using a script

SanjivMeher — Thu, 11 Jan 2018 19:51:39 GMT

Since you are directly calling the report, it will give you result based on export limit, even if you pass the first row parameter.

If you are directly querying the table, you may be able to split them. But not sure how you will export it then.

Re: Export Report in CSV format using a script

knowledgeenthus — Thu, 11 Jan 2018 19:55:36 GMT

Yea, the data is grabbed from different tables which is why i am using report so i can get data in one report. So that means there is no way to accomplish this is what i am guessing?

Re: Export Report in CSV format using a script

SanjivMeher — Thu, 11 Jan 2018 20:02:48 GMT

I think there is not. But wait for response from others. There could be.

One option could be to query the table and just print everything to an email. But then it becomes difficult for them to go through the data in an email. There could be a limit on the email body as well.

Or query everything from the table directly and try exporting it to csv using code. Not sure how feasible it is.

How to export JavaScript array info to csv (on client side)? - Stack Overflow

Re: Export Report in CSV format using a script

Josh Cooper — Thu, 11 Jan 2018 21:23:39 GMT

Our typical go to for this is to capture and reuse the sys_ids as a filter (where possible). It's an extra step because it may ruin whatever sorting you've already got, but if you're ordering by date it should be pretty much the same.

- add OrderBy (or OrderByDesc) sys_id to the query

- Within the PowerShell (or Bash, for any future readers who may need it) capture the sys_id of the last node that you receive

- resubmit your next request adding a "sys_id > *whatever the last sys_id was*" ( sys_id < for OrderByDesc)

It's not perfect, but hopefully it'll work for you!

Re: Export Report in CSV format using a script

knowledgeenthus — Thu, 11 Jan 2018 21:45:23 GMT

That sounds helpful, can you give me an example of the URI for this scenario and that will work with report_template_do also right?

What would the URI look like when we are specifying > sys_id

Re: Export Report in CSV format using a script

knowledgeenthus — Thu, 11 Jan 2018 23:45:56 GMT

so i tried this

Invoke-webRequest -OutFile "Path\report.csv" -Uri "https://instance.com/sys_report_template.do?CSV&jvar_report_id=b2343242h24324hbg24e2&sysparm_orderby=u_correlation_id"

THis gave me 50,000 records arranged by correlation id. Then i tried this

Invoke-webRequest -OutFile "path/Report.csv" -Uri "https://instance.com/sys_report_template.do?CSV&jvar_report_id=basfoffhfffdss&sysparm_query=u_correlation_id%3Efsdfjsdfkjsdfjsdlfkj&sysparm_orderby=u_correlation_id"

But this gave me the same 50,000 rown, i used the %3E to specify greater than to get the next 50,000 rows. Any idea what i am doing wrong?

you can ignore the report id number as i just made up for the sake of the example