Shedule Archive/export to excel

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,

  • 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.

  • 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,
  • 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

  • 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

  • Verified Answer

    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);
    }

     

  • 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 ,
  • 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

  • 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,
  • 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.