topic Re: JSON to collection: store all items as text format in Product Forum

JSON to collection: store all items as text format

sumire — Tue, 01 Jul 2025 04:45:35 GMT

Hello,

I want to store all JSON items as text format in a collection, even if the value is only a number.

The items to be retrieved are not constant. Please share if you have any ideas.

Re: JSON to collection: store all items as text format

Nandhakumar — Tue, 01 Jul 2025 09:46:19 GMT

Utility - JSON: JSON to Collection automatically identifies the data type of the columns of the collection from json. From there we can do the following for small volume of records,

1. Create a defined collection with text data type and assign the values from original collection by looping.

Is it possible for you to have defined collection? or collection fields are dynamic?

thanks,

Nandha

Re: JSON to collection: store all items as text format

sumire — Tue, 01 Jul 2025 09:50:17 GMT

Hello @Nandhakumar ,

The collection fields are dynamic. So, I can not define the collection.

Re: JSON to collection: store all items as text format

Nandhakumar — Tue, 01 Jul 2025 10:12:22 GMT

Hi @sumire

May i ask you, you dont even have any clue that what will be the name of the columns in collection?

Because if the columns are dynamic then we can create an assingment of the value using blue prism without knowing the column name.

My suggestion to create a collection with dynamic columns is this but without knowing the fields of the collection we can't assign the values,

1. Get fields of the collection after Json to collection

2. Loop the fields and use append field (Text) to collection action to add a field into a Final collection

this way we can create the collection with fields which are coming via json. but without having the list of fields possible in a json response not possible to assign.

let me know your thoughts!

thanks,

Nandha

Re: JSON to collection: store all items as text format

sumire — Wed, 02 Jul 2025 03:01:58 GMT

I wrote Python code to change all items of JSON to text format.
However, even with the JSON converted using this code, the data type of the items is automatically determined when using Utility - JSON 😞

import json input_file = 'input.json' output_file = 'output.json' import os import sys os.chdir(os.path.dirname(os.path.abspath(sys.argv[0]))) def convert_values_to_str(data): if isinstance(data, dict): return {str(k): convert_values_to_str(v) for k, v in data.items()} elif isinstance(data, list): return [convert_values_to_str(item) for item in data] else: return str(data) try: with open(input_file, 'r', encoding='utf-8') as f: original_data = json.load(f) text_data = convert_values_to_str(original_data) with open(output_file, 'w', encoding='utf-8') as f: f.write(json.dumps(text_data, ensure_ascii=False, indent=4)) print(f"Finished: {output_file}") except Exception as e: print(f"Error: {e}") input("Finished. Press Enter")

Re: JSON to collection: store all items as text format

Nandhakumar — Wed, 02 Jul 2025 08:43:12 GMT

Hi @sumire ,

i would suggest you to write the code for converting the columns datatype into text of a collection.

Yes, JSON to collection utility automatically detects the data type. So first convert json to collection then write a code to change all the columns data type.

thanks,

Nandha

Re: JSON to collection: store all items as text format

Nandhakumar — Wed, 02 Jul 2025 12:37:45 GMT

Hi @sumire

Please try this code for converting all the fields into text data type. vb code just add it in collection manipulation utility as new page.

' Create a new DataTable to hold the output
Dim outputTable As New DataTable()

' Add columns as Text
For Each column As DataColumn In InputCollection.Columns
outputTable.Columns.Add(column.ColumnName, GetType(String))
Next

' Loop through each row and convert values to text
For Each row As DataRow In InputCollection.Rows
Dim newRow As DataRow = outputTable.NewRow()
For Each column As DataColumn In InputCollection.Columns
If row.IsNull(column) Then
newRow(column.ColumnName) = ""
Else
newRow(column.ColumnName) = row(column.ColumnName).ToString()
End If
Next
outputTable.Rows.Add(newRow)
Next

' Set the output
OutputCollection = outputTable

before this you can convert the json to collection.

Note: I have tested the code and it is working fine. but make sure to the values you are converting whether they stay the same or values change.

thanks,

Nandha

Re: JSON to collection: store all items as text format

sumire — Fri, 04 Jul 2025 00:56:27 GMT

I decided to modify "Utility - JSON" VBO.

I altered the global code of the VBO.
By modifying it this way, JSON records are stored in the collection as plain text instead of as an array of [JSON:Array].

Here is the code for the modified parts:

Private Function DeserialiseArrayWithoutJArray(ByVal jarr As JArray, ByVal populate As Boolean) As DataTable Dim dt As New DataTable ' Column definition (fixed as string type for all) If jarr.Count > 0 AndAlso TypeOf jarr(0) Is JObject Then Dim firstObj As JObject = DirectCast(jarr(0), JObject) For Each prop As JProperty In firstObj.Properties() If Not dt.Columns.Contains(prop.Name) Then dt.Columns.Add(prop.Name, GetType(String)) ' Force the data type to be fixed as String End If Next End If ' Loading line data If populate Then For Each token As JToken In jarr If TypeOf token Is JObject Then Dim row As DataRow = dt.NewRow() For Each prop As JProperty In DirectCast(token, JObject).Properties() row(prop.Name) = prop.Value.ToString() Next dt.Rows.Add(row) End If Next End If Return dt End Function

Re: JSON to collection: store all items as text format

sumire — Fri, 04 Jul 2025 01:05:19 GMT

Hello @Nandhakumar ,

The solution to add actions to the 'Collection Manipulation' VBO is also great. I will try it myself.

Re: JSON to collection: store all items as text format

Nandhakumar — Fri, 04 Jul 2025 01:29:30 GMT

Hi @sumire ,

Great to hear that you are able to resolve the issue 🙂

Thanks,

Nandha