Query result set output in EXCEL file

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

Query result set output in EXCEL file

alzeena
I have a requirement to develop an AWK script for running a DQL and writing the result set in an ABC.xls.

I need to place the output excel under the folder path /Systems/Reports in Documentum Administration.

My code is :

function MyScript(session)
{
myQuery = "select r_object_id,count(*) from dm_document where r_creator_name='XYZ' GROUP BY r_object_id";
status = dmAPIExec("execquery,"session",F,"myQuery );
print "status: " status;

if (status != 1)
{  
err_msg = dmAPIGet("getmessage,session")
print err_msg;
exit;
}
else
{
count=0;
collection = dmAPIGet("getlastcoll,"session);
while(dmAPIExec("next,"session","collection)>0)
{  
objID = dmAPIGet("get,"session","collection",r_object_id");
print "Object ID is " objID ;
count=count+1;
print "\n";
print "$1,$2;" OFS="|" >> ABC.xls;
print "\n";
}
}
print "##Total number of objects: "count;
}
 
With the above code I am able to create a query collection but I am unable to write the results into an excel file. Even writing the result into a text file first and then writing it to excel file via txt file is also a possible workaround.

Kindly help me out in this! TIA
Reply | Threaded
Open this post in threaded view
|

Re: Query result set output in EXCEL file

Raj Kiran
Hi ,You can write the data first to csv and then convert it into excel.Please find sample perl script as below


===
#!/usr/bin/perl -w
use strict;
use Spreadsheet::WriteExcel;
use Text::CSV_XS;

# Check for valid number of arguments
if (($#ARGV < 1) || ($#ARGV > 2)) {
   die("Usage: csv2xls csvfile.txt newfile.xls\n");
};

# Open the Comma Separated Variable file
open (CSVFILE, $ARGV[0]) or die "$ARGV[0]: $!";

# Create a new Excel workbook
my $workbook  = Spreadsheet::WriteExcel->new($ARGV[1]);
my $worksheet = $workbook->add_worksheet();

# Create a new CSV parsing object
my $csv = Text::CSV_XS->new;

# Row and column are zero indexed
my $row = 0;


while (<CSVFILE>) {
    if ($csv->parse($_)) {
        my @Fld = $csv->fields;

        my $col = 0;
        foreach my $token (@Fld) {
            $worksheet->write($row, $col, $token);
            $col++;
        }
        $row++;
    }
    else {
        my $err = $csv->error_input;
        print "Text::CSV_XS parse() failed on argument: ", $err, "\n";
    }
}
====