Discussion:
IBM SDI JSON Data Parse To CSV File
(too old to reply)
Devran Uluçay
2020-02-26 08:35:17 UTC
Permalink
Hello,

I want to json data parse to csv file.
I'm using http client connector for Rest Api.
I have response message.

Example Data:
{
"schemas": [
"urn:ietf:params:scim:api:messages:2.0:ListResponse"
],
"totalResults": 349,
"totalPage": 1,
"page": 1,
"itemsPerPage": 349,
"resources": [
{
"schemas": [
"urn:ietf:params:scim:schemas:core:2.0:Entitlement",
"urn:ibm:params:scim:schemas:extension:bean:agc:2.0:Entitlement"
],
"id": "800",
"meta": {
"created": 1556093067000,
"lastModified": 1556093068000
},
"type": 3,
"urn:ibm:params:scim:schemas:extension:bean:agc:2.0:Entitlement": {
"code": "testRole",
"lastModTime": 1556093068000,
"incompHigh": false,
"incompMedium": false,
"incompLow": false,
"administrative": 0,
"published": 1,
"isFulFilled": false,
"creationDate": 1556093067000,
"name": "testRole",
"description": "testRole_mta"
}
},
{
"schemas": [
"urn:ietf:params:scim:schemas:core:2.0:Entitlement",
"urn:ibm:params:scim:schemas:extension:bean:agc:2.0:Entitlement"
],
"id": "1097",
"meta": {
"created": 1576146817000,
"lastModified": 1576146815000,
"lastModUser": "Rule Engine TARGET"
},
"type": 1,
"urn:ibm:params:scim:schemas:extension:bean:agc:2.0:Entitlement": {
"code": "d4efcd9a-238c-4d33-b9ee-85785afd8d74",
"application_id": 132,
"application_name": "MSSQLConnector",
"lastModTime": 1576146815000,
"lastModUser": "Rule Engine TARGET",
"externalRef": "master:dbo",
"incompHigh": false,
"incompMedium": false,
"incompLow": false,
"administrative": 0,
"published": 0,
"isFulFilled": true,
"creationDate": 1576146817000,
"name": "master:dbo",
"permissionType_id": 144,
"permissionType_name": "SQLDbSchemaGrpProfile"
}
},


I want to this data parse to csv file.
I'm using hierarchy entry but not working. Maybe I can't do this.
What can I do. I can send AL xml file from private.

Thanks.
Eddie Hartman
2020-02-26 18:13:42 UTC
Permalink
Post by Devran Uluçay
Hello,
I want to json data parse to csv file.
I'm using http client connector for Rest Api.
I have response message.
{
"schemas": [
"urn:ietf:params:scim:api:messages:2.0:ListResponse"
],
"totalResults": 349,
"totalPage": 1,
"page": 1,
"itemsPerPage": 349,
"resources": [
{
"schemas": [
"urn:ietf:params:scim:schemas:core:2.0:Entitlement",
"urn:ibm:params:scim:schemas:extension:bean:agc:2.0:Entitlement"
],
"id": "800",
"meta": {
"created": 1556093067000,
"lastModified": 1556093068000
},
"type": 3,
"urn:ibm:params:scim:schemas:extension:bean:agc:2.0:Entitlement": {
"code": "testRole",
"lastModTime": 1556093068000,
"incompHigh": false,
"incompMedium": false,
"incompLow": false,
"administrative": 0,
"published": 1,
"isFulFilled": false,
"creationDate": 1556093067000,
"name": "testRole",
"description": "testRole_mta"
}
},
{
"schemas": [
"urn:ietf:params:scim:schemas:core:2.0:Entitlement",
"urn:ibm:params:scim:schemas:extension:bean:agc:2.0:Entitlement"
],
"id": "1097",
"meta": {
"created": 1576146817000,
"lastModified": 1576146815000,
"lastModUser": "Rule Engine TARGET"
},
"type": 1,
"urn:ibm:params:scim:schemas:extension:bean:agc:2.0:Entitlement": {
"code": "d4efcd9a-238c-4d33-b9ee-85785afd8d74",
"application_id": 132,
"application_name": "MSSQLConnector",
"lastModTime": 1576146815000,
"lastModUser": "Rule Engine TARGET",
"externalRef": "master:dbo",
"incompHigh": false,
"incompMedium": false,
"incompLow": false,
"administrative": 0,
"published": 0,
"isFulFilled": true,
"creationDate": 1576146817000,
"name": "master:dbo",
"permissionType_id": 144,
"permissionType_name": "SQLDbSchemaGrpProfile"
}
},
I want to this data parse to csv file.
I'm using hierarchy entry but not working. Maybe I can't do this.
What can I do. I can send AL xml file from private.
Thanks.
Ok, Devran, I would first convert this to a JS object and then grab properties from the tree of data to create a flat set of attributes in an Entry. This you can pass to a CSV Parser to write. So...

-----
json = system.getScriptText("Devrans_JSON")
hEntry = work.fromJSON(json);

// Ok, since you already have an hierarchical Entry, I am using one here
// You could also have used fromJson() to convert your JSON payload directly
// to a JS object.
jsObj = fromJson(hEntry.toJSON());

// We'll need an Entry for collecting Attributes
entry = system.newEntry();

// Now load a pre-configured CSV Connector (FileSystem w/ CSV Parser) from
// Resources/Connectors
csv = system.getConnector("CSVConnector");
// And initialize it
csv.initialize(null);

// Now iterate through the members of the 'resources' Array
for (i = 0; i < jsObj.resources.length; i++) {
schema = jsObj.resources[i];

// Empty our Entry
entry.removeAllAttributes();

// Grab the id and type directly
entry.id = schema.id;
entry.type = schema.type;

// Now grab the object with the Entitlement properties
atts = schema["urn:ibm:params:scim:schemas:extension:bean:agc:2.0:Entitlement"];

// Walk through these with a for-loop which returns the name of each
// property in a JS object
for (attName in atts) {
// Add it to our empty Entry
entry[attName] = atts[attName];
}

// Log it
task.logmsg(entry);
// Add it to our CSV file
csv.putEntry(entry);
}
-----

Hope this helps!

/Eddie
Devran Uluçay
2020-03-03 08:39:40 UTC
Permalink
Hi,

Thanks for your answer.
I tried you said that but not working.
Our AL like below;

FEED
DATA FLOW
HttpClientConnector (Login Method)
HttpClientConnector2 (Find Data Method)
Empty Script
CsvConnector -> AddOnly Mode CSV Parser

HttpClientConnector2 return response successfuly. I take this response in input map. work.Response

Then Empty Script;

json = system.getScriptText(work.Response)
hEntry = work.fromJSON(json);

// Ok, since you already have an hierarchical Entry, I am using one here
// You could also have used fromJson() to convert your JSON payload directly
// to a JS object.
jsObj = fromJson(hEntry.toJSON());

// We'll need an Entry for collecting Attributes
entry = system.newEntry();

// Now load a pre-configured CSV Connector (FileSystem w/ CSV Parser) from
// Resources/Connectors
csv = system.getConnector("CsvConnector");
// And initialize it
csv.initialize(null);

// Now iterate through the members of the 'resources' Array
for (i = 0; i < jsObj.resources.length; i++) {
schema = jsObj.resources[i];

// Empty our Entry
entry.removeAllAttributes();

// Grab the id and type directly
entry.id = schema.id;
entry.type = schema.type;

// Now grab the object with the Entitlement properties
atts = schema["urn:ibm:params:scim:schemas:extension:bean:agc:2.0:Entitlement"];

// Walk through these with a for-loop which returns the name of each
// property in a JS object
for (attName in atts) {
// Add it to our empty Entry
entry[attName] = atts[attName];
}

// Log it
task.logmsg("EntryDevran: " + entry);
// Add it to our CSV file
csv.putEntry(entry);
}

Did I get it wrong?
What can I do?

Thanks.
Devran Uluçay
2020-03-03 08:42:26 UTC
Permalink
CSV Connector Output Map is empty.
Eddie Hartman
2020-03-04 11:24:15 UTC
Permalink
Post by Devran Uluçay
Hello,
I want to json data parse to csv file.
I'm using http client connector for Rest Api.
I have response message.
{
"schemas": [
"urn:ietf:params:scim:api:messages:2.0:ListResponse"
],
"totalResults": 349,
"totalPage": 1,
"page": 1,
"itemsPerPage": 349,
"resources": [
{
"schemas": [
"urn:ietf:params:scim:schemas:core:2.0:Entitlement",
"urn:ibm:params:scim:schemas:extension:bean:agc:2.0:Entitlement"
],
"id": "800",
"meta": {
"created": 1556093067000,
"lastModified": 1556093068000
},
"type": 3,
"urn:ibm:params:scim:schemas:extension:bean:agc:2.0:Entitlement": {
"code": "testRole",
"lastModTime": 1556093068000,
"incompHigh": false,
"incompMedium": false,
"incompLow": false,
"administrative": 0,
"published": 1,
"isFulFilled": false,
"creationDate": 1556093067000,
"name": "testRole",
"description": "testRole_mta"
}
},
{
"schemas": [
"urn:ietf:params:scim:schemas:core:2.0:Entitlement",
"urn:ibm:params:scim:schemas:extension:bean:agc:2.0:Entitlement"
],
"id": "1097",
"meta": {
"created": 1576146817000,
"lastModified": 1576146815000,
"lastModUser": "Rule Engine TARGET"
},
"type": 1,
"urn:ibm:params:scim:schemas:extension:bean:agc:2.0:Entitlement": {
"code": "d4efcd9a-238c-4d33-b9ee-85785afd8d74",
"application_id": 132,
"application_name": "MSSQLConnector",
"lastModTime": 1576146815000,
"lastModUser": "Rule Engine TARGET",
"externalRef": "master:dbo",
"incompHigh": false,
"incompMedium": false,
"incompLow": false,
"administrative": 0,
"published": 0,
"isFulFilled": true,
"creationDate": 1576146817000,
"name": "master:dbo",
"permissionType_id": 144,
"permissionType_name": "SQLDbSchemaGrpProfile"
}
},
I want to this data parse to csv file.
I'm using hierarchy entry but not working. Maybe I can't do this.
What can I do. I can send AL xml file from private.
Thanks.
This line is wrong, Devran:

json = system.getScriptText(work.Response)

The system.getScriptText(<scriptName>) is for loading the text contents of a Resources > Script in your library. What you want is the built-in fromJson() function.

json = fromJson(work.getString("Response") || "{}");

Note that work.Response references an Attribute, an SDI class for named container of values. What you want is the value of this attribute as a String, so you use Entry.getString(<attributeName>) to return it, or null if the attribute does not exist in the Entry (work, conn, etc.) or has no values.

The extra bit I added inside the parenthesis of fromJson() - '|| "{}"' - will cause an empty JSON string to be passed to it in the case the .getString() method returns null. Otherwise you could rather use an if-statement and only call fromJson() when you knew you had a string value for work.Response.

Hope this helps!

/Eddie
Devran Uluçay
2020-03-09 07:43:48 UTC
Permalink
Hi,

Thanks for your answer.
I tried json = fromJson(work.getString("Response") || "{applicationId}"); but not working.
As far as I understand, the name attribute will be written in brackets.

Thanks.
Eddie Hartman
2020-03-09 17:33:06 UTC
Permalink
Your JSON is incorrect to the left of the logical OR. It could be something like this:

... || '{"applicationId": ""}'

or similar

Loading...