cancel
Showing results for 
Search instead for 
Did you mean: 

Convert sql result to .csv file

Former Member
0 Kudos

Hi,

I need help to get all my sql result in .csv file.

My scenario:

I select a privilege --> Choose UI task to generate user list per privilege as .csv file

my sql request is working fine but I cannot generate the .csv correctly. It's empty.

function users_per_priv(Par){

Var userMSKEY= Par.get("MSKEY");

var SQL="";

var SQL = "SELECT

        L.mcOtherMSKEYVALUE Privilege,

        U.mcDisplayName DisplayName,

        FROM idmv_entry_simple U inner join idmv_link_ext2 L on U.mcMSKEY = L.mcThisMSKEY

        WHERE L.mcThisOcName='MX_PERSON'

        AND L.mcOtherOcName='MX_PRIVILEGE'

        AND L.mcLinkState=0

        AND L.mcOtherMSKEY=" +  userMSKEY;

var result="";

result= uSelect(SQL);

result = uReplaceString(result, "!!", "|");

return result;

}

My problem is how to get the result in .csv in To Ascii File pass, and to manage the delimiter correctly?

Many thanks,

Mia

Accepted Solutions (1)

Accepted Solutions (1)

former_member2987
Active Contributor
0 Kudos

Mia,

I am not somewhere where I can test this or get screenshots for you, but I'd consider something like this:

There's an option to set the delimiter called CSV delimiter in the ToASCII pass. In your screen shot it's set to ; just change it to ,

Matt

Former Member
0 Kudos

Hi Matt,

Sorry, but I am still not getting the .csv file even if I change the Delimeter as requested.

Any other idea ?

Regards,

Mia

laurent_vandenbemden
Participant
0 Kudos

Hello Mia,

Have you tried to remove the "Reset Output file" checkbox before generating the .csv?

I sometimes have an empty output when this box is checked .

Krz,

Laurent

terovirta
Active Contributor
0 Kudos

I think the error is that the destination-tab expects being passed a hashtable (like the Par-object) but your script passes a string.

Pass the data as hashtable or move your formatting (changing the separator for multivalue) logic to destination tab.


regards, Tero

Former Member
0 Kudos

Mia,

What Tero mentions is very likely the cause of the empty file problem. You're using the entry script as if it was an attributescript and this does not work. You need to add the result of your query to the Entry object that is returned from the script. See the helpfile and documentation for details.

An easier solution might be to remove the entry script setting and add it to the destination grid instead:

PRIVILEGE=%MSKEYVALUE%

DISPLAYNAME=%DISPLAYNAME%

MEMBERS=$function.users_per_priv(%MSKEY%)$$

(you might want to change the name of your userMSKEY variable to privMSKEY in the script to avoid confusion at a later time).

Another very much easier solution is to use:

PRIVILEGE=%MSKEYVALUE%

DISPLAYNAME=%DISPLAYNAME%

MEMBERS=%MXMEMBER_MX_PERSON%


And no script at all, but this only gives a list of the members mskeys.


Br,

Chris

Former Member
0 Kudos

Many thanks to all,

As you suggest it Per, I am using MEMBERS=%MXMEMBER_MX_PERSON%

I am getting the file as:

PRIVILEGEDISPLAYNAMEMEMBERS
PRIV:ADS123:PRIV123PRIV_ADS123_PRIV123978|1234|2676|7686|6567|4234

How to format the output as:

PRIVILEGEDISPLAYNAMEMEMBERS
PRIV:ADS123:PRIV123PRIV_ADS123_PRIV1239780
PRIV:ADS123:PRIV123PRIV_ADS123_PRIV1231234
PRIV:ADS123:PRIV123PRIV_ADS123_PRIV1232676
PRIV:ADS123:PRIV123PRIV_ADS123_PRIV1237684
PRIV:ADS123:PRIV123PRIV_ADS123_PRIV1236567
PRIV:ADS123:PRIV123PRIV_ADS123_PRIV123

4234

Thanks you very much,

Mia

terovirta
Active Contributor
0 Kudos

Easiest thing would be calling your existing script from the destination tab, row "displayname", and pass the user mskey to it.

regards, Tero

Former Member
0 Kudos

If you want one line per member the implementation is easier if you do it as a job rather than a workflow action. The action will try to generate just one line of data.

Job with To Ascii pass

If you need this to be initialized by a user from the workflow then you need to look into creating a script that first retrieves the members, then loops through the resultset and gets the displayname and other data and writes it line by line using the uToFile function. The action task is by design unable to do this for you.

The query for reference:

select L.mcOtherMskeyValue Privilege,P.mcDisplayName PrivilegeDisplayName,L.mcThisMskeyValue "User",E.mcDisplayName  UserDisplayName

from

  idmv_link_ext_active L

  inner join idmv_entry_simple E on E.mcmskey = L.mcThisMSKEY

  inner join idmv_entry_simple P on P.mcmskey = L.mcOtherMSKEY

where mcOtherOcName='MX_PRIVILEGE' and mcOtherMSKEYValue = 'PRIV:ADS123:PRIV123'

Br,

Chris

Former Member
0 Kudos

Hi Tero,

I get all the list of users in one line separated by: " | " but my question how to get the list one user per line as displayed.

Many thanks,

Mia

terovirta
Active Contributor
0 Kudos

Hi,

if you change:

result = uReplaceString(result, "!!", "|");

to:

result = uReplaceString(result, "!!", "\n;");

didn't have a chance to test it but if you're expecting to get two columns in CSV, this would add a new line for each member and add empty cell for the first column (privilege name).

regards, Tero

Former Member
0 Kudos

Hi Chris,

It's more clear now, I get my .csv file after looping to the mskey user list and adding all results to .csv file line by line.

I appreciate your help and Tero help too,

Mia

Answers (0)