nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

AWK Script for Running DQL

k.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(r_object_id) from dm_document where r_creator_name='XYZ'";

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 read the results for count(r_object_id) and write the results into an excel file.

Kindly help me out in this! TIA

Find more posts tagged with

Note:

If there are Accepted Answers, those will be shown by default. You can switch to 'All Replies' by selecting the tab below.

Button

Accepted answers

k.alzeena

The requirement has been achived with the help of AWK sript and the output being set in CSV format with delimiters.

All comments

lastnitescurry

Why AWK? It does not have a library to write Excel files.

Use java and Apache POI

PanosK

There is no need to write in Excel format. MS Excel understands just fine CSV format.

To OP: Remove the r_object_id from the SELECT DQL clause and your count result will return the precious number.

k.alzeena

Firstly thank you Brain and Panos for the response!

@ Brian,

I have the requirement to be done in AWK script only. so cant go with Java.

@ Panos,

can you kindly provide me a sample code, as I am struggling in getting these results to an excel output file.

I have resolved the count(r_object_id) by replacing it as count(*).

PanosK

You have to test your query first to ensure that is valid. The suggested change was from:


myQuery = "select r_object_id,count(r_object_id) from dm_document where r_creator_name='XYZ'";

to:


myQuery = "select count(*) from dm_document where r_creator_name='XYZ'";

or count(r_object_id) also works. It cannot work with the first r_object_id as it needs to be in a "group by" expression.

k.alzeena

I have updated my query as

myQuery = "select r_object_id,count(*) from dm_document where r_creator_name='XYZ' group by r_object_id";

On trying to print the results for r_object_id,count(*) I am getting the proper data.

My concern now lies with writing this data into an excel file.

PanosK

You do realise that your count will always be 1 as there is only one creator per dm_document. I would advise to clarify your requirement, because your code does not make much sense. If you are trying for example to count the documents per author, then you would need the following DQL:


select r_creator_name,count(*) from dm_document group by r_creator_name order by 2 desc

If you are trying to count the author for all versions of a document, then you have to add the "(all)" directive and the i_chronicle_id that is the r_object_id of the first version:


select i_chronicle_id,count(*) from dm_document(all) where r_creator_name='XYZ'  group by i_chronicle_id order by 2 desc

k.alzeena

My WHERE statement has many AND conditions and filters Panos, thus my result count will be more.

The result set fetching issue has been resolved. the issue now lies with output redirection to an excel file

PanosK

Just output your values in a comma separated line (CSV format) and add an XLS extension and you will be fine. It is understood by all MS Excel versions.

k.alzeena

@Panos,

I am unable to code that part in AWK script.

I have tried many combinations but I am getting cant open file or illegal statement. I tried the statement printf ("%s|%s|\n", $1, $2) >> fnXls (had set variable like fnXls = "ABC.xls"). But it gave an error stating cant open the file.

I tried in the below manner:

path="/folder1/folder2/folder3"

currentfile=$path/ABC.xls;

print {$1} >> $currentfile;

But it said error syntax at line

>>> currentfile=$path/ABC <<< .xls;

I have a txt file try.txt in the same location as my AWK script in server. when I try the statement

"print objID > try.txt;" I get the below error

syntax error at source

print objID > >>> try <<< .txt;

illegal statement at source next line

Can you kindly help me out in printing these values in an excel.

PanosK

I do not speak AWK, only the basics and maybe you have to redirect your question to an AWK related support forum.

The following command will create for example a nice CSV with the logins in first column and the names in the second:


$ awk -F: '{print $1,",",$5}' /etc/passwd >> test.csv

in a Unix environment, but my knowledge is limited

k.alzeena

The requirement has been achived with the help of AWK sript and the output being set in CSV format with delimiters.