Highlighted
Absent Member.
Absent Member.
1014 views

Shedule Archive/export to excel

Jump to solution

Dear All,

 

I want to schedule the archival of tickets from SM to csv/xls format so that it is in readable format.

It should be same as we use export to excel utility while viewing list of Tickets/records.

 

Using Purge/Archive SM is creating .unl file but I want to be in .xls format with desired/all fields same as export to excel utility.

 

How it could be possible?

 

I am using SM7.03

Oracle 10g

 

 

Thanks,

Tushar Jain,

____________________________________
Assign Kudo, if found post useful and mark it accepted if solves the issue.
0 Likes
1 Solution

Accepted Solutions
Highlighted
Absent Member.
Absent Member.

Here is the same solution using JavaScript in a ScriptLibrary record...

Advantage: It can be called directly from a schedule record

Disadvantage: Issues with the disconnect panel when using JavaScript

 

Note: I had to disable Client side load/unload in the Windows client for the JavaScript to complete without error (see Disadvantage above)

 

ScriptLibrary: ExportTickets

 

var tickets = new SCFile("probsummary");
var query = "number#\"IM1000\" and problem.status=\"Work In Progress\"";
var rc = tickets.doSelect(query);

if (rc == RC_SUCCESS)
{
	var layout = new SCFile("format");
	var rr = layout.doSelect("name=\"probsummary.qbe.g\"");
	var path = "c:\\example\\tickets.csv";
	var delim = "csv"; // $L.delim = ","
	var header = true;
	
	var rteCode = new SCDatum();
	var rteNames = new SCDatum();
	var rteValues = new SCDatum();
	
	rteNames.push("record");
	rteNames.push("name");
	rteNames.push("query");
	rteNames.push("string1");
	rteNames.push("boolean1");
	
	rteValues.push(tickets);
	rteValues.push(layout);
	rteValues.push(path);
	rteValues.push(delim);
	rteValues.push(header);
	
	system.functions.rtecall("callrad", rteCode, "us.dump.delimited", rteNames, rteValues, false);
}

 

View solution in original post

9 Replies
Highlighted
Absent Member.. Absent Member..
Absent Member..

The old export tool in SM might be able to do it, but I think it only can generate fixed width output, not delimited files like csv. 

 

The base Connect-It license includes everything you would need to create an archive that is readable. I'd recommend using Connect-It. It will not purge anything from SM, though, so if you wanted to also purge the records you'd have to separately manage the purge from the Connect-It extract.

----------------------------------------------------
Kudos - what, where, how, and why
Want Good Answers? Ask Good Questions...
0 Likes
Highlighted
Absent Member.
Absent Member.
Hi John,

Since it is fixed width that is why I do not want to use it.
I am not using Connect-It but SCAuto.
So is there any other solution for the same.


hth,
____________________________________
Assign Kudo, if found post useful and mark it accepted if solves the issue.
0 Likes
Highlighted
Absent Member.
Absent Member.
Any one have any clue over same.
____________________________________
Assign Kudo, if found post useful and mark it accepted if solves the issue.
0 Likes
Highlighted
Absent Member.
Absent Member.

The Mass Unload option (RAD: database.unload) provides three export modes: Binary, Text, and Formatted Text. Unfortunately, the two text options do not generate output in CSV format.

 

The work-flow for a scheduled Purge/Archive option (RAD: pa.main.appl.bg) eventually calls the file.unload RAD Application. Unfortunately, file.unload does not generate output in CSV format.

 

The Export to Text File option (RAD: us.dump.delimited) does provide the option to export text in CSV format. This work-flow does not purge records, but it can be used to archive records.

 

The following script (not JavaScript or ScriptLibrary) can be called from a schedule record using the script.execute.bg RAD Application (separate download provided by Customer Support).

 

Script name (dbdict = scripts): Export Tickets

Skip Display: true

 

Pre RAD Statements tab:

$L.void=rtecall("rinit", $L.rinit, $L.tickets, "probsummary")

$L.query="number#\"IM1000\" and problem.status=\"Work In Progress\""

$L.void=rtecall("select", $L.select, $L.tickets, $L.query)

$L.void=rtecall("rinit", $L.code, $L.layout, "format")

$L.void=rtecall("select", $L.code, $L.layout, "name=\"probsummary.qbe.g\"")

$L.path="c:\\example\\tickets.csv"

$L.delim="csv"

$L.header=true

 

Note: Make sure any directories in the filename path already exist

Note: There is a difference in the work-flow between $L.delim="csv" and $L.delim=","

 

RAD tab:

Application: us.dump.delimited

 

Parameter Names (1): record

Parameter Names (2): name

Parameter Names (3): query

Parameter Names (4): string1

Parameter Names (5): boolean1

 

Parameter Values (1): $L.tickets

Parameter Values (2): $L.layout

Parameter Values (3): $L.path

Parameter Values (4): $L.delim

Parameter Values (5): $L.header

 

Note: Use the script.execute RAD Application to test the script

Note: A set of scripts should be used in a PROD environment to validate the probsummary query returned at least one record

Highlighted
Absent Member.
Absent Member.

Here is the same solution using JavaScript in a ScriptLibrary record...

Advantage: It can be called directly from a schedule record

Disadvantage: Issues with the disconnect panel when using JavaScript

 

Note: I had to disable Client side load/unload in the Windows client for the JavaScript to complete without error (see Disadvantage above)

 

ScriptLibrary: ExportTickets

 

var tickets = new SCFile("probsummary");
var query = "number#\"IM1000\" and problem.status=\"Work In Progress\"";
var rc = tickets.doSelect(query);

if (rc == RC_SUCCESS)
{
	var layout = new SCFile("format");
	var rr = layout.doSelect("name=\"probsummary.qbe.g\"");
	var path = "c:\\example\\tickets.csv";
	var delim = "csv"; // $L.delim = ","
	var header = true;
	
	var rteCode = new SCDatum();
	var rteNames = new SCDatum();
	var rteValues = new SCDatum();
	
	rteNames.push("record");
	rteNames.push("name");
	rteNames.push("query");
	rteNames.push("string1");
	rteNames.push("boolean1");
	
	rteValues.push(tickets);
	rteValues.push(layout);
	rteValues.push(path);
	rteValues.push(delim);
	rteValues.push(header);
	
	system.functions.rtecall("callrad", rteCode, "us.dump.delimited", rteNames, rteValues, false);
}

 

View solution in original post

Highlighted
Absent Member.
Absent Member.
Hi,

Thanks -m- ,it worked.
But I am having issue if any simple text column have the next line character in it,in this case csv is taking as next column data.

So here what does "csv" means for delim variable ?


Thanks ,
____________________________________
Assign Kudo, if found post useful and mark it accepted if solves the issue.
0 Likes
Highlighted
Absent Member.
Absent Member.

I'm not immediately aware of a solution which addresses fields that have a next line character. I think this will be an issue with any OOB text export option used.

 

The difference between $L.delim="csv" and $L.delim="," is defined in the us.dump.delimited RAD Application.

 

RAD: us.dump.delimited

Panel: check.csv

 

If $L.delim = "csv" then it executes the expressions on the build.csv panel. Otherwise, it executes the expressions on the build panel. The expressions are responsible for generating the text that is exported for each record.

 

Comma output:

Incident ID,Open Time,Title
IM10002,09/02/07 01:51:00,Webmail login failure
IM10003,09/02/07 21:49:00,System crashes with message "not enough memory" daily
IM10005,09/06/07 15:57:00,Microsoft Office keeps asking to install Language packs
IM10008,09/06/07 18:14:00,Desktop DVD,drive makes strange noices
IM10009,09/06/07 18:37:00,Desktop screen out of order

 

CSV output (note the extra double-quotes for IM10003 and IM10005😞
Incident ID,Open Time,Title
IM10002,09/02/07 01:51:00,Webmail login failure
IM10003,09/02/07 21:49:00,"System crashes with message ""not enough memory"" daily"
IM10005,09/06/07 15:57:00,Microsoft Office keeps asking to install Language packs
IM10008,09/06/07 18:14:00,"Desktop DVD,drive makes strange noices"
IM10009,09/06/07 18:37:00,Desktop screen out of order

Highlighted
Absent Member.
Absent Member.
Hi -m-

This issue is also in OOB text export option but in that content after next line are ignored but in background load whole data is exporting and next line is treated as next record(which is obvious).
I tried different deliminator but no luck as supposed.
Although it is not expected to have next line character in simple text field.

But still do you have any about how to deal with it.


Thanks,
____________________________________
Assign Kudo, if found post useful and mark it accepted if solves the issue.
0 Likes
Highlighted
Regular Contributor.
Regular Contributor.

Hi All,

 

I am also trying to build the similar functionality by using the same RAD call but getting the below error.

unable to open connection (file.open.withoptions,connect)

I have disabled the load/unload option in the windows client but still its not working. Can any one guide me how to resolve this issue.

 

 

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.